select query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

select query

Hi guys, I’m trying to make a select query and I’m having some problems with it. Here’s my problem, I have a filter table that has many fields (FILTER1, FILTER2, FILTER3…) These filter fields can be null, but at least one of them will have a value in it. Depending on the field or fields that have data in them, I will have to compare these values with values in other tables. Does anyone have any idea how I do this? Thanks in advance, Mike
quote:Originally posted by makri Hi guys, I’m trying to make a select query and I’m having some problems with it. Here’s my problem, I have a filter table that has many fields (FILTER1, FILTER2, FILTER3…) These filter fields can be null, but at least one of them will have a value in it. Depending on the field or fields that have data in them, I will have to compare these values with values in other tables. Does anyone have any idea how I do this? Thanks in advance, Mike
Use IS NOT NULL OR <> ” for the condition while joining. You should get the matching values with not null. Thanks, Name
———
Dilli Grg (1 row(s) affected)
I thought of that, but let’s say I have this: FILTER1 FILTER2 FILTER3
——- ——- ——-
NULL 1 NULL
so in the case filters 1 and 3 are null and filter 2 is not so if I do where filter1 is not null, this row won’t be selected even though filter2 has a value in it. So it doesnt solve my problem. Mike
What column are you joining with other table? I am assuming you should have some other column to join to other table besides null. If you are comparing with other table value then you can use isNULL(ColumnName, 0). Thanks, Name
———
Dilli Grg (1 row(s) affected)
ok here’s a more detailed example: VIEW1:
ID1 FILTER1 FILTER2 FILTER3
— ——- ——- ——-
1 1 NULL NULL
2 NULL 3 4
3 NULL NULL 5
VIEW2:
ID2 FILTER1 FILTER2 FILTER3
— ——- ——- ——-
1 1 4 4
2 2 3 5
3 2 3 1
4 1 4 4
5 2 3 4 I want to come up with a query that the result would be: ID2

1
4
5
2 From the ID1=1 of VIEW1 (1 null null), ID2=1, 4 should be selected from VIEW2
From the ID1=2 of VIEW1 (null 3 4), ID2=5 should be selected VIEW2
From the ID1=3 of VIEW1 (null null 5), ID2=2 should be selected VIEW2 The filter fields in VIEW2 cannot be null, and when the filter fields are null in VIEW1 I just want to ignore that field not the whole row. Thanks in advance for all your help, Mike
Good luck if you have more than 3 filter declare @VIEW1 table
(
ID1 int,
FILTER1int,
FILTER2 int,
FILTER3int
)
insert into @VIEW1
select1, 1, NULL, NULL union all
select2, NULL, 3, 4union all
select3, NULL, NULL, 5 declare @VIEW2 table
(
ID2 int,
FILTER1int,
FILTER2 int,
FILTER3int
)
insert into @VIEW2
select1, 1, 4, 4union all
select2, 2, 3, 5union all
select3, 2, 3, 1union all
select4, 1, 4, 4union all
select5, 2, 3, 4 selectv2.ID2
[email protected] v1 inner join @VIEW2 v2
onv1.FILTER1= v2.FILTER1
wherev1.FILTER1is not null
andv1.FILTER2is null
andv1.FILTER3is null union all selectv2.ID2
[email protected] v1 inner join @VIEW2 v2
onv1.FILTER2= v2.FILTER2
wherev1.FILTER1is null
andv1.FILTER2is not null
andv1.FILTER3is null union all selectv2.ID2
[email protected] v1 inner join @VIEW2 v2
onv1.FILTER3= v2.FILTER3
wherev1.FILTER1is null
andv1.FILTER2is null
andv1.FILTER3is not null union all selectv2.ID2
[email protected] v1 inner join @VIEW2 v2
onv1.FILTER1= v2.FILTER1
andv1.FILTER2= v2.FILTER2
wherev1.FILTER1is not null
andv1.FILTER2is not null
andv1.FILTER3is null union all selectv2.ID2
[email protected] v1 inner join @VIEW2 v2
onv1.FILTER1= v2.FILTER1
andv1.FILTER3= v2.FILTER3
wherev1.FILTER1is not null
andv1.FILTER2is null
andv1.FILTER3is not null union all selectv2.ID2
[email protected] v1 inner join @VIEW2 v2
onv1.FILTER2= v2.FILTER2
andv1.FILTER3= v2.FILTER3
wherev1.FILTER1is null
andv1.FILTER2is not null
andv1.FILTER3is not null union all selectv2.ID2
[email protected] v1 inner join @VIEW2 v2
onv1.FILTER1= v2.FILTER1
andv1.FILTER2= v2.FILTER2
andv1.FILTER3= v2.FILTER3
wherev1.FILTER1is not null
andv1.FILTER2is not null
andv1.FILTER3is not null KH
So VIEW2 is your actual data, and you want to filter that data using the parameters from VIEW1, ignoring the NULL values? SELECT v2.ID2
FROM VIEW2 v2
INNER JOIN VIEW1 v1
ON (v2.FILTER1 = v1.FILTER1 OR v1.FILTER1 IS NULL)
AND (v2.FILTER2 = v1.FILTER2 OR v1.FILTER2 IS NULL)
AND (v2.FILTER3 = v1.FILTER3 OR v1.FILTER3 IS NULL) Now that wasn’t so bad, was it?
]]>