All Both Any - Data fetching problem

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

General SQL Server Forums

New to SQL Server Programming

All Both Any - Data fetching problem

Author

Topic

Chaits
Starting Member

4 Posts

Posted - 2011-01-24 : 11:58:54

Hi,

Let me recreate the condition, I have a table three columns, namely, emplid, address type(home, business) and address information.
For every address type there will be a new row.
Now, I have to get all those employee which have rows corresponding to different address_type. As in,

Row 1 - emplid1 business 34 ABC, USA
Row 2 - emplid2 home 34 DEF, USA

I should get list of all emplid like this.

Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:04:34

isnt this enough?

SELECT empid,AddressType,AddressInfo FROM table ORDER BY empid,Addresstype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-24 : 12:05:51

select empid
from tbl
group by empid
having count(*) > 1

or maybe
select empid
from tbl
group by empid
having count(distinct [address type]) > 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Chaits
Starting Member

4 Posts

Posted - 2011-01-24 : 12:19:58

@ Nigel - I missed to mention that multiple row can exist for same business_type address. so it going to give those emplids which have multiple rows for even the same address_type.

I tried this approach but feels that it can be btr -

select distinct a.emplid from PS_TABLE1 a, PS_TABLE1 b
where a.emplid = b.emplid
and a.ADDRESS_TYPE = 'BUSINESS' and
b.ADDRESS_TYPE = 'HOME'
-- PL. IGNORE CASE SENSTIVITY FOR ADDRESS_TYPE

Please review. And let me know if you can give any alternative sloution.

@Visakh - It won't help for the reason mentioned above. Anyways, thnx for giving your time in reading this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:24:09

ok i think what you need is this


SELECT ps.* 
FROM PS_TABLE1 ps
INNER JOIN (SELECT emplid ,COUNT(DISTINCT AddressType) AS Cnt
            FROM PS_TABLE1
            GROUP BY emplid) ps1
ON ps1.emplid = ps.emplid
WHERE ps1.Cnt > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-24 : 12:25:11

The second one I gave caters for multiple rows of same business type - only returns those with different business type entries.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Chaits
Starting Member

4 Posts

Posted - 2011-01-24 : 12:38:16

Thanks much guys...Both of them worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:37:56

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources