Jump to content


Photo

Query on Queries. Multiple in one


  • Please log in to reply
3 replies to this topic

#1 Humpty

Humpty
  • Members
  • PipPipPip
  • Advanced Member
  • 132 posts

Posted 05 February 2006 - 11:41 AM

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 isn't listed in table2 field7

I hope that made logical sense to someone. :D

(love heart added as symbol of worldy love for all)
- Humpty
  #> Where does the light go when it goes out?

#2 Humpty

Humpty
  • Members
  • PipPipPip
  • Advanced Member
  • 132 posts

Posted 05 February 2006 - 12:05 PM

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.
- Humpty
  #> Where does the light go when it goes out?

#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 07 February 2006 - 06:07 PM

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:

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


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 February 2006 - 07:12 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users