Query for this reqt - 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 2005 Forums

Transact-SQL (2005)

Query for this reqt

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-12-09 : 03:33:49

i have 4 tables each table having 4 columns.


Tbl_First 
First Name | White1 | Blac1 | Red1

Tbl_Second
Last Name | White2 | Blac2 | Red2

Tbl_Third
Middle Name | White3 | Blac3 | Red3

Tbl_Fourth
Zip Code | White4 | Blac4 | Red4

All the tables contains numeric data except for name coumn. I want to retrive a data from all tables by sending parameters of one record(.ie firstname, lastname, middlename and his zipcode). we will get four rows from all tables. Now i want to find out which White contains the largest value(i.e Among white1,white2,white3,white4)

is there any way to write a single query for this reqt. ?
Or
As i explanied above, need to write 4 quereis to retrive data from 4 tables and then check with .net code to find the largest one ?

developer :)

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 03:41:09

Hi,

select max(white) from (
select white1 as white from Tbl_First where First Name = First Name union all
select white2 as white from Tbl_Second where Last Name = Last Name union all
select white3 as white from Tbl_Third where Middle Name = Middle Name union all
select white4 as white from Tbl_Fourth where Zip Code = Zip Code
) as t

Thanks,
vikky.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-09 : 03:52:36

quote:
Originally posted by vikky

Hi,

select max(white) from (
select white1 as white from Tbl_First where First Name = First Name union all
select white2 as white from Tbl_Second where Last Name = Last Name union all
select white3 as white from Tbl_Third where Middle Name = Middle Name union all
select white4 as white from Tbl_Fourth where Zip Code = Zip Code
) as t

Thanks,
vikky.

Please check the column names....

-------------------------
R...

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-12-09 : 04:10:05

Hi Vikky,

Thanks for Query... It works perfect.

quote:
Originally posted by vikky

Hi,

select max(white) from (
select white1 as white from Tbl_First where First Name = First Name union all
select white2 as white from Tbl_Second where Last Name = Last Name union all
select white3 as white from Tbl_Third where Middle Name = Middle Name union all
select white4 as white from Tbl_Fourth where Zip Code = Zip Code
) as t

Thanks,
vikky.

developer :)

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 05:28:38

ok ur welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources