Merge two MySQL queries in to one?

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
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Merge two MySQL queries in to one?

Post by JAB Creations »

I'm wondering if I can merge the following two MySQL queries in to a single query?

Code: Select all

SELECT tag_id FROM tags WHERE tag_name_base = '".$tag_name_base."'
Result: 3

Code: Select all

SELECT thread_title, thread_url FROM xhref_tags LEFT JOIN threads ON xhref_thread_id = thread_id WHERE xhref_tag_id = '3'ORDER BY thread_title ASC
Result: thread_title thread_url
Example 3 example_3
Example 2 example_2
Example 1 example_1


Between yesterday and today I have finally gotten reasonably used to left joins. I'm wondering if I could merge these two however?
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Merge two MySQL queries in to one?

Post by novice4eva »

Code: Select all

 
SELECT thread_title, thread_url
FROM xhref_tags
LEFT JOIN threads
ON xhref_thread_id = thread_id
LEFT JOIN tags
ON xhref_tag_id = tag_id
WHERE tag_name_base = '".$tag_name_base."'
ORDER BY thread_title ASC
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Merge two MySQL queries in to one?

Post by VladSun »

[sql]SELECT thread_title, thread_url FROM xhref_tags LEFT JOIN threads ON xhref_thread_id = thread_id WHERE xhref_tag_id = '3'ORDER BY thread_title ASC[/sql]
Ok ... I have had some doubts about it, but now I'm pretty sure - you are talking about LEFT JOINing tables all the time, but in fact you don't need it and I guess this is because you haven't realized yet what is LEFT/INNER/RIGHT JOIN.

Let's have some e-learning ;)

Exercise 1)
Take a look at
http://www.w3schools.com/sql/sql_join_left.asp
http://www.w3schools.com/sql/sql_join_inner.asp
http://www.w3schools.com/sql/sql_join_right.asp

And explain why the query showed above is wrong.

PS: I know why this has happened - you needed to JOIN two tables, you tried with LEFT JOIN, you liked the result (your "A-ha" approach) and you decided that you need LEFT JOIN, but in fact, you got the right results not because you were using LEFT JOIN properly, but because of your *data* happened to be so.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply