Stupid SQL question

Pig

This should be obvious to me, but my brain appears to be fried.

I have a two tables. Table2 is joined to table1 by table1's id. There are potentially multiple records for table2 for each record in table1. I want to get all the records from table1 where none of the associated records in table2 contain a certain value.

table1

table2

id
table1id
col1

So I can get the full set of data by joining thusly:
select table1.id, table2.col1 from table1 inner join table2 on table2.table1id = table1.id
results in:

table1.id    table2.col1
---------------------------
1                 9
1                 1
1                 8
2                 4
2                 3

I only want to get records from table1 if NONE of the records in table2 are in a certain list. I tried doing "where table2.col1 NOT IN (9,8,7)" as the main where clause, and within the inner join. All it does is filter out that combo. EG:

table1.id    table2.col1
---------------------------
1                 1
2                 4
2                 3

What I really want to do is not get record "1" from table1 at all.

Thanks!

paulnaj

Hi,

This might work (switched the FROM & JOIN)...

SELECT table1.id, table2.col1
FROM table2
INNER JOIN table1 ON table2.table1id = table1.id
WHERE table2.col1 NOT IN (9,8,7)
GROUP BY table1.id

EDIT. Sorry, just followed through and realised it'll still pick out that "1" ... I'll think again. Paul

Pig

Yeah, that's what I meant by "within the inner join". Thanks for your efforts.

paulnaj

The problem is that, it's a sort of double summary, so how about just joining table2 to itself(!) on table1id ...

SELECT table2.table1id, table2.col1
FROM table2
LEFT JOIN table2 AS a_table2 ON (table2.table1id = a_table2.table1id)
WHERE table2.col1 <> a_table2.col1
AND table2.col1 NOT IN (9,8,7)
AND a_table2.col1 NOT IN (9,8,7)
GROUP BY table2.table1id

Might work :evilgrin:

Paul.

Pig

I'm pretty sure that won't work. Table1 isn't even in that query. Ultimately I don't even want anything from table2. I just want to filter out results based on it.

paulnaj

Well you could at least try it!

It will list the table1ids that you wanted and you can left-join table1 if you want ...

SELECT table2.table1id, table2.col1
FROM table2
LEFT JOIN table2 AS a_table2 ON (table2.table1id = a_table2.table1id)
LEFT JOIN table1 ON (table2.table1id = table1.id)
WHERE table2.col1 <> a_table2.col1
AND table2.col1 NOT IN (9,8,7)
AND a_table2.col1 NOT IN (9,8,7)
GROUP BY table2.table1id

One way or another you'll need to have table2 appear twice in the query ... if you end up doing it all in one query, that is.

Pig

Sorry, I did try the fist example after posting that. It still gave me the results I did not want. I'm still plugging away at it. Thanks

Pig

I can't think of a way to do this with one query. I think I'll have to use sub queries. meh.

Lars_Berg

select id
   from table1
  where not exists
 (select *
      from table2
    where table1id = table1.id
       and table2.col1 in (7,8,9))