mysql 2 table query

[SeenGee]

Hard to explain this one.. heres some pseudo sql which hopefully will explain what i'm trying to do:

SELECT * FROM events WHERE promoter_id = '$user_id'

and then:

SELECT * FROM event_djs, events WHERE eventdjs.dj_name = '$username' (APART FROM THOSE WHERE event_id has already been listed from the initial query) JOIN events ON eventdjs.event_id = events.event_id (and also retrieve events.event_name, events.event_date and events.event_venue)

i hope someone can make sense of this somehow...

Thanks.

[eoghain]

What you are going to want to do is build a comma separated list of event_id's ($idList) from your first query and write your second query like this:

SELECT event_djs.*, 
       events.event_name,
       events.event_date,
       events.event_venue
  FROM `event_djs`
  JOIN 'events'
    ON eventdjs.event_id = events.event_id
 WHERE eventdjs.dj_name = '$username' 
   AND events.event_id NOT IN ($idList)

[SeenGee]

great, that looks exactly like what i need.

so just something inside a loop like:

$idList .= ''.$event_id.',';

??

[eoghain]

Yep, but the loop you want to use looks more like this:

$idList = "";
$sep = "";
while ($row = mysql_fetch_array($result)
{
   $idList .= $sep . $row[event_id];
   $sep = ",";
}

That way you won't have a list that looks like (1,2,3,) and breaks your query.

[SeenGee]

cheers man, thanks a lot.