September 17, 2008 at 10:42 am
Problem:
I have two reference tables which join to a data table. I am using full joins as I want to see my output like the following below:
Age Gender Count
13-17 Male 0
13-17 Female 1
13-17 Unknown 0
18-24 Male 2
18-24 Female 3
18-24 Unknown 1
25-29 Male 0
25-29 Female 4
25-29 Unknown 1
30-39 Male 2
30-39 Female 9
30-39 Unknown 0
etc. etc.
Now the values from the Age are coming from a UNION select as there is no lookup table. The other values are coming from a reference table directly are are unique value. The count is the number of records from the data table that matches.
The object is to get the Age to show, all three genders and their respective counts from the data table. If no data, then the count is zero and you still show the Age and Gender. Keep in mind all three Male, Female and Unknown have to show for each age.
The query I have is below. It works to a point. Not fully understanding FULL Cartisian joins I am not understanding what I need to do differently to approach this. If someone can take a look?
Query:
select
case when Age is null then 'Unknown' when Age = 'Unknown' then 'Unknown' else Age end as "Age",
case when g.Gender is null then 'Unknown' when g.Gender = 'Unknown' then 'Unknown' else g.Gender end as "Gender",
count(*)
FROM Table1 T1
FULL JOIN Table1 T2 ON t1.respondentid = t2.respondentid
FULL JOIN (SELECT 'Male' AS Gender
UNION SELECT 'Female' AS Gender
UNION SELECT 'Unknown' AS Gender) G ON G.Gender = T1.code
FULL JOIN (SELECT GroupLabel as "Age" FROM StandardDemoGroups D
WHERE D.DemoID = 2
AND D.DemoReportGroupCode = 'US' and variable = 'AGE' ) AS D ON D.Age = T2.code
group by case when g.Gender is null then 'Unknown' when g.Gender = 'Unknown' then 'Unknown' else g.Gender end,
case when Age is null then 'Unknown' when Age = 'Unknown' then 'Unknown' else Age end
order by 1
Results I get now:
13 - 17Unknown1
18 - 24Unknown1
25 - 29Female1
25 - 29Unknown1
30 - 39Female2
30 - 39Male1
30 - 39Unknown3
40 - 49Female1
40 - 49Male3
40 - 49Unknown4
50 - 64Female5
50 - 64Male2
50 - 64Unknown7
65 +Male1
65 +Unknown1
UnknownFemale9
UnknownMale7
UnknownUnknown18
I am missing in my results 13-17 male 0, 13-17 female 0, and a few others.
All of the articles I find do not deal with multiple reference/lookup tables to a singl data table like this. Any help is appreciated.
:w00t:
September 17, 2008 at 11:39 am
Yes! I finally get to use a cross join for something!
Setup:
CREATE TABLE #Gender(
Gendervarchar(10))
INSERT INTO #Gender(Gender)
VALUES('Male')
INSERT INTO #Gender(Gender)
VALUES('Female')
INSERT INTO #Gender(Gender)
VALUES('Unkown')
CREATE TABLE #Age(
Agevarchar(10))
INSERT INTO #Age(Age)
VALUES('13-17')
INSERT INTO #Age(Age)
VALUES('18-24')
INSERT INTO #Age(Age)
VALUES('25-29')
INSERT INTO #Age(Age)
VALUES('30-39')
INSERT INTO #Age(Age)
VALUES('40-49')
INSERT INTO #Age(Age)
VALUES('50-64')
INSERT INTO #Age(Age)
VALUES('65+')
INSERT INTO #Age(Age)
VALUES('Unknown')
CREATE TABLE #Data(
Agevarchar(10),
Gendervarchar(10))
INSERT INTO #Data
VALUES('13-17','Male')
INSERT INTO #Data
VALUES('13-17','Male')
INSERT INTO #Data
VALUES('13-17','Male')
INSERT INTO #Data
VALUES('13-17','Male')
INSERT INTO #Data
VALUES('13-17','Female')
INSERT INTO #Data
VALUES('13-17','Female')
INSERT INTO #Data
VALUES('13-17','Unknown')
INSERT INTO #Data
VALUES('18-24','Male')
INSERT INTO #Data
VALUES('40-49','Male')
Query:
SELECT A.Age, G.Gender, COUNT(D.Gender)
FROM #Age A CROSS JOIN #Gender G
LEFT JOIN #Data D ON G.Gender = D.Gender AND A.Age = D.Age
GROUP BY A.Age, G.Gender
ORDER BY A.Age
You'd just need to add back in your case statements / additional conditions in the where clause.
Edit: My query is dropping one of the Unknown's for some reason, and as soon as this Visual Studio upgrade quits hammering my machine I'll try to figure out why.
September 17, 2008 at 11:43 am
I am not sure about the rest, but you should understand that a FULL JOIN is NOT the same thing as a Cartesian Join.
"FULL JOIN" is an abbreviation for "FULL OUTER JOIN" which is just a special kind of outer join, as evidenced by the fact that you still have to include the "ON" clause.
"CROSS JOIN" is the operator that produces a full Cartesian product which is also evidenced by the fact that there is no ON clause.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2008 at 11:47 am
I"m glad you got to use Cross Joins! Thank you for your input, it helped greatly with what I was trying to achieve. 😀
September 17, 2008 at 11:56 am
I had not fully realized the difference and do appreciate the explanation of both. Yet a new thing I can catalog in my Brain of Books online...:D
September 17, 2008 at 12:50 pm
JJ, can you post your table structure with some sample data?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply