while through loops with and without condition

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

while through loops with and without condition

Post 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.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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)
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Hmm, I couldnt find any documentation on the mysql site specifically for "IS IN" or "IS NOT IN".
Last edited by Smasher on Wed Jun 27, 2007 1:03 pm, edited 1 time in total.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post 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
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Thanks for your help :) That worked perfectly.
Post Reply