joining more than two tables without repeating values

Question

I want to join three tables,

I have three tables user, profession and education where "uid" is primary key for user table and foreign key for other two tables. I want to join these tables to produce result in one single table

user              profession           education
+------+-------+  +-----+----------+  +-----+---------+
| uid  | uName |  | uid | profName |  | uid | eduName |
+------+-------+  +-----+----------+  +-----+---------+
|    1 | aaa   |  |   1 | prof1    |  |   1 | edu1    |
|    2 | bbb   |  |   1 | prof2    |  |   1 | edu2    |
|    3 | ccc   |  |   2 | prof1    |  |   1 | edu3    |
|      |       |  |   3 | prof3    |  |   3 | edu4    |
|      |       |  |   3 | prof2    |  |     |         |
+------+-------+  +-----+----------+  +-----+---------+


Expected output
+------+-------+-----+----------+-----+---------+
| uid  | uName | uid | profName | uid | eduName |
+------+-------+-----+----------+-----+---------+
|    1 | aaa   |   1 | prof1    |   1 | edu1    |
| null | null  |   1 | prof2    |   1 | edu2    |
| null | null  |null | null     |   1 | edu3    |
|    2 | bbb   |   2 | prof1    | null| null    |
|    3 | ccc   |   3 | prof3    |   3 | edu4    |
| null | null  |   3 | prof2    | null| null    |
+------+-------+-----+----------+-----+---------+

I tried following query

select u.uid ,u.uName,p.uid , p.profName,e.uid,e.eduName
from  user  u inner join profession p on u.uid=p.pid
inner join education e on u.uid = e.uid
where u.uid=p.uid
and u.uid=e.uid
and i.uid=1

Which gives me duplicate values

+------+-------+-----+----------+-----+---------+
| uid  | uName | uid | profName | uid | eduName |
+------+-------+-----+----------+-----+---------+
|    1 | aaa   |   1 | prof1    |   1 | edu1    |
|    1 | aaa   |   1 | prof2    |   1 | edu1    |
|    1 | aaa   |   1 | prof1    |   1 | edu2    |
|    1 | aaa   |   1 | prof2    |   1 | edu2    |
|    1 | aaa   |   1 | prof1    |   1 | edu3    |
|    1 | aaa   |   1 | prof2    |   1 | edu3    |
+------+-------+-----+----------+-----+---------+

Is there a way to get the output with not repeating the values. Thanks

Answer 1

Bit of a swine this one.

I agree with @GordonLinoff that ideally this presentation would be done on the client side.

However, if we wish to do it in SQL, then the basic approach is that you have to get the maximum number of rows that will be consumed by each user (based on a count of how many entries they have in each of the professions and educations tables, and then of these counts, the max count).

Once we have the number of rows required for each user, we expand the rows out for each user as necessary using a numbers table (I've included a number generator for the purpose).

Then we join each table on, according to the uid and the row number of the entry in the joined table relative to the row number of the "expanded" rows for each user. Then we select the relevant columns, and that's us done. Pay the nurse on the way out!

WITH 
number_table(number) AS
(
    SELECT
        (ones.n) + (10 * tens.n) + (100 * hundreds.n) AS number

    FROM --available range 0 to 999
         (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS ones(n)
        ,(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS tens(n)
        ,(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS hundreds(n)
)

,users(u_uid, userName) AS
(
    SELECT 1, 'aaa'
    UNION ALL
    SELECT 2, 'bbb'
    UNION ALL
    SELECT 3, 'ccc'
)

,professions(p_u_uid, profName) AS
(
    SELECT 1, 'prof1'
    UNION ALL
    SELECT 1, 'prof2'
    UNION ALL
    SELECT 2, 'prof1'
    UNION ALL
    SELECT 3, 'prof3'
    UNION ALL
    SELECT 3, 'prof2'
)
,educations(e_u_uid, eduName) AS
(
    SELECT 1, 'edu1'
    UNION ALL
    SELECT 1, 'edu2'
    UNION ALL
    SELECT 1, 'edu3'
    UNION ALL
    SELECT 3, 'edu4'
)

,row_counts(uid, row_count) AS
(
    SELECT u_uid, COUNT(u_uid) FROM users GROUP BY u_uid
    UNION ALL
    SELECT p_u_uid, COUNT(p_u_uid) FROM professions GROUP BY p_u_uid
    UNION ALL
    SELECT e_u_uid, COUNT(e_u_uid) FROM educations GROUP BY e_u_uid
)

,max_counts(uid, max_count) AS
(
    SELECT uid, MAX(row_count) FROM row_counts GROUP BY uid
)

SELECT 
    u_uid
    ,userName
    ,p_u_uid
    ,profName
    ,e_u_uid
    ,eduName

FROM
    max_counts 

INNER JOIN 
    number_table ON number BETWEEN 1 AND max_count

LEFT JOIN 
    (
        SELECT u_uid, userName, ROW_NUMBER() OVER (PARTITION BY u_uid ORDER BY userName) AS user_match
        FROM users
    ) AS users
    ON u_uid = uid
    AND number = user_match

LEFT JOIN 
    (
        SELECT p_u_uid, profName, ROW_NUMBER() OVER (PARTITION BY p_u_uid ORDER BY profName) AS prof_match
        FROM professions
    ) AS professions
    ON p_u_uid = uid
    AND number = prof_match

LEFT JOIN 
    (
        SELECT e_u_uid, eduName, ROW_NUMBER() OVER (PARTITION BY e_u_uid ORDER BY eduName) AS edu_match
        FROM educations
    ) AS educations
    ON e_u_uid = uid
    AND number = edu_match

ORDER BY 
     IIF(COALESCE(u_uid, p_u_uid, e_u_uid) IS NULL, 1, 0)   ASC --nulls last
    ,COALESCE(u_uid, p_u_uid, e_u_uid)                      ASC
    ,IIF(COALESCE(p_u_uid, e_u_uid) IS NULL, 1, 0)          ASC --nulls last
    ,COALESCE(p_u_uid, e_u_uid)                             ASC
    ,IIF(e_u_uid IS NULL, 1, 0)                             ASC --nulls last
    ,e_u_uid                                                ASC

And the results:

u_uid       userName p_u_uid     profName e_u_uid     eduName
----------- -------- ----------- -------- ----------- -------
1           aaa      1           prof1    1           edu1
NULL        NULL     1           prof2    1           edu2
NULL        NULL     NULL        NULL     1           edu3
2           bbb      2           prof1    NULL        NULL
3           ccc      3           prof2    3           edu4
NULL        NULL     3           prof3    NULL        NULL

Answer 2

Did you try the distinct keyword?

select DISTINCT u.uid ,u.uName,p.uid , p.profName,e.uid,e.eduName
from  user  u inner join profession p on u.uid=p.pid
inner join education e on u.uid = e.uid
where u.uid=p.uid
and u.uid=e.uid
and i.uid=1