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.
id     name
1      abc
2      def
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

Reply via email to