Jump to content


Photo

possible join query


  • Please log in to reply
1 reply to this topic

#1 jmag

jmag
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationSweden

Posted 26 March 2006 - 02:35 PM

Hello,

I believe this should be fairly simple, but I'm not quite sure how to do it.

I store some imagedata in a table and in another table I store which pages this image might be connected to. Lets see if I manage to put it on print:
All pictures that have pic_type right should be selected from the pics table and are connected to the current page being viewed.

the start of the query I came up with looks like this:
SELECT pic.*, conn.* FROM pics AS pic, pic_con AS conn WHERE pic.pic_type = 'right' AND conn.page_id = '1';
But there aren't any connection in that so I fetch the right rows from the pic_con, is it? well, beats me...

the table layouts look like this:
pics: pic_id, pic_src, pic_alt, pic_caption, pic_link, pic_type and pic_id is the primary key
pic_con: pic_id, page_id, page_sortorder where pic_id and page_id is a combined primary key

Greatful for any help!

Edit:
I actually made the query work... forgot the from clause. But it returns the data a bit wrong. I get one pic_id from the pics table and on the same row another pic_id is returned from the pic_con table.
It would also be great if I in the same query get a random row from the result, so in the end only one row is returned.

#2 jmag

jmag
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationSweden

Posted 26 March 2006 - 02:59 PM

Nvm the query, I got it :>
SELECT * FROM pics AS pic LEFT JOIN pic_con AS conn ON pic.pic_id = conn.pic_id WHERE conn.page_id = '1';




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users