Page 1 of 1

Merge two MySQL queries in to one?

Posted: Tue Nov 04, 2008 6:19 pm
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?

Re: Merge two MySQL queries in to one?

Posted: Tue Nov 04, 2008 10:48 pm
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
 

Re: Merge two MySQL queries in to one?

Posted: Wed Nov 05, 2008 4:13 am
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.