RE: [PHP-DB] SQL guru needed: Is this query possible ?

Ryan, Thanks for the Response The query you gave me to play with is very close to what I'm trying to do. I was trying to get "all procedure names that a provider can perform with the equipment that they own". I've modified your query slightly to pull results from procedure instead of providers. I added DISTINCT procedure.name and removed the first left join. The query below seems to do what I need. After looking at what you gave me it all made much more sense :). I just couldn't get it right by myself. I had a couple of questions I wanted to ask, I'm hoping you can answer. What purpose does the first left join serve in your query? (left join [procedure] on [procedure].id = [procedure].id) I removed it from the query below, but I want to make sure it didn't serve a purpose that I can't see. I seem to get the results I want either way. 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' Thanks again for the Response, It was the kick I needed to get me moving in the right direction. Max -----Original Message----- From: Ryan Jameson (USA) [mailto:RJameson@usa.ibs.org] Sent: Wednesday, June 19, 2002 8:05 AM To: php-db@lists.php.net Subject: RE: [PHP-DB] SQL guru needed: Is this query possible ? Try This: select * from provider left join [procedure] on [procedure].id = [procedure].id 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' I'm sketchy on what exactly you want but this should be a good start. I think the stump was the unconditional join to procedure. I made the condition always true. <>< Ryan -----Original Message----- From: Max Sullivan [mailto:max@artco.com] Sent: Tuesday, June 18, 2002 11:28 PM To: php-db@lists.php.net Subject: [PHP-DB] 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