DEV Community

How to write this SQL select the best way?

Robin Kretzschmar on April 04, 2019

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 r...

Read full post

Dian Fay • Apr 4 '19 • Edited

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 • Apr 4 '19

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 • Apr 4 '19

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 • Apr 4 '19

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 • Apr 4 '19

Been there, done that!

Tobias SN • Apr 4 '19

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

Dian Fay • Apr 4 '19

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.