Page 1 of 1

combining 2 queries

Posted: Wed Jun 27, 2007 3:03 pm
by hame22
patrikG | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi

I am trying to create a query that takes a members ID looks up past images they have submitted and the locations they are from. It then uses these locations to find further images of the particluar locations submitted by other members.

Am having a little trouble writing this query 

so far I have 

[syntax="php"]$result = mysql_query("Select * from plogger_pictures INNER JOIN pubs on plogger_pictures.location_id = location.location_id where plogger_pictures.member_id = '$member_id'")
any ideas how to progress

all opinions will be appeciated!!


patrikG | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Jun 27, 2007 5:01 pm
by pickle
A hint as to what your problem is would certainly help.

Re: combining 2 queries

Posted: Wed Jun 27, 2007 5:11 pm
by superdezign
hame22 wrote:

Code: Select all

$result = mysql_query("Select * from plogger_pictures INNER JOIN pubs on plogger_pictures.location_id = location.location_id where plogger_pictures.member_id = '$member_id'")
Why isn't `pubs` anywhere in your ON clause?

Posted: Wed Jun 27, 2007 5:45 pm
by ReverendDexter
Okay, I'm having a bad syntax day so here's my psuedocode $.02

I'd use a subquery, basically inverting the way you phrased it

So, something along the lines of:

Code: Select all

SELECT pictures
FROM plogger_pictures
WHERE location IN (SELECT location
           FROM plogger_pictures
           WHERE member_id = '$member_id')
AND member_id <> '$member_id'
The AND clause will make sure the member gets pictures they didn't take - that was my assumption as to where you were going with this idea. I threw quotes around member_id 'cause I wasn't sure what the datatype would be.

Hope it helps!

Posted: Fri Jun 29, 2007 3:26 am
by hame22
thats great thanks!