SQL Query with two tables and need to search one of the tables for specific and multiple column values

Question

I need to write a complex query that involves the two sample tables below and I’m struggling to understand how to construct the query properly.

The table structure is as follows, along with sample data:

Table 1:
ID | Type | Size
A123 | Block | Medium
C368 | Square | Large
X634 | Triangle | Small
K623 | Square | Small

Table 2:
ID | Code | Description | Price
A123 | C06 | Sensitive Material | 99.99
A123 | H66 | Heavy Grade | 12.76
A123 | U74 | Pink Hue | 299.99

C368 | H66 | Heavy Grade | 12.76
C368 | G66 | Green Hue | 499.99
C368 | C06 | Sensitive Material | 99.99
C368 | K79 | Clear Glass | 59.99

X634 | G66 | Green Hue | 499.99
X634 | K79 | Clear Glass | 59.99
X634 | Z63 | Enterprise Class | 999.99

K623 | K79 | Clear Glass | 59.99
K623 | G66 | Green Hue | 499.99
K623 | X57 | Extra Piping | 199.99

The query should be based on the Type column from Table 1 primarily and then join on the ID column of Table 2. The goal of the query is to search for all IDs in Table 1 that have specific Code column combinations in Table 2.

The final output should be a table that looks like this for Type = Square AND both (Code = G66 AND Code = K79) as well:

ID 
C368
K623

Those two IDs should be returned because they both have BOTH option codes in the pseudo query above.

How can I assemble this result using these two tables? Below are two initial queries I've written - neither produce the correct result. I've tried the IN operator along with =/AND/OR operators as you can see.

Attempt 1 (seems to work with ONE code but not > 1 code):

select distinct ID
from
(
SELECT shapes.ID, details.code, details.description
FROM db.table2 details 
JOIN db.table1 shapes
ON details.VIN = shapes.VIN
WHERE shape.type='Square'
) src
where code IN ("G66", "K79")
-- where code = "G66" AND code = "K79" (Produces zero results)
-- where code = "G66" OR code = "K79" (Produces incorrect results)

Attempt 2 (seems to work with ONE code but not > 1 code):

SELECT distinct ID
FROM db.table2 details
WHERE ID IN
(
    SELECT ID FROM db.table1 shapes 
    WHERE shapes.type='Square' 
) AND code IN ("G66", "K79")
-- AND code = "G66" AND code = "K79" (produces zero results)
-- AND code = "G66" OR code = "K79" (Produces incorrect results)

Thanks

Answer 1

Whenever you have a problem that is of the ilk "I need the IDs from this table where there is one row that has value A and another row that has value B and both rows have this same ID" you need to select all the rows matching your criteria, group them and then count them and only use the rows that have the matching count:

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79')
GROUP BY t2.id
HAVING COUNT(*) = 2

If there might be some bogus results like two rows that are G66 and no rows that are K79, then this simple counting will be defeated. We can instead look at the values (if it's 2) using MIN and MAX:

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79')
GROUP BY t2.id
HAVING MIN(t2.code) = 'G66' AND MAX(t2.code) = 'K79'

It works because alphabetically G66 is less than K79, so G66 will be the min one

If we have 3 values that we must mandate, we can do some trick like turning all the codes into a number, and demanding the sum be something. I'll use base 2 for this:

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79', 'X99')
GROUP BY t2.id
HAVING SUM(CASE t2.Code WHEN 'G66' THEN 1 WHEN 'K79' THEN 2 WHEN 'X99' THEN 4 END) = 7

If we map them to 1, 2 and 4 then the only way to make 7 (if the values are unique) is to have one of each. If there could be 7 G66 and none of the others, giving a bogus result, then we might have to count them individually:

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79', 'X99')
GROUP BY t2.id
HAVING 
  SUM(CASE t2.code WHEN 'G66' THEN 1 ELSE 0 END) = 1 AND
  SUM(CASE t2.code WHEN 'K79' THEN 1 ELSE 0 END) = 1 AND
  SUM(CASE t2.code WHEN 'X99' THEN 1 ELSE 0 END) = 1 

Answer 2

Couldn't you just use an INNER JOIN on the id's from Table 1 after getting the relevant rows which have the type we're looking for since Table 1 and Table 2 are related by ID?

I imagine we could first do a query to to get all the rows which have the relevant type and then run an INNER JOIN to get the shared rows which have the ID we care about.

Finally, we could just group our results by their code column?

Maybe something like this could work?:

SELECT * FROM db.table1 WHERE db.table1.type = "whatever"
INNER JOIN db.table2 ON db.table1.id = db.table2.id
GROUP BY db.table2.code HAVING COUNT(*) >= 1
AND db.table2.code IN ("code_1, code_2, code_3")

I just started SQL so I hope this hopes!

P.S. I realized I didn't cover your condition for having the code be a member of the the subset of codes that you care about. So I think this may work.

Answer 3

Here is what I think.

Filter data from t1 by the where clause, then join the t2 , 2 times, each to be filtered by conditions to have G66 & K79 in same table (2 different joins)

Select t1.ID 
from t1 
inner join t2 as t2_G66 on t1.ID = t2_G66.ID
inner join t2 as t2_K79 on t1.ID = t2_K79.ID
where t1.Type = 'Square' and 
    t2_G66.Code = 'G66' and 
    t2_K79.Code = 'K79' 

Here is the fiddle