List:General Discussion« Previous MessageNext Message »
From:Thomas Spahni Date:February 14 2006 5:25pm
Subject:Re: joining 3 tables
View as plain text  
On Tue, 14 Feb 2006, Amy Thornton wrote:

> I am trying to join 3 tables together.
>
> Table A has 3 fields: sub_id, subject, id
> Table B has 3 fields: ref_cat_id, sub_id, ref_cat
> Table C has 7 fields: uid, ref_cat_id, etc.
>
> I am trying to join Table A and B over sub_id and join B and C over ref_cat_id
> while pulling in the value of sub_id and ref_cat_id from a php program
>
> I tried doing it this way:
>
> select reference.uid from reference, subject_name, ref_cat where
> subject_name.sub_id = '45' and ref_cat.ref_cat_id = '3' and
> ref_cat.ref_cat_id = reference.ref_cat_id and subject_name.sub_id =
> ref_cat.sub_id
>
> with the '45' and '3' being the values coming from my variables in
> PHP.  We only have MySql 4.0 so I can't use a subquery.

Hi Amy,

I suggest you try the following:

SELECT reference.uid FROM reference
  INNER JOIN ref_cat      ON reference.ref_cat_id = ref_cat.ref_cat_id
  INNER JOIN subject_name ON ref_cat.sub_id = subject_name.sub_id
WHERE subject_name.sub_id = 45
  AND ref_cat.ref_cat_id  =  3;

HTH
Thomas Spahni

Thread
joining 3 tablesAmy Thornton14 Feb
  • Re: joining 3 tablesThomas Spahni14 Feb
  • Re: joining 3 tablesPeter Brawley14 Feb