SQL Query Help - 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

Transact-SQL (2000)

SQL Query Help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-17 : 07:48:46

Siva writes "Hi All,

I have a situation here to select one record from many records

My select Statement is,
Select t1.Name, t2.Title, t3.AddressType, t3.Address_Line1, t3.Address_line2, t3.city, state
from Table1 t1, Table2 T2, Table3 t3
Where t1.col1 = t2.col1
and t2.col2=t3.col2
and t3.AddressType = 'XA'

But I have up to five addresstypes (XA, RA, BA, SA, etc) for each record, requirement is to collect one addresstype for each record, If the record doesn't have 'XA', then select 'RA' record. If the record doesn't have the XA and RA, then select BA record.

I hope I have explained the situation, Please let me know

Thanks
Siva"

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-17 : 08:05:21

Select t1.Name, t2.Title, t3.AddressType, t3.Address_Line1, t3.Address_line2, t3.city, state
from Table1 t1
inner join Table2 T2 on t1.col2 = t2.col2
inner join Table3 T3 on t2.col2=t3.col2
GROUP BY t1.Name, T2.Title, T3.Address_Line1, T3.Address_Line2, T3.City, State

The group by eliminates the addresstype but returns a single row for each customer.

Sam

0x307836383635364336433646

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-17 : 18:02:18

Someone should post a query that renders the recordset with the addresstype.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-17 : 18:41:45

I'm havin' a conversation with myself in this thread.

How about this?

Select t1.Name, t2.Title, t3.AddressType, t3.Address_Line1, t3.Address_line2, t3.city, state, MAX(t3.AddressType)

from Table1 t1

inner join Table2 T2 on t1.col2 = t2.col2

inner join Table3 T3 on t2.col2=t3.col2

GROUP BY t1.Name, T2.Title, T3.Address_Line1, T3.Address_Line2, T3.City, State

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-17 : 22:18:02

First, in your table of possible address types (you do have this table, right?

) list each address type and it's weight; a higher number means use that address type first. Make sure the "Weight" field is unique. (put a constraint on that field if you need to; it could even be the primary key of the table if you like).

Then, you would say something like:

Select *
FROM
Addresses A1
WHERE Weight = (select max(weight) from Addresses A2 WHERE A1.Customer = A2.Customer)

That is not line for line what you need to write (your Sql was a bit different) but hopefully it will get you started in the right direction.

Never use CASE's or IF's or anything like that in this scenerio-- always store information like which address types to use first in your DATA, not in your database OBJECTS.

Good Luck.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources