Query issue - SQL Server Forums

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)

Query issue

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-09-10 : 19:43:47

I have the following query

Select a.*,b.*
From T1 a cross Join t2 b
Where
((b.PriorityID = 1 and a.daysleft > 30)
or
(b.PriorityID = 2 and a.daysleft > 15 and a.daysleft <=30)
or
(b.PriorityID = 3 a.dayslef <=15))

t2 has 3 items in it
PriorityID,Col2
1,Low
2,Medium
3,High

For some reason, even when I specify the specific priorityID, In table t2, since it is cross joined I am getting all three priorities back for each record. I only want the priorityid that is specified in the where clause to come back.

How am I supposed to do this? Cross Join does not seem like it is the right approach.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-10 : 21:33:09

Please post your table with sample data and the result that you want.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-10 : 22:46:57

This works OK for me. You should double-check you code.


Select
	a.*,
	b.*
From
	(select daysleft= number 
	 from f_table_number_range(0,33) ) a 
	cross Join
	(select PriorityID= number 
	 from f_table_number_range(1,3) ) b
Where 
	(
	(b.PriorityID = 1 and a.daysleft > 30)
	or 
	(b.PriorityID = 2 and a.daysleft > 15 and a.daysleft <=30)
	or
	(b.PriorityID = 3 and a.daysleft <=15))
order by
	1,2

Results:


daysleft    PriorityID  
----------- ----------- 
0           3
1           3
2           3
3           3
4           3
5           3
6           3
7           3
8           3
9           3
10          3
11          3
12          3
13          3
14          3
15          3
16          2
17          2
18          2
19          2
20          2
21          2
22          2
23          2
24          2
25          2
26          2
27          2
28          2
29          2
30          2
31          1
32          1
33          1

(34 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources