CODEWITHSUNDEEP
sqlsql-serverdatabase
devrique
devrique

Reputation: 538

SQL Server : get IN value for which it was found that row

I'm having difficulties for solving this problem. I'm using Microsoft SQL Server 2014 (ver. 12.0.4100.1).

I have two tables contacts and business. I have an array of contact usernames. I'm fetching a business list from this username array with a subquery in the WHERE, like this:

SELECT business.id, business.name, business.notes
FROM business
WHERE business.id IN (SELECT contacts.business_id
                      FROM contacts
                      WHERE contacts.username IN ('username1', 'username2', 'username3'))

This query is working correctly, but I can't figure out how to select also the value for which every row was found; for example: if I fetch the business from the contact with username 'username1', I want to fetch it and see the result:

business.id = 1
business.name = 'Business 1'
business.notes = 'Notes from business 1.'
contacts.username = 'username1'

Getting that result, I could see easily from what contact came every business.

Thank you in advance!

EDIT: There may be more than one contact for every business, so doing an INNER JOIN would return duplicated business results for every contact. I want to know exactly what contact was the one which was selected on the subquery.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you are looking for a join:

SELECT b.id, b.name, b.notes, c.username
FROM business b JOIN
     contacts c
     ON b.id = c.business_id
WHERE c.username IN ('username1', 'username2', 'username3');

Upvotes: 3

Related Questions