From: Peter Brawley
Date: February 14 2006 7:04pm
Subject: Re: joining 3 tables
List-Archive: http://lists.mysql.com/mysql/194886
Message-Id: <43F229B5.8040307@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Amy,

You didn't mention what the problem is with your query...

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

IAC it is easier to understand and debug written in proper join syntax ...

SELECT
  reference.uid
FROM reference AS r
INNER JOIN ref_cat AS rc ON r.ref_cat_id=rc.ref_cat_id
INNER JOIN subject_name AS s ON rc.ref_cat.sub_id=s.sub_id
WHERE s.sub_id = 45
  AND rc.ref_cat_id = 3

and it will be easier to help if you provide the CREATE statements and a 
bit of sample data.

PB

-----

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.
>
> If anyone has any suggestions I would be very thankful.  Please also 
> let me know if I need to provide more information about the problem.
>
> Thank you for your time and help.
>
> Amy Thornton, Electronic Services Specialist
> Information Services, Cook Library
> The University of Southern Mississippi
> 118 College Drive #5053
> Hattiesburg, MS 39406-0001
> Phone: (601) 266-6668
> Fax: (601) 266-6857
> E-mail: amy.thornton@stripped
> http://www.lib.usm.edu/
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.7/259 - Release Date: 2/13/2006