Page 1 of 1

while through loops with and without condition

Posted: Wed Jun 27, 2007 11:38 am
by Smasher
I have two queries to furfil.

SELECT * FROM slabs WHERE id is not present in table2.slabs_id
SELECT * FROM slabs WHERE id is present in table2.slabs_id

Basically I want to find if one a row from a table has a link/join to another table, and the complete opposite.
So I can filter out results that are present in another table and ones that arent.

The only way I can think of doing it is looping through table 2 and getting the row details then. Just wondered if theres away todo the above!

Hope I explained myself.

Posted: Wed Jun 27, 2007 12:00 pm
by ReverendDexter
Are you looking for the queries? Or a way to do it in one query?

For the first, I would try doing something along the lines of

Code: Select all

SELECT *
FROM slabs
WHERE id IS IN ( SELECT slabs_id
                           FROM table2)
For the other query, just make the "IS IN" "IS NOT IN" (I think - double check that syntax)

Posted: Wed Jun 27, 2007 12:45 pm
by Smasher
Hmm, I couldnt find any documentation on the mysql site specifically for "IS IN" or "IS NOT IN".

Posted: Wed Jun 27, 2007 12:52 pm
by mentor
The syntax is IN () and NOT IN().

You can acheive the same results with INNER JOIN and LEFT JOIN

This query will list all those rows which are present in both tables

Code: Select all

SELECT s.* FROM slabs s
INNER JOIN table2 t2 ON t2.slabs_id = s.id
This query will list all those rows which are not present in table2

Code: Select all

SELECT s.* FROM slabs s
LEFT JOIN table2 t2 ON t2.slabs_id = s.id
WHERE t2.slabs_id IS NULL

Posted: Wed Jun 27, 2007 1:02 pm
by Smasher
Thanks for your help :) That worked perfectly.