How to write this SQL select the best way?

Hey guys,

I know there are some SQL specialists in my home feed and I want to find the best way to write a SQL statement to my task.

Let's dive right in what my structure is. I will sketch the table structures and include only the fields needed for my case.

HEADER TABLE (HDR):

BOMId	RecId
FirstBOM	0515151848480
SecndBOM	0525091647216

LINES TABLE (LINE):

BOMId	ItemId	RecId
FirstBOM	CoolItemHere	984986541
FirstBOM	KetchupBottle	515105685
FirstBOM	RubberDuckey	541980894
FirstBOM	TesaBandRoll	976512058
SecndBOM	SideProduct12	896414986
SecndBOM	KetchupBottle	324858715
SecndBOM	FreezerLabels	321054879
SecndBOM	MagnetHolder	980489541
SecndBOM	WoodenStick	261456696

Relation: HDR (1) - (N) LINE

Task:
I need to retrieve only the first (maybe single one) BOMId of table HDR where a combination of items in LINE exists to this BOMId.

Example:
I want to retrieve FirstBOM when I search for a combination of ItemId's "KetchupBottle" && "RubberDuckey". This statement should not return SecndBOM also, because "KetchupBottle" is there but not "RubberDuckey".

I tried grouping it, but with no luck.

Kinda hard to explain with words... Hope this is understandable!

How would you write a select for this?

Comments

[Dian Fay]

select bom_id
from line
where item_id = 'KetchupBottle' or item_id = 'RubberDuckey'
group by bom_id
having count(*) = 2;

The having clause needs to check that the count equals the number of item ids you're matching.

[Robin Kretzschmar]

Hey Dian thanks for that suggestion! The having was what I've been missing, worked out very well, thanks a lot! 😊

It's those kinda tasks where I can learn a lot from.

[Kevin McKenna]

I won't weigh in with a solution as Dian Fey already did.. but a couple of thoughts.

If you have a hard time explaining the result, try giving a few examples of the desired result.

Also, may be out of your control but that text field for itemid just about breaks my heart.

Good luck getting your data out! :)

[Robin Kretzschmar]

Yeah Kevin I feel the same about the column for the item but I can't change it unfortunately, it's part of a big erp system 😅 if I could, I definitely would change it 😁

[Kevin McKenna]

Been there, done that!

[Tobias SN]

Not really an answer, but if you don’t wanna deal with SQL you could try using an ORM.

[Dian Fay]

O/RMs don't help with this kind of situation. They operate at the level of discrete records and translate them to&from objects and object graphs. Here, it's what's in the records that matters, and specifically the relationships of multiple records in the same table. It can't be abstracted to the O/RM level; SQL is the only option.