Self join

[j9]

I'm having a problem with a self join. Here is how I wrote the query:

SELECT s.ssn, count(g1.ssn) as Count1, count(g2.ssn) as Count2

FROM security s, grads g1, grads g2

WHERE s.ssn*=g1.ssn AND
s.ssn*=g2.ssn AND
g1.role='Major' AND
g2.role='Co-Major'

GROUP BY s.ssn

--The problem is that my numbers come out wrong-I think rows are being counted twice between the two 'grads' tables. Obviously, if I write two queries I get the correct numbers. But if possible I want the 3 columns (ssn, Count1, Count2) all in the same query.

I thought about using views, but I need to pass parameters and I'm not sure you can pass parameters into a view.

 

[tlbroadbent]

Add a test for NULL to your Where clause.

SELECT s.ssn, count(g1.ssn) as Count1, count(g2.ssn) as Count2

FROM security s, grads g1, grads g2

WHERE s.ssn*=g1.ssn AND
s.ssn*=g2.ssn AND
(g1.role='Major' Or g1.role Is Null) AND
(g2.role='Co-Major' Or g2.role Is Null)

GROUP BY s.ssn

I also recommend ANSI standard JOIN syntax.

SELECT s.ssn, count(g1.ssn) as Count1, count(g2.ssn) as Count2

FROM security s Left Join grads g1 On s.ssn=g1.ssn
Left join grads g2 On s.ssn=g2.ssn

WHERE (g1.role='Major' Or g1.role Is Null) AND
(g2.role='Co-Major' Or g2.role Is Null)

GROUP BY s.ssn Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[j9]

No luck, but thanks for the input (the result I got was identical count totals in columns Count1 and Count2).

To clarify, the output I want is:

column SSN: each SSN contained in the security table

column Count1: a count of all rows for the given ssn where role='Major'

column Count2: a count of all rows for the same given ssn where role='Co-Major'

 

[tlbroadbent]

Sorry, I didn't see this before. You want to count the Role column not the SSN column.

count(g1.role) as Count1, count(g2.role) as Count2
Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[j9]

Still no luck! I got the same exact results. ?????

 

[tlbroadbent]

Try this. It is a better solution, I believe.

SELECT Distinct
s.ssn,
Count1=(Select count(ssn) From grads
Where ssn=s.ssn And role='Major')
Count2=(Select count(ssn) From grads
Where ssn=s.ssn And role='Co-Major')
FROM security s Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[j9]

BINGO! Thank you for all of your help!