Conditional union? - 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)

Conditional union?

Author

Topic

keystroke
Starting Member

2 Posts

Posted - 2005-04-01 : 14:48:02

I could use some help trying to select lines with SQL. I can't wrap my
mind around how to handle in order to explain what I want. I guess the
best way is to show you what I want to select with a small example. I
want to select the lines marked with a '*' below. I only want to select
the lines where the size column contains non null values, unless the
null size values are the only choice (like for item3). Otherwords, If
the size column contains only null values for the name/color
combination I would like to see the name/color also. I think I will
have to make two passes over the data to do this.

I'm sure there is a way to do this with SQL.

At first I thought about UNION but I don't know how to make it
conditional. Then I thought about MINUS, but that doesn't work. Then I
got over my head and figured I would have to do something like count the
number of non null values for each name/color combo and select the lines
that only have a non null value.


    table1
    name    color    size
    ------  ------   ----
*   item1   red      L
*   item1   red      M
    item1   red
*   item2   blue     L
*   item2   blue     M
    item2   blue
*   item3   red
*   item3   blue

Can you help?
Thanks.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-01 : 14:57:36

Select name ,color, size
From table1
Where size is not null OR name+color IN
(Select name+color
From table1
Group by name, color
Having count(*) < 2)

Jim
Users <> Logic

keystroke
Starting Member

2 Posts

Posted - 2005-04-01 : 15:44:28

I dont understand what that does JimL, but it doesn't work exactly right for me.
I get every record returned. It may be that I'm not using actual null values
in my test database, just empty values.

This is the closest I've gotten so far. But I don't know if I can use this for my real problem.
The real problem has 6 columns that have to match out of 12 total selected columns
from 6 different tables. Each table has millions of records.
I was hoping I could find a way to do this without passing over the database twice.

[CODE]
SELECT name,color,size FROM table1
EXCEPT
SELECT t1.name,t1.color,t1.size
FROM table1 t1
join table1 t2 ON (t1.name=t2.name AND t1.color=t2.color)
WHERE (t1.size != t2.size AND t1.size = '')
GROUP BY t1.name,t1.color,t1.size
[/CODE]

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-01 : 16:01:27

[code]
Select name, color, size
From table1 As t1
Where (
size is not null
And exists (
Select *
From table1 As t2
Where t2.name = t1.name
And t2.size is not null
)
Or
(
size is null
And Not Exists (
Select *
From table1 As t2
Where t2.name = t1.name
And t2.size is not null
)
)[/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-01 : 16:46:31

1) >>> I only want to select the lines where the size column contains non null values

2) >>> I'm not using actual null values

You do see how that makes it harder for us to be of assistance, yes?

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 16:47:43

Is this a cleanup operation or something?
Or is Your data actualy stored that way.

If it is a cleanup, I might tbe tempted to work with temporary tables,
and when the data is fixed constrain the data so that the situation doesn't arise again.

>> You do see how that makes it harder for us to be of assistance, yes?

funny
rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources