Select Distinct + Cross Join Nightmare
-
Can someone please help me? I created this monster query that ran forever.
First the criteria : Table A - item1, item2, item3
Table B - Region, Company, Department, Q1, Q2, Q3
I wanted every region, company, department links to all three items in table A.
But in Table B there are multiple rows of Region Company and Department
The query I had
SELECT T.Region, T.Company, T.Department, T.item1, T.item2, T.item3, MAX(S.Q1) Q1, MAX(Q2) Q2, MAX(Q3) FROM (SELECT b.Region, b.Company, b.Department, a.item1, a.item2, a.item3 FROM TableA a CROSS JOIN (SELECT DISTINCT Region, Compnay, Department FROM TableB) b) T LEFT OUTER JOIN (SELECT Region, Company, Department, SUM(Q1) Q1, SUM(Q2) Q2, SUM(Q3) Q3 FROM TableB GROUP BY Region, Company, Department) S ON T.Company = S.Company AND T.Region = S.Region AND T.Department = S.Department GROUP BY T.Region, T.Company, T.Department, T.item1, T.item2, T.item3
Can someone help me to simplifily it? I did an execution plan, the cross join had a sort in it which caused 65% of the whole query.
Thanks a lot.
-
I'm not sure what is required -
it seems like you should be able to make do with "select * from tableA cross join tableB" ("I wanted every region, company, department links to all three items in table A"...)
I don't understand the "group by"s..
could you post some sample data and the kind of result you're looking for ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Yup sample data and expected results are definitly required here.
-
I want the query to do the following:
Table A - Item1, Item2, Item3 Values X, Y, X
Table B - Region, Company, Department, Q1, Q2, Q3
Values R1, C1, D1, 1, 2, 3
Values R1, C1, D1, 2, 3, 4
Values R1, C1, D2, 2, 2, 2
Values R1, C1, D2, 1, 1, 1
Values R1, C2, D1, 1, 1, 1
Values R1, C2, D1, 2, 1, 1
Result:
R1, C1, D1, X, Y, Z, 3, 5, 7
R1, C1, D2, X, Y, Z, 3, 3, 3
R1, C2, D1, X, Y, Z, 3, 2, 2
Thanks
-
Fallen Angel - give me some time (work always interferes)...the table b part of your query looks like it should be:
SELECT Region, Company, Department, SUM(Q1) Q1, SUM(Q2) Q2, SUM(Q3) Q3 FROM TableB
GROUP BY Region, Company, Department
**ASCII stupid question, get a stupid ANSI !!!** -
Okay fallen angel...let me know if this works:
select A.Region, A.company, A.Department, B.Item1, B.Item2, B.Item3, A.Q1, A.Q2, A.Q3 from
(select Region, Company, Department, SUM(Q1) Q1, SUM(Q2) Q2, SUM(Q3) Q3 FROM TableB
GROUP BY Region, Company, Department)A
cross join tableA B
ps: sorry I got the As and Bs mixed up...makes for bad readability...but you can change it around....
**ASCII stupid question, get a stupid ANSI !!!** -
Why not use meaningfull alias as the best practice commands
. -
I always do Remi...obsessively compulsively so...today is "lack of time"....
**ASCII stupid question, get a stupid ANSI !!!** -
Then what are you doing here
? -
...sais pas...call it addiction and lack of control!
**ASCII stupid question, get a stupid ANSI !!!** -
We'll have to start a group, call it work and meet there
. -
Yeah really - I enjoy "working" on ssc so much that I have started neglecting my real job - the one that keeps the bread buttered...seriously mean to cut down on all the "chat" stuff that can get to be pretty endless...
ps:maybe I should ration it to something like "once a week" or something....like chocolate!
**ASCII stupid question, get a stupid ANSI !!!** -
Not gonna happen
. -
Sushila
It worked. Thanks.
BTW I used a, b,c, d.... as alias too.
-
Glad it works - thanks for getting back!
ps: Don't let Remi hear you say you've been using a, b, c....as aliases...he can get pretty intimidating when he wants to...
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply