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
