Page 1 of 1

Query, join and order by... need help

Posted: Fri Jun 15, 2007 2:17 pm
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.

Posted: Fri Jun 15, 2007 2:57 pm
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.

Posted: Fri Jun 15, 2007 4:52 pm
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]

Posted: Fri Jun 15, 2007 7:09 pm
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