PHP Language - SQL query with 3 tables.

Author

SQL query with 3 tables.

Craig Mason

2004-05-18, 10:30 pm

Hi,

I've succesfully managed to select data from 2 tables using an inner
join on the unique field. However, I now need to select data from 3
tables, all using the same unique identifier.

my current attempt:

SELECT ibf_member_extra.photo_type, ibf_member_extra.photo_location ,
ibf_pfields_content.field_1, ibf_pfields_content.field_2 ,
ibf_members.name, ibf_members.interests FROM ibf_members INNER JOIN
ibf_pfields_content ON (ibf_members.id = ibf_pfields_content.member_id)
AND (ibf_members.id = ibf_member_extra.id) WHERE (ibf_members.interests
<> "" ) AND (ibf_pfields_content.field_2 <> "") ORDER BY rand() LIMIT 1

I need:

ibf_member_extra.photo_type
ibf_member_extra.photo_location

ibf_pfields_content.field_1
ibf_pfields_content.field_2

ibf_members.name
ibf_members.interests

Using the ibf_members.id field to identify the records.

Can anyone point me in the right direction?

Many thanks
Craig

Michael Vilain

2004-05-19, 12:30 am

In article <40aab96a$0$20518$cc9e4d1f@news-text.dial.pipex.com>,
Craig Mason <mason@qdolphin.nothx.net> wrote:

> ibf_member_extra.photo_type
> ibf_member_extra.photo_location
>
> ibf_pfields_content.field_1
> ibf_pfields_content.field_2
>
> ibf_members.name
> ibf_members.interests
>
> Using the ibf_members.id field to identify the records.

Try this:

SELECT x.photo_type, x.photo_location,
c.field_1, c.field_2, m.name, m.interests
FROM ibf_members m, ibf_member_extra x, ibf_pfields_content c
WHERE
m.id = c.member_id AND
m.id = x.id AND
(m.interests <> "" ) AND (c.field_2 <> "")
ORDER BY rand() LIMIT 1;

--
DeeDee, don't press that button! DeeDee! NO! Dee...

Sponsored Links