SQL help needed: filter by 2 rows

Transact-SQL
1

Hi there!
I'm sorry for probably a dumb question, I'm new to all this :slight_smile:

I have a table which is:
EMPLOYEE ID | ADDRESS ID | ADDRESS TYPE ID | ADDRESS NAME
1 | 1 | 1 | 12th Street
2 | 3 | 1 | 11th Street
3 | 9 | 2 | 13th Street
4 | 7 | 1 | 13th Street
5 | 4 | 2 | 19th Street
5 | 4 | 3 | 21th Street
...

I need to create a query to show all the employees whose ADDRESS NAME is equal for ADDRESS TYPE ID 1 AND 2.

Can you please help me with that?
Thank you so so much! I'll appreciate if you will be able to offer a few approaches of doing that for my education.

2 
SELECT [EMPLOYEE ID]
FROM dbo.your_table
GROUP BY [EMPLOYEE ID]
HAVING MAX(CASE WHEN [ADDRESS TYPE ID] = 1 THEN [ADDRESS NAME] END) =
    MAX(CASE WHEN [ADDRESS TYPE ID] = 2 THEN [ADDRESS NAME] END)
ORDER BY [EMPLOYEE ID]
1 Like
3

Do I understand correctly that you're using MAX for a String just to get an ANY value?

4

I assumed that for any single, given employee id, there would be only 1 address type id of 1 and 1 address type id of 2. Thus, it wasn't so much to get "any" value but to get "the" value.

If that is not correct, and the same emp id can have multiple type 1 and/or type 2 addresses (??), then you would need to use a different method.

1 Like
5

It will likely make more sense if you look at this query:

SELECT [EMPLOYEE ID],
    MAX(CASE WHEN [ADDRESS TYPE ID] = 1 THEN [ADDRESS NAME] END) AS address_name_1,
    MAX(CASE WHEN [ADDRESS TYPE ID] = 2 THEN [ADDRESS NAME] END) AS address_name_2
FROM dbo.your_table
GROUP BY [EMPLOYEE ID]
ORDER BY [EMPLOYEE ID]
1 Like
6

Thank you so much, Scott! Make total sense.