Query on Queries. Multiple in one

[Humpty]

G'day guys,
I'm a newbie for PHP, and I have done some SQL for a while but I am lost when it gets too advanced. Need more practice.

What i need is a query that gives results based on criteria that is a query itself (i think)

This should explain it:

What I want:
SELECT * FROM table1 WHERE field2 [i]isn't listed in table2 field7[/i]

I hope that made logical sense to someone. :D

(love heart added as symbol of worldy love for all)

[Humpty]

I have had success by copying and altering another users query in thier post titled "about subquery".

Please excuse me i'm quick to the post. Please also delete this post moderators as I don't see how it would help anyone.

Thankyou all.

[wickning1]

Subqueries are almost always slower than joins. If you can figure out a join to use instead, you should.

This kind of query is a common question among newcomers to SQL, here is the solution:

[code]
SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.field2=t2.field7 WHERE t2.field7 IS NULL
[/code]

[fenway]

First, there are many cases where you simply can't use a JOIN. Second, the main reason why subqueries are often slower is simply that MySQL can't figure out a good way to optimize it -- and this should get better with time. Still, JOINs are "better" from a speed perspective, but "harder" for some people to visualize.