Who knows the answer fro this select statement

[bhp]

These are the valus in the table:
Siteid Link1 Link2 Link3
30861 NULL NULL NULL
30861 NULL NULL NULL
30861 NULL NULL NULL
30861 NULL NULL NULL
30861 NULL NULL NULL
30861 NULL NULL 542

How do I easily return just the siteid and whatever column holds a value, as it could be link1, link2 or link3
All I need to return in this case is 30861 and 542.
Thanks very much for anyone who can help!!

 

[gmmastros]

Select SiteId,
       Coalesce(Link1, Link2, Link3) As TheLink
From   Table
Where  Coalesce(Link1, Link2, Link3) is Not NULL
[code]



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

what about
30861 NULL 600 542

then only 600 will be returned

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bhp]

Hi George
very clever I have never heard of the Coalesce command that is very neat thanks for your very fast response. Hope to help more folks myself in this forum, regards - James

 

[gmmastros]

I interpreted, as it could be link1, link2 [!]or[/!] link3 to mean, that there could only be 1. If I'm mistaken, then bhp will probably let us know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

if it can be only one of them, why not redesign the table and have 1 link field and a linktype field that can take the values 1,2 or 3
Much better no?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bhp]

maybe as I also have a similar result and not sure whether Georges clever code will work:

Igroup link1 link2 link3
542 NULL NULL 6737
542 NULL NULL 30861
542 NULL NULL 7050
542 NULL 35121 NULL
542 NULL NULL NULL
542 NULL NULL NULL
542 NULL NULL NULL

In this case I just want to return the igroup and any numbers that appear in any of the link fields?

 

[SQLDenis]

george's code will work in this case without a problem, you will get 4 rows of course

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Select IGroup, Link1
From   Table 
Where  Link1 Is Not NULL

Union All

Select IGroup, Link2
From   Table 
Where  Link2 Is Not NULL

Union All

Select IGroup, Link3
From   Table 
Where  Link3 Is Not NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

That last one will accomodate rows that have data in any combination of 'link' columns. For example, if one row had a link1 and a link3, but not a link2, this query will return the link1 and the link3.

As Denis suggests, a table redesign would improve this.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[williamkremer]

Coalesce? Works like Case? Geez.....
And why not use 'where link1 is not null and link2 is not null and link3 is not null' ?

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[bhp]

thanks all for help - BHP

 

[Geoka]

Coalesce Returns the first nonnull expression among its arguments.