CODEWITHSUNDEEP
sqlsql-serverselectjoinunion
phazonNinja
phazonNinja

Reputation: 74

SQL Server - Select a single column from two joined table data contexts

Let's say that I need to find all persons that are enabled users of a specific registration type of a person... And I want the PersonId of the user, as well as the related registration's person. A person can have multiple registrations, multiple persons, and multiple users.

SELECT DISTINCT person.PersonId, user.PersonId
FROM Person person
INNER JOIN Registration registration
ON person.PersonId = registration.PersonId
INNER JOIN User user
ON registration.UserId = user.UserId
WHERE person.Type = 1
    AND registration.Type = 2
    AND user.Enabled = 1

The query above works, but I want the first line to select person.PersonId AND user.PersonId in a single column, instead of two separate columns. I know that this is possible with a Union and two separate queries, but I'm trying to avoid the redundancy, if possible. Perhaps there is a more intuitive way to use a Union in this scenario?

Something like:

SELECT DISTINCT person.PersonId UNION user.PersonId
FROM Person person
...

Or

SELECT DISTINCT person.PersonId
UNION SELECT DISTINCT user.PersonId
FROM Person person
...

Upvotes: 0

Views: 140

Answers (3)

phazonNinja
phazonNinja

Reputation: 74

I can see now the purpose of the query wasn't really clear, so here's a few constraints to set the stage:

  • A User is a Person
  • A User has many Registrations
  • A Registration can be for a person

So as a "real-world" example of this; I login as a user and my person-data is loaded. Other persons have the ability to use the account (think Xbox or Netflix) at the same time as me.

This query could be used to find all persons of a given registration type, as well as the person of the user that they are registered on.

Anyway, here is the solution I went with (thanks to John Odom's suggestion):

SELECT DISTINCT PersonPID = person.PersonId, UserPID = user.PersonId
INTO #TempPersonIds
FROM Person person
INNER JOIN Registration registration
ON person.PersonId = registration.PersonId
INNER JOIN User user
ON registration.UserId = user.UserId
WHERE person.Type = 1
    AND registration.Type = 2
    AND user.Enabled = 1

SELECT PersonPID FROM #TempPersonIds
UNION
SELECT UserPID FROM #TempPersonIds
DROP TABLE #TempPersonIds

I realize this probably doesn't perform as well as Turophile's answer, but I'm not expecting to do this query often, so I'm preferring maintainability over performance.

Upvotes: 0

Turophile
Turophile

Reputation: 3405

I don't think you can avoid a UNION. Perhaps someone else can come up with a clever technique, but I'd do it this way:

SELECT person.PersonId
FROM Person person
INNER JOIN Registration registration
ON person.PersonId = registration.PersonId
INNER JOIN User user
ON registration.UserId = user.UserId
WHERE person.Type = 1
    AND registration.Type = 2
    AND user.Enabled = 1
UNION
SELECT user.PersonId 
FROM Person person
INNER JOIN Registration registration
ON person.PersonId = registration.PersonId
INNER JOIN User user
ON registration.UserId = user.UserId
WHERE person.Type = 1
    AND registration.Type = 2
    AND user.Enabled = 1

EDIT Removed CTE example - too much SQL Server lately & I forgot mySQL doesn't support them.

Upvotes: 1

BrianAtkins
BrianAtkins

Reputation: 1349

How about CONCAT the two colums together in the select statement?

SELECT CONCAT (person.PersonId,User.PersonId) AS CombinedID

But given your above query wouldn't the id be the same in person and user?

Upvotes: 0

Related Questions