CODEWITHSUNDEEP
sqlsql-serverjoin
alejandrovg
alejandrovg

Reputation: 15

SQL Server Query for values within same Table

I have a data table (not very well structured) in which I have the following

ClientID | Parameter | Value 
111..........Street..........Evergreen
111..........Zip................75244
111..........Country.........USA
222..........Street..........Evergreen
222..........Zip................75244
222..........Country.........USA
333..........Street..........Evergreen
333..........Zip................75240
333..........Country.........USA
444..........Street..........Evergreen
444..........Zip................75240
444..........Country.........USA
555..........Street..........Evergreen
555..........Zip................75240
555..........Country.........USA
666..........Street..........Some Street
666..........Zip................75244
666..........Country.........USA

For this I want to Select all those Client ID that are on Street = Evergreen BUT also with ZIP 75244, I have over 700K rows so, exporting all would be a big issue.

My idea was:

SELECT ClientID 
from (select ClientID from table1 where Value = 'evergreen')
Where Zip = '75244'

But it wont give me the accurate results in this case I would like to get the values for ClientIDs 111 and 222 because the match the criteria Im looking for Street= Evergreen adn Zip=75244

Is there a way to do this?

Upvotes: 1

Views: 2120

Answers (3)

msi77
msi77

Reputation: 1632

Try this

Select e.ClientId from Table1 e
Where e.value in ('Evergreen','75244')
GROUP BY e.ClientId
HAVING count(distinct e.value) = 2

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

Select ClientId from MyTable e
inner join MyTable z On e.clientId = z.ClientID
Where e.value = 'Evergreen' and e.Parameter = 'Street'
and z.parameter = 'Zip' and z.Value = '75244'

Just use an alias with a join so you can "use your table twice"

Upvotes: 2

mshsayem
mshsayem

Reputation: 18028

Try this:

select clientid 
from table1 
where (parameter='Street' and value='Evergreen')
and clientid in (select clientid from table1 where parameter='Zip' and value='75244')

Upvotes: 1

Related Questions