combining 2 queries

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
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

combining 2 queries

Post 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]
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

A hint as to what your problem is would certainly help.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: combining 2 queries

Post 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?
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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!
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

Post by hame22 »

thats great thanks!
Post Reply