Single where clause on multiple joins...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Single where clause on multiple joins...

Author

Topic

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 13:43:31

Ok, I've got a question here. I narrowed down my example for testing purposes, so don't ask me why I want to do this :) Because other tables are involved, but not for the sake of the question.

Ok, consider this, I have one table (DB.VOLUMES) that contains 3 fields ( 1.PROD (key - alpha)
2.Q1 (int)
3.Q2 (int) )

Here's the code :

SELECT A.PROD,
B.VOL_Q1,
C.VOL_Q2
FROM DB.VOLUMES A

LEFT OUTER JOIN(
SELECT PROD,
SUM(Q1) AS VOL_Q1
FROM DB.VOLUMES
GROUP BY PROD
) B
ON A.PROD = B.PROD

LEFT OUTER JOIN(
SELECT PROD,
SUM(Q2) AS VOL_Q2
FROM DB.VOLUMES
GROUP BY PROD
) C
ON A.PROD = C.PROD

WHERE A.PROD = 'CA'

Ok, my question is, since the WHERE clause is the same for all 3 and that these are all left outer joins, I thought of only putting ONE where clause (at the root level).
Now this takes awhile.

If I decide to copy the where clause in my 2 sub-queries, the query returns much faster.
In this case, only one prod was chosen, but if I have a hundred PROD wanted, since the where clause is duplicated, it makes a hell of a number of lines. And in my environment, I'm limited in the number of lines a statement can be.

Anybody can help me with this ?

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 14:05:38

How about:


SELECT A.PROD, VOL_Q1=SUM(ISNULL(B.Q1,0)), VOL_Q2=SUM(ISNULL(B.Q2,0)) 
  FROM DB.VOLUMES A LEFT JOIN DB.VOLUMES B ON A.PROD = B.PROD 
 WHERE A.PROD = 'CA' GROUP BY A.PROD

And in 3 lines!

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 14:08:21

And since you already know what prod id is going to be...why not


SELECT VOL_Q1=SUM(ISNULL(B.Q1,0)), VOL_Q2=SUM(ISNULL(B.Q2,0)) 
  FROM DB.VOLUMES A LEFT JOIN DB.VOLUMES B ON A.PROD = B.PROD WHERE A.PROD = 'CA'

2 Lines!

Brett

8-)

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 14:11:15

Ok, that's what I thought I would get. I already knew that. I'll add why I want to do this. Each summarized column is joined to a table with different conditions. Here would be the whole statement.

SELECT A.PROD,
B.VOL_Q1,
C.VOL_Q2
FROM DB.VOLUMES A

LEFT OUTER JOIN(
SELECT PROD,
SUM(Q1) AS VOL_Q1
FROM DB.VOLUMES T
LEFT OUTER JOIN DB.FLAG R
ON T.PROD = R.PROD
WHERE R.FLAG = 1
GROUP BY T.PROD
) B
ON A.PROD = B.PROD

LEFT OUTER JOIN(
SELECT PROD,
SUM(Q2) AS VOL_Q2
FROM DB.VOLUMES T
LEFT OUTER JOIN DB.FLAG R
ON T.PROD = R.PROD
WHERE R.FLAG = 2
GROUP BY T.PROD
) C
ON A.PROD = C.PROD

WHERE A.PROD = 'CA'

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 14:25:34

OK...the story unravels...

How about this then...


   SELECT A.PROD
	, VOL_Q1=SUM(ISNULL(CASE WHEN R.FLAG = 1 THEN B.Q1 ELSE 0),0)
	, VOL_Q2=SUM(ISNULL(CASE WHEN R.FLAG = 2 THEN B.Q2 ELSE 0),0)
     FROM DB.VOLUMES A 
LEFT JOIN DB.VOLUMES B ON A.PROD = B.PROD
LEFT JOIN DB.FLAG    R ON A.PROD = R.PROD 
    WHERE A.PROD = 'CA' GROUP BY A.PROD

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 14:27:31

Again, though, it holds true, that if you know PROD you can loss the group by...You'll only need it for multiple values of PROD...

Brett

8-)

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 14:41:41

Thanks for the quick replies :)
Ok, do you want to find out more then ?

First table (DB.VOLUMES) has those 3 fields
PROD
Q1
Q2

The second table (FLAG)
PROD
QUARTER
FLAGGED (Y or N)

I'll try to formulate this the best I can. If you want me to paste in my complete code, I can. Here's the thing :

I want the PROD, the sum of Q1 (VOL_Q1) with the FLAGGED of Q1
AND
the sum of Q2 (VOL_Q2) with the FLAGGED of Q2.

VOL_Q1 is calculating by joining the DB.VOLUMES table to the FLAG (let's call it R) table on the PROD (this we know). We need to get the R.FLAGGED where the R.QUARTER = 1. IF R.QUARTER = 1 does NOT exist, I must get R.QUARTER = 4. Last thing, IF the PROD from R does not exist (R.QUARTER IS NULL), then we assume that FLAGGED is 'N'.

Same for VOL_Q2...Meaning :

VOL_Q2 is calculating by joining the DB.VOLUMES table to the FLAG (let's call it R) table on the PROD (this we know). We need to get the R.FLAGGED where the R.QUARTER = 2. IF R.QUARTER = 2 does NOT exist, I must get R.QUARTER = 4. Last thing, IF the PROD from R does not exist (R.QUARTER IS NULL), then we assume that FLAGGED is 'N'.

That's the story

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 14:53:25

Here's the code,
it's not THAT mixed up if you're patient

And it works, it's just darn slow...

SELECT DISTINCT
U.PROD,
E.VOL_Q1,E.FLAGGED1
F.VOL_Q2,F.FLAGGED2

FROM DB.VOLUMES U

LEFT OUTER JOIN TABLE(
SELECT PROD,
SUM(Q1) AS VOL_Q1,
(CASE WHEN FLAGGED IS NULL
THEN 'N' ELSE FLAGGED END) AS FLAGGED1
FROM DB.VOLUMES

LEFT OUTER JOIN TABLE(
SELECT A.PROD,A.FLAGGED
FROM FLAG A
WHERE (QUARTER = '4')
AND NOT EXISTS (
SELECT B.PROD,B.FLAGGED
FROM FLAG B
WHERE (QUARTER = '1')
AND A.PROD = B.PROD
)
UNION
SELECT C.PROD,C.FLAGGED,
FROM FLAG C
WHERE (QUARTER = '1')
) D
ON PROD = PROD
WHERE Q1 <> 0
GROUP BY PROD) E
ON U.PROD = E.PROD

LEFT OUTER JOIN TABLE(
SELECT PROD,
SUM(Q2) AS VOL_Q2,
(CASE WHEN FLAGGED IS NULL
THEN 'N' ELSE FLAGGED END) AS FLAGGED2
FROM DB.VOLUMES
LEFT OUTER JOIN TABLE(
SELECT A.PROD,A.FLAGGED
FROM FLAG A
WHERE (QUARTER = '4')
AND NOT EXISTS (
SELECT B.PROD,B.FLAGGED
FROM FLAG B
WHERE (QUARTER = '2')
AND A.PROD = B.PROD
)
UNION
SELECT C.PROD,C.FLAGGED,
FROM FLAG C
WHERE (QUARTER = '2')
) D
ON PROD = PROD
WHERE Q2 <> 0
GROUP BY PROD) F
ON F.PROD = F.PROD

WHERE IFNULL(E.VOL_Q1,0) + IFNULL(F.VOL_Q2,0) <> 0
AND U.PROD IN ('CA')

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 14:59:08

Sounds like 4 separate requirements..it'd be easier as a union...if the data can be vertical...if they all have to be on 1 row, the you'll need 4 left joins...maybe not..

Best way to do these things is to break them up into small pieces and bring them together...make sure each piece performs, and returns the results you want....

I'll take a look...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 15:00:14

Why SELECT DISTINCT?

Brett

8-)

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 15:03:41

Frankly, I don't know why I need to use the DISTINCT, I was afraid you would ask the question.

For some reason that I can't figure out, if I leave out the DISTINCT, I have duplicate volumes...

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 15:09:07

Ouch..I think I hurt myself...

Help me out though...

an you do this one easy enough?

quote:

VOL_Q1 is calculating by joining the DB.VOLUMES table to the FLAG (let's call it R) table on the PROD (this we know). We need to get the R.FLAGGED where the R.QUARTER = 1. IF R.

And not worry about the other pieces just yet?

Brett

8-)

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 15:20:00

You mean doing this ?

SELECT PROD,
SUM(Q1) AS VOL_Q1,
(CASE WHEN FLAGGED IS NULL
THEN 'N' ELSE FLAGGED END) AS FLAGGED1
FROM DB.VOLUMES U

LEFT OUTER JOIN TABLE(
SELECT A.PROD,A.FLAGGED
FROM FLAG A
WHERE (QUARTER = '1')
ON U.PROD = A.PROD
WHERE Q1 <> 0
GROUP BY PROD

that works fine

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 15:20:19

Now doesn't this give you 2 of your requirements.


SELECT PROD, ISNULL(FLAGGED,'N') AS FLAGGED1, VOL_Q1=SUM(Q1)
  FROM DB.VOLUMES A LEFT JOIN FLAG R ON A.PROD = R.PROD 
 WHERE R.QUARTER = 1

Assuming (a bad thing) That the flag can be only 'Y', 'N', or non existant, then doesn't this give you

1. Quarter 1 where there is a flag
2. Quarter 1 where the flag is set to N or doesn't exist?

Since setting quarter = 1, anything quarter row that is null ignored..

Does that seem correct?

Brett

8-)

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 15:30:33

Mmm...I'm not sure I understand the question...

We want a flag returned, no matter what.

So if, in the FLAG file, the key does exist (meaning R.PROD and R.FLAGGED exists), then we supply the 'Y' or 'N' accordingly.

If it doesn't exist in FLAG, then we supply the FLAG and set it to 'N'

This message board trims every spaces so I can't put example data

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 15:34:12

Oh, and you're assumption was correct,
the FLAGGED can only be 'Y' or 'N' (or NULL of course if the PROD is in VOLUMES but not in FLAG)

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 15:42:27

Sorry, for my explanation up,

For VOL_Q1,

in priority :

IF PROD exists in FLAG AND IF R.QUARTER = 1 exists then Get flag according to R.QUARTER = 1

IF PROD exists in FLAG AND IF R.QUARTER = 1 does NOT exist, Get flag according to R.QUARTER = 4

IF the PROD does NOT exist in FLAG, then FLAGGED for VOL_Q1 is set to 'N'

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 15:51:27

OK, There's relational non existance, and attributive non existance...

And we seem to be blurring the line....

Let's forget about quarter 4 for a minute...I'm getting there, but I want to nail down everything for quarter 1

quote:

1. IF PROD exists in FLAG AND IF R.QUARTER = 1 exists then Get flag according to R.QUARTER = 1

~~2. IF PROD exists in FLAG AND IF R.QUARTER = 1 does NOT exist, Get flag according to R.QUARTER = 4~~

3. IF the PROD does NOT exist in FLAG, then FLAGGED for VOL_Q1 is set to 'N'

Does that take care of everything for Quarter 1 and VOL_Q1

Such that you should always get 2 rows:

"CA", "Y", Amount
"CA", "N", Amount

Would you expect anything different?

Would you agree the only thingh left (besides Q2) is to find the 1 or 2 (which is it) additional rows for Q4?

Brett

8-)

jif26
Starting Member

14 Posts

Posted - 2003-11-04 : 19:26:36

Yes it does

The small sample you gave me is correct.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-05 : 09:04:23

OK then..

The following should give you the Calculation for Q4 where Q1 does not exist...my question comes in, is that don't you need to exclude Q2 (and Q3) as well to exclude overlap?

Since the methods for Q1 and Q4 seem to be identical, your Q4 for Q1 or Q2 would be included.

Seems to be, the independace of Q4 should pnly be done once. Not twice. It's a matter of relationships. There is no direct relationship for the Q4 rows except where Q1 does not exists...

Well I guess you could do


SELECT PROD, ISNULL(FLAGGED,'N') AS FLAGGED1, VOL_Q1=SUM(Q1)
  FROM DB.VOLUMES A LEFT JOIN FLAG R ON A.PROD = R.PROD 
 WHERE R.QUARTER = 4
   AND NOT EXISTS (SELECT * FROM FLAG I1 WHERE I1.PROD = R.PROD AND I.QUARTER = 1)
   AND EXISTS (SELECT * FROM FLAG I2 WHERE I2.PROD = R.PROD AND I.QUARTER = 2)

I'm not sure I follow everything...BUT...thing of your pieces of a query as a pie...all the pieces need to add up as one. That way you can prove you're query is working...

Whatd'ya think?

Brett

8-)

jif26
Starting Member

14 Posts

Posted - 2003-11-05 : 10:26:09

Hold on to your hat...I think with your help I have something beyond fantastic.

I'll post later (later in the day)

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources