[SOLVED] Query from many to many relationship

[jellis]

Hi all,

 

Long time reader, first time poster.

 

I'm absolutely stuck.  I have the following table structure.

 

Presentations

pres_id

title

description

date

 

Speakers

speak_id

fname

lname

bio

 

Speakpres

sp_id

speak_id

pres_id

 

I've written a page to display the information from a presentation.  It queries the database based on the pres_id and returns those values... no problems.

 

The problem arises when I try to return the presenter(s) from the presentation.  I've tried numerous ways to return these values but I just cannot get it to work.

 

My latest idea was this:

 

"SELECT fname, lname FROM conf2007_speakers AS s, conf2007_speakpres AS sp WHERE sp.pres_id = '$pres_id' AND s.speak_id = sp.speak_id"

 

but this is obviously wrong...

 

Thanks for any input.

 

Josh

[jellis]

Okay... I made another step (I think)... let me know if this is correct:

 

"SELECT * FROM conf2007_speakpres LEFT JOIN conf2007_speakers ON conf2007_speakpres.speak_id = conf2007_speakers.speak_id WHERE conf2007_speakpres.pres_id = '$pres_id'"

[bubblegum.anarchy]

Try something like this:

 

SELECT concat_ws(' ', lname, fname) AS speaker_name 
FROM Speakers
INNER JOIN Speakpres ON Speakpres.pres_id = $pres_id AND Speakers.speak_id = Speakpres.speak_id 

[jellis]

Thankyou for your help.

 

My left join is working for the time being, but I will change it to the inner join that you speak of and that will reduce it to one query only.

 

Thankyou so much!