Question 331447 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL distinct values from multiple columns

I am trying to get distinct values from each column and not distinct row values.

My Query is:

SELECT DISTINCT ac.admin_id , sc.student_id , tc.teacher_id
  from contact c
  join admin_contacts ac on ac.`contact_id` = c.id
  join student_contacts sc on sc.`contact_id`= c.`id`
  join teacher_contacts tc on tc.`contact_id`= c.`id`
 WHERE c.id = 1

Adding GROUP BY only gives unique values as long as I group by a single column, but then I would need to do this query 3 times and group by a different column each time.

Example: This is a representation of what I’m getting with this query. (This table does not list all 27 rows.)
Assuming each table column has 3 different id values, this gives a total of 3^3 = 27 rows

c.admin_id c.student_id c.teacher_id
100 10 1
101 10 1
102 10 1
100 11 1
100 11 2
100 11 3
100 12 1

I want the following: It doesn’t really have to show NULL, just to not give me all possible permutations but rather each value just once: in this example 9 values

This is what I want:

c.admin_id c.student_id c.teacher_id
100 NULL NULL
101 NULL NULL
102 NULL NULL
NULL 10 NULL
NULL 11 NULL
NULL 12 NULL
NULL NULL 1
NULL NULL 2
NULL NULL 3

This is also good, although I don’t think it is possible:

c.staff_id c.student_id c.teacher_id
100 10 1
101 11 2
102 12 3

The structure is as follows: one contact table, one role table each for admin, student and teacher, and 3 tables to combine the contacts to the roles (as it should be possible for each role to be assigned multiple contacts).

I am able to solve this by looping through the results and using only unique values for each column, but I’m looking for a more efficient way to do this and avoid looping through hundreds or thousands of results. (Imagine one contact represents dozens of people like an agent or similar.)

Source data:

Contact table

id name email
1 "foo" "[email protected]"

admin table

id name
100 foo
101 bar
102 bla

student table

id name
10 foo
11 bar
12 bla

teacher table

id name
1 foo
2 bar
3 bla

admin_contacts table

id contact_id admin_id
1 1 100
2 1 101
3 1 102

student_contacts table

id contact_id student_id
1 1 10
2 1 11
3 1 12

teacher_contacts table

id contact_id teacher_id
1 1 1
2 1 2
3 1 3
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks @jarlh for the hint

    I solved it by using UNION and GROUP BY. The query looks like this:

        SELECT ac.admin_id , sc.student_id , tc.teacher_id from contact c
join admin_contacts ac on ac.`contact_id` = c.id
join student_contacts sc on sc.`contact_id`= c.`id`
join teacher_contacts tc on tc.`contact_id`= c.`id`
WHERE c.id = 1
GROUP BY ac.admin_id
    UNION SELECT NULL, sc.student_id, NULL FROM contact c
join student_contacts sc on sc.`contact_id`= c.`id`
WHERE c.id = 1
    UNION SELECT NULL, NULL, tc.teacher_id FROM contact c
join teacher_contacts tc on tc.`contact_id`= c.`id`
WHERE c.id = 1

    This gives the following results which I'm okay with

    c.admin_id c.student_id c.teacher_id
    100 10 1
    101 10 1
    102 10 1
    NULL 10 NULL
    NULL 11 NULL
    NULL 12 NULL
    NULL NULL 1
    NULL NULL 2
    NULL NULL 3

  2. 0

    When you want to get distinct values from the set, use DISTINCT. No need to join all the tables if there are no common columns. Use UNION to combine the result sets.

    This would the query that will give you the desired result:

    SELECT DISTINCT ac.admin_id , NULL, NULL 
FROM contact c
  JOIN admin_contacts ac ON ac.contact_id = c.id
WHERE c.id = 1
UNION
SELECT DISTINCT NULL ,sc.student_id, NULL 
FROM contact c
  JOIN student_contacts sc ON sc.contact_id = c.id
WHERE c.id = 1
UNION
SELECT DISTINCT NULL, NULL, tc.teacher_id
FROM contact c
  JOIN teacher_contacts tc ON tc.contact_id = c.id
WHERE c.id = 1

    See dbfiddle.

    Login or Signup to reply.
  3. 0

    To get your first result you could union then join back to contacts. Note that UNION ALL is more efficient than UNION.

    SELECT
  t.admin_id,
  t.student_id,
  t.teacher_id
from contact c
join (
    select ac.contact_id, ac.admin_id, null as student_id, null as teacher_id
    from admin_contacts ac
    where ac.`contact_id` = c.id

    union all

    select sc.contact_id, null, sc.student_id, null
    from student_contacts sc
    where sc.`contact_id` = c.`id`

    union all

    select tc.contact_id, null, null, tc.teacher_id
    from teacher_contacts tc
    where tc.`contact_id` = c.`id`
) t on t.contact_id = c.contact_id
WHERE c.id = 1;

    For the second result you can join on ROW_NUMBER

    SELECT
  ac.admin_id,
  sc.student_id,
  tc.teacher_id
from contact c
left join (
    select *,
      ROW_NUMBER() OVER (PARTITION BY ac.contact_id ORDER BY ac.admin_id) as rn
    from admin_contacts ac
) ac on ac.`contact_id` = c.id
left join (
    select *,
      ROW_NUMBER() OVER (PARTITION BY sc.contact_id ORDER BY sc.student_id) as rn
    from student_contacts sc
) sc on sc.`contact_id` = c.`id` and sc.rn = ac.rn
left join (
    select *,
      ROW_NUMBER() OVER (PARTITION BY tc.contact_id ORDER BY tc.admin_id) as rn
    from teacher_contacts tc
) tc on tc.`contact_id` = c.`id` and tc.rn = ac.rn
WHERE c.id = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search