Need to combine queries, but unsure where to even start..

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
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Need to combine queries, but unsure where to even start..

Post by Skara »

Alright, here's the one query:

Code: Select all

SELECT id,file,name,info,uploaded FROM photos ORDER BY uploaded DESC LIMIT 10;
and here's the second:

Code: Select all

SELECT time,post FROM blog ORDER BY time DESC LIMIT 10;
I need the latest 10 entries of both combined. ...er.. In other words, I need the latest 10 entries as if they were in one table.

I know somehow I need a JOIN, but I don't see how to combine it with the two ORDER BYs and get the right results. ^^;
User avatar
elecktricity
Forum Contributor
Posts: 128
Joined: Sun Sep 25, 2005 8:57 pm
Location: Trapped in my own little world.
Contact:

Post by elecktricity »

im not sure, try displaying 5 of each of them displaying the results, but that wouldnt put them side by side, you may want to try this out:

Code: Select all

<?PHP
SELECT * FROM content, users DESC LIMIT 10
?>
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

:arrow:

Code: Select all

SELECT p.id, p.file, p.name, p.info, p.uploaded, b.time, b.post
FROM photos AS p, blog as b
ORDER BY uploaded DESC, time DESC
LIMIT 10
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't think either of the solutions posted previously will cut it, but a UNION will

Code: Select all

(SELECT `id`,`file`,`name`,`info`,`uploaded` FROM `photos`)
UNION
(SELECT '','','',`post`,`time` FROM `blog`)
ORDER BY `uploaded` DESC LIMIT 10
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

feyd wrote:I don't think either of the solutions posted previously will cut it
can you explain plz
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

both are joining tables that have no relation to each other therefore building an O^n result set. Sorting based on one field first gives priority to that field. The fields need to be processed as one, only a union can do that.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

seriously, I first did think about using a UNION but, well... umm. OK thanks for explaining :)
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

Voila! Thanks, feyd. ;)
Post Reply