Query, join and order by... need help

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
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

Query, join and order by... need help

Post by idotcom »

Hi,

I can't seem to get this right. I've been searching here and other sites for an example similar to what I need to do, but no luck... unless I just didn't recognize it.

Anyhow, I would be greatful for an example for my scenario :)


Tables:

images:

imgid __ userid __ path
1 _____ 1 ______ ...
2 _____ 1 ______ ...
3 _____ 1 ______ ...
4 _____ 2 ______ ...


image_order:

imgid __ ordernum
2 _____ 1
3 _____ 2
1 _____ 3

I need something like this:

select all from images where userid=$var and select all from image_order where imgid and then order images by image_order.ordernum


I think it's probably simple enough... :oops: just not getting it...

Thank you in advance for your help.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Probably somethng like:

Code: Select all

SELECT images.imgid, images.userid, images.path FROM images LEFT JOIN images_order ON images..imgid=images_order.imgid ORDER BY images_order.imgid
Since you have a 1:1 relationship between the tables they could be combined.
(#10850)
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

Post by idotcom »

Thanks for your reply.

I tried that but it didn't work. Where would I add a WHERE?

Like:

Code: Select all

SELECT images.imgid, images.userid, images.path FROM images [b]WHERE images.userid=$userid [/b]LEFT JOIN images_order ON images.imgid=images_order.imgid ORDER BY images_order.imgid
[/syntax]
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

Post by idotcom »

Got it to work :)

Thank you

Code: Select all

SELECT images.imgid, images.userid, images.path 
FROM images 
LEFT JOIN images_order ON images.imgid=images_order.imgid 
WHERE images.userid=$userid ORDER BY images_order.ordernum ASC
Post Reply