[SOLVED] Query multiple tables for repeating table

[biggieuk]

Hi,

 

I have two tables like so:

 

session

+----------+----------+----------+----------+

|  groupid  |  group    |  mentor  |  room    | 

+----------+----------+----------+----------+

|      1      |  Sports  |  bob      |  l657      |

 

 

resource

+----------+----------+----------+----------+

| resourceid|  name    |    path    |  groupid  | 

+----------+----------+----------+----------+

|      1      |  ball      | ../1.jpg  |      1      |

 

 

Resources are assigned to a certain group and so the groupid is added to the resource table.

 

I need to display a repeating table but i am unsure about the sql code that will enable me to get the following:

 

+----------+----------+----------+----------+

|  group    |  mentor  |    room  |  path    | 

+----------+----------+----------+----------+

 

i need the path column to only contain a value for the groups that have had a resource assigned to them.

 

Help would be appreciated.

 

 

[Barand]

SELECT s.group, s.mentor, s.room, r.path

FROM session s JOIN resource r ON s.groupid = r.groupid

[biggieuk]

thanks for this however i don't think i explained what i wanted very clearly.

 

This returns the rows of data that have a resource applied to them only. I need all of the rows from session to be displayed and if a resource has been added to that session then the path is displayed, otherwise the path field is left blank.

 

I have tried:

 

SELECT s.group, s.mentor, s.room, r.path

FROM sessions s JOIN resource r

 

This returns the whole of the sessions table and the same path for all of the sessions which shouldn't be the case.

[Barand]

in which case

 

SELECT s.group, s.mentor, s.room, r.path

FROM session s LEFT JOIN resource r ON s.groupid = r.groupid

[biggieuk]

brilliant, thanks!