CODEWITHSUNDEEP
sqlsql-serverjoin
MikeB
MikeB

Reputation: 1659

Join 3 tables in one column and sort them

I'm completely new to SQL and need a bit of help.

I have 3 tables in my SQL Server 2008 database. In each table there are different kinds of users (coworkers, students, teachers). All of them have Nicknames. There is also a table with all users.

I would like to join all of them into one single table with one column called nickname. In this column the three different types of users are supposed to be sorted in packages like so regardless of their nickname:

coworker1 coworker2 coworker2 student1 student2 student3 teacher1 teacher2

I tried the following way:

SELECT A.Nickname, B.Nickname, C.Nickname, D.Nickname
FROM users AS A LEFT OUTER JOIN coworkers AS B ON
A.Nickname = B.Nickname
LEFT OUTER JOIN teachers AS C ON
A.Nickname = C.Nickname
LEFT OUTER JOIN students AS D ON
A.Nickname = D.Nickname

Well that does not work at all and gives me a table with 4 columns each full with NULLs and unsorted users

Thanks HABO this did it for me:

select Nickname from (
  SELECT Coalesce( B.Nickname, C.Nickname, D.Nickname, A.Nickname ) as Nickname,
    case
      when B.Nickname is not NULL then 1
      when C.Nickname is not NULL then 3
      when D.Nickname is not NULL then 2
      else 0 end as Package
  FROM users AS A LEFT OUTER JOIN coworkers AS B ON
  A.Nickname = B.Nickname
  LEFT OUTER JOIN teachers AS C ON
  A.Nickname = C.Nickname
  LEFT OUTER JOIN students AS D ON
  A.Nickname = D.Nickname ) as Elmer
order by Package, Nickname

Upvotes: 0

Views: 150

Answers (2)

Brendan Ritchie
Brendan Ritchie

Reputation: 2345

It sounds like you could just union and order your three user type tables instead of doing a bunch of joins.

Try something like this:

SELECT s.nickname nickname, ..., 'student' type FROM students s
UNION
SELECT t.nickname nickname, ..., 'teacher' type FROM teachers t
UNION
...
ORDER BY type;

Does this closer to what you are trying to accomplish?

Upvotes: 1

HABO
HABO

Reputation: 15816

If you only want the non-NULL column output:

SELECT Coalesce( B.Nickname, C.Nickname, D.Nickname, A.Nickname ) as Nickname
FROM users AS A LEFT OUTER JOIN coworkers AS B ON
A.Nickname = B.Nickname
LEFT OUTER JOIN teachers AS C ON
A.Nickname = C.Nickname
LEFT OUTER JOIN students AS D ON
A.Nickname = D.Nickname

Note that the columns have been rearranged so that the nickname from users is the last choice.

Your comment regarding sorting into packages is unclear to me. Do you mean something like this:

select Nickname from (
  SELECT Coalesce( B.Nickname, C.Nickname, D.Nickname, A.Nickname ) as Nickname,
    case
      when B.Nickname is not NULL then 1
      when C.Nickname is not NULL then 3
      when D.Nickname is not NULL then 2
      else 0 end as Package
  FROM users AS A LEFT OUTER JOIN coworkers AS B ON
  A.Nickname = B.Nickname
  LEFT OUTER JOIN teachers AS C ON
  A.Nickname = C.Nickname
  LEFT OUTER JOIN students AS D ON
  A.Nickname = D.Nickname ) as Elmer
order by Package, Nickname

Upvotes: 0

Related Questions