SQL pseudo query

Question

Three data tables A, B and C. A and B share a common variable ID1. B and C share a common variable ID2. Write a pseudo query to get this result: select everything from Table B, with matching records in both Table A and B, but not in Table C.

I am new to sql and i'd really appreciate the help

Ask a *specific* question. Don't just dump your homework assignment here and expect us to do it. — Siyual, Jun 01 '17 at 20:44
What's a pseudo query? It sounds like you really need to write a `JOIN`. — Barmar, Jun 01 '17 at 21:35
See http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 for how to select records that don't match table C. — Barmar, Jun 01 '17 at 21:37

score 1 · Answer 1 · answered Jun 01 '17 at 21:01

We can't do your homework for you, but here's some direction for learning: Table A and Table B will be joined with a basic inner join on the ID1 column.

The trickier part is excluding results from A and B if they exist in Table C. For that you can do an outer join, and add a where clause to find the null values of table C. This is because when you do an outer join (for this example, we'll use a left outer join), it will pull all of the appropriate records from the left table, and then try to pull matching records from the right table. But if the record doesn't exist in the right table, then it will return a record with "null" values.

Your query would be:

SELECT
    table_b.* -- "select all from table B"
FROM
    table_a INNER JOIN table_b ON (table_a.ID1 = table_b.ID1) -- "matching records in A and B"
    LEFT OUTER JOIN table_c ON (table_b.ID2 = table_c.ID2) -- Bring in table C to let us check if B's rows exist in C
WHERE
    table_c.ID2 IS NULL -- Only display records that don't have a corresponding C record

The key to "selecting records that in B and not C" is understanding outer joins.

SQL pseudo query

1 Answers1