MySQL query three tables omit duplicates in columns

Question

Got 3 Tables: pers, skills, articles (persons have n skills and wrote n articles)

(T1) pers

1  John
2  Joe

(T2) skills

1  John_sings    
1  John_laughs
2  Joe_runs

(T3) article

1  John_article_1
2  Joe_article_1
3  Joe_article_2

I expect:

John - John_laughs - John_article_1
John - John_sings  - [NULL]
Joe  - Joe_runs    - Joe_article_1
Joe  - [NULL]      - Joe_article_2

For we have 2 separate 1:n relations a consecutive join won't do it -> not T1 x T2 x T3, rather (T1 x T2) x (T1 x T3) according to this question.

I've tried:

SELECT child1.id,
       child1.name,
       child1.skill,
       child2.title
FROM
  (SELECT pers.id,
          pers.name,
          skills.skill
   FROM pers
   LEFT JOIN skills ON pers.id = skills.pers_id) child1
INNER JOIN
  (SELECT pers.id,
          article.title
   FROM pers
   LEFT JOIN article ON pers.id = article.pers_id) child2 ON child1.id = child2.id

but this shows

John - John_laughs - John_article_1
John - John_sings  - John_article_1
Joe  - Joe_runs    - Joe_article_1
Joe  - Joe_runs    - Joe_article_2

Obviously, I don't want "Joe_runs" two times, neither "John_article_1" two times.

Appreciate any suggestion!

jcho360 - thanks, you mean group by the IDs of article / skills ? Unfortunately it eats away either an article or a skill... (tried it both within the nested SELECTs and at the end, for the whole query) — user1571271, Aug 02 '12 at 14:18
Did you solved it?, if not, can you put some information in SQLfiddle.com to allow us make some tests? — jcho360, Aug 06 '12 at 12:26
Sure: http://sqlfiddle.com/#!2/8ea2c/4/0 Solved it not by means of an sql query, but via PHP. Still interested in a sql-solution, though :-) — user1571271, Aug 08 '12 at 14:38

score 0 · Answer 1 · edited May 23 '17 at 10:24

Assign a row number to each article per person and each skill per person.

So now your data will be like

T1) pers
PId Name
1   John

2   Joe

(T2) skills
PId Skill      Rank

1   John_sings   1

1   John_laughs  2

2   Joe_runs     1

(T3) article
PId Article        Rank
1   John_article_1 1
2   Joe_article_1  1 
2   Joe_article_2  2

Now Full Outer Join Skills and Article on Pid and Rank, it should give you

PID   Skill       Article
1     John_Sings  john_Article_1
1     John_Laughs Null
2     Joe_runs    Joe_article_1
2     Null        Joe_Article_2

Now join this with person to get the desired results.

This SO question explains how to assign ranks/rownumber in a select. MySQL - Get row number on select

score 0 · Answer 2 · answered Aug 02 '12 at 15:12

0

The result you want is a typical LEFT JOIN. You should do:

SELECT * FROM T1
LEFT JOIN T2 on (T1.id=T2.id)
LEFT JOIN T3 on (T1.id=T3.id);

This is compatible with 2 separate 1:n relations, note that this joins T1-T2 and T1-T3

answered Aug 02 '12 at 15:12

golimar

2,419
1
22
33

golimar: i am afraid, but it provides the old duplicates, thanks though. – user1571271 Aug 03 '12 at 07:28

MySQL query three tables omit duplicates in columns

2 Answers2