problem with join

Question

question

tommy 1 asked Jan 7, '10

problem with join

Hey im new to sql and am having some trouble could someone pls help? i need to output the names of all hospitals with more than one department, have tried a few different ways but i cant seem to crack it =/

                    
Table Meddept                    
md_ID                    
Hospital_ID                    
Dept                    
                    
Table Hospital                    
Hospitals_ID                        
Name    	                    
Address 	                    
Capacity                    
                    
SELECT Hospital.name, COUNT(Meddept.Hospitals_ID) AS NoOfDept                     
FROM Meddept, Hospital                     
WHERE Meddept.hospitals_ID = Meddept.hospitals_ID                    
GROUP BY Hospital.name                    
HAVING COUNT(meddept.dept) > 1;

joins aggregates

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tommy 1 commented · Jan 07, 2010 at 05:01 PM

lol never mind i cracked it =D

0 ·

Answer 1 · 2010-01-08T05:13:45Z

Kristen answered Jan 8, '10

            
SELECT Hospital.name, COUNT(*) AS NoOfDept             
FROM Meddept, Hospital            
WHERE Meddept.hospitals_ID = Meddept.hospitals_ID            
GROUP BY Hospital.name            
HAVING COUNT(*) > 1;

COUNT(*) should work to tell you the number of records in the JOIN between Meddept and Hospital.

You have the same table on both sides of your WHERE clause which is either a typo, or if you actually have it in your code will be causing a Cartesian Join WHERE Meddept.hospitals_ID = Meddept.hospitals_ID

Much better would be to use a JOIN rather than that antequated syntax:

            
FROM Meddept            
    JOIN Hospital            
        ON Hospital.Hospitals_ID = Meddept.hospitals_ID

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question