SQL Query - Select Statement

Question

I have a table where the data could be like these below:

CusID | Name | State | PhyAddress

160285 | FYZPZ | NULL | 0

160285 | FYZPZ | NJ | 1

160285 | FYZPZ | NJ | 1

or

CusID | Name | State | PhyAddress

160285 | FYZPZ | NJ xx | 1

or

CusID | Name | State | PhyAddress

160285 | FYZPZ | NULL | 0

160285 | FYZPZ | NJ xx | 0

I need to get the default state for the user. And the logic is:

The result should display the physcial state first (PhyAddress = 1) and if there is no physical address (PhyAddress = 0), then it should display the mailing adress state (PhyAddress = 0). If there are none, the state can be blank.

There are multiple customers in the table and they each can have multiple rows like above and I need to get the default state for each customer.

The resuts needs to show a result of CusID, Name, State

Please help me with the query. TIA

Do you have the ability to change the structure of the table? Could you split out address into a new table, with a type, separate from name? — Brian Hoover
– Brian Hoover, Commented Sep 30, 2011 at 14:34
Nope :( It can break other things in the application. Its a huge application. — Ree
– Ree, Commented Sep 30, 2011 at 14:45

Yahia · Accepted Answer · 2011-09-30 16:48:07Z

1

use (EDIT after comments):

SELECT DISTINCT Y.CusID, Y.Name, ISNULL (Y.S1, Y.S0) AS State
FROM
(
SELECT
X.CusID,
X.Name,
( SELECT MAX ( State ) FROM Address T WHERE T.PhyAddress = 1 AND T.CusID = X.CusID ) AS S1,
( SELECT MAX ( State ) FROM Address T WHERE T.PhyAddress = 0 AND T.CusID = X.CusID ) AS S0
FROM Address AS X
) AS Y

edited Sep 30, 2011 at 16:48

answered Sep 30, 2011 at 15:01

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ree Over a year ago

Its giving me Incorrect syntax near the keyword 'SELECT'. for teh 3rd select. and its not recognizing X.table1 in the 2nd and 4th select statements. And the last SO is also not recognized.

Yahia Over a year ago

what is the real name of your table ?

Cyril Gandon · Accepted Answer · 2011-09-30 14:32:29Z

0

Search the maximum value of PhyAddress:

SELECT CusID, 
       State
FROM table t1
WHERE PhyAddress = 
    (
        SELECT MAX(t2.PhyAddress)
        FROM table t2
        WHERE t2.CusID = t1.CusID
    )

answered Sep 30, 2011 at 14:32

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

1 Comment

Ree Over a year ago

Thank you. PhyAddress is a bit operator and MAX wont work on that one.

dansasu11 · Accepted Answer · 2011-09-30 14:33:57Z

0

What have you tried?

let me give you something to get you going

 Select CusID, Name, case when phyaddress = something then something
        when
        when
        ELSE ' ' END as State

from sometable

where something

answered Sep 30, 2011 at 14:33

dansasu11

9131 gold badge9 silver badges17 bronze badges

Comments

Brian Hoover · Accepted Answer · 2011-09-30 14:42:03Z

0

You could do something like

select CusID, Name, State, PhyAddress from sometable where (CusID, PhyAddress) in
  (select CusID, max(PhyAddress) from sometable)
  group by CusID, Name, State, PhyAddress

answered Sep 30, 2011 at 14:42

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

3 Comments

Brian Hoover Over a year ago

What version of MySQL are you using? I just created the table on my local machine with physAddress as a bit and max worked just fine.

Ree Over a year ago

Its SQL 2008. Its giving me the error: Operand data type bit is invalid for max operator.

Brian Hoover Over a year ago

Well, that makes sense. I've re-tagged the question, because we were all answering this as if it was a MySQL question, not a SQL Server 2008 question

Collectives™ on Stack Overflow

SQL Query - Select Statement

4 Answers 4

2 Comments

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related