Nick,
Thanks for the response. I think I understand what you are saying, but
I wanted to try and get this to work with out adding another table if
possible. I've got some help from the list and below is the query that
seems to work without the need for another table. I guess that brings
up another question, which is more efficient? Another table to do the
lookup or the left join approach with less tables? Anyone have any
idea?
select DISTINCT procedure.name from procedure
left join procedure_equipment on procedure.id =
procedure_equipment.procedureid
left join provider_equipment on procedure_equipment.equipid =
provider_equipment.equipid
where provider_equipment.providerid = '1'
-----Original Message-----
From: Nick [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 20, 2002 8:43 AM
To: Max Sullivan
Subject: Re: SQL guru needed: Is this query possible ?
Hello Max:
I am not sure about what you are trying to get. But looking at your
tables I relaize you might need to add one more table. In Procedure
tabel, id is primary key and it is a foreign key in Procedure_equipment
In Provider table , id is a primary key and it is a foriegn key in
Provider_equipment Now you need a nother table to connect
Provider_Equipment and Procedure_equipment would have equipment_Id as
primary key and they are exist in those two tables as foriegn key that
way you got all your tables connected, and would be easy to get any
query Hope it helps,
----- Original Message -----
From: "Max Sullivan" <[EMAIL PROTECTED]>
Newsgroups: php.db
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 18, 2002 11:28 PM
Subject: SQL guru needed: Is this query possible ?
> I've been racking my brain for too long trying to figure out how to do
> this query, I don't think it should be too hard, but I just can't get
> it right. I've searched and read about every type of join I could
> find to figure out a way of doing this, but I think I'm either
> overlooking something simple or need a different table structure. I'm
> trying this in MySQL.
>
> I've got the following tables broken down for simplicity.
>
> procedure
> ----------------
> id name
> ----------------
> 1 abc
> 2 def
>
> provider
> ----------------
> id name
> ----------------
> 1 ghi
> 2 jkl
>
>
> procedure_equipment (each procedure may require more than one equipid)
> -----------------------------------------
> id procedureid equipid
> -----------------------------------------
> 1 1 1
> 2 1 2
> 3 2 1
>
>
> provider_equipment (each provider may have more than one eqiupid
> -----------------------------------------
> id providerid equipid
> -----------------------------------------
> 1 1 1
> 2 1 2
> 3 2 1
>
>
> What I am trying to do is get procedure.name from procedure where
> provider.id = say 1 and the equipid's from both the
> procedure_equipment and provider_equipment tables need to match.
> Basically I want to get the equipid's from provider_equipment where
> providerid = 1, then get the procedureid from procedure_equipment
> where the provider_equipment's equipid's equal that of the
> procedure_equipments equipid's (1 and 2).
>
> The problem I have is the fact that each equipid will be from their
> own row and I don't know how to equate provider_equipment.equipid 1
> AND 2 and procedure_equipment.equpiid 1 AND 2.
>
> At first I tried the following query. Just to quickly realize that I
> would of course retrieve procedure.name for each equipid In the db.
> SELECT procedure.name from procedure, procedure_equipment,
> provider_equipment WHERE provider_equipment.providerid = '1' AND
> provider_equipment.equipid = procedure_equipment.equipid.
>
> Although I'd like to do this with one query, I've tried to think of a
> way to do it with a couple of queries and again found short comings
> doing it that way as well. I'm beginning to think that the way I have
> the tables set up won't allow me to query the way I want to and that I
> may need to change the table structure. Although it seems (to me
> anyway) that I should be able to get this query to work if I can
> figure out the correct way of running the query.
>
> I'm hoping someone can offer some incite on this, if a query like this
> is possible or not. I don't normally post to the list as I like to
> try to figure these things out on my own, but I'm really stumped on
> this one. I'm hoping there is a way of joining the tables, in a way I
> don't yet know of or understand, to get the results I want :)
>
> I'd really appreciate any help you can offer.
>
> Thanks in advance
>
> Max Sullivan
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
