Select distinct on multiple columns with where statement

Question

I want to select each available entry for each column once. This problem was solved with

SELECT DISTINCT a from my_table
UNION
SELECT DISTINCT b from my_table
UNION
SELECT DISTINCT c from my_table
UNION
SELECT DISTINCT d from my_table

in this question: MySQL SELECT DISTINCT multiple columns

I want to go further and use the same WHERE statements on each subquery. Is there any way without defining the WHERE each time? My current query would look like this:

SELECT DISTINCT a from my_table WHERE a='a' AND b=1 AND c='.' AND d='ab'
UNION
SELECT DISTINCT b from my_table WHERE a='a' AND b=1 AND c='.' AND d='ab'
UNION
SELECT DISTINCT c from my_table WHERE a='a' AND b=1 AND c='.' AND d='ab'
UNION
SELECT DISTINCT d from my_table WHERE a='a' AND b=1 AND c='.' AND d='ab'

All parameters don't have to be given, I just want to show the maximum that has to be possible. Is there any way to write this shorter?

I use PHP with doctrine, if that is any help.

Thanks in advance!

Example: my_table:

  a  |  b  |  c  |  d  
-----+-----+-----+-----
  a  |  0  |  .  | ab
  b  |  0  |  -  | ag
  a  |  1  |  .  | cfd
  c  |  1  |  .  | b
  a  |  1  |  -  | ab
  c  |  1  |  -  | cfd

should give this result (without where statement):

  a  |  b  |  c  |  d  
-----+-----+-----+-----
  a  |  0  |  .  | ab
  b  |  1  |  -  | ag
  c  |     |     | cfd
     |     |     |  b

And with WHERE b=0 statement:

  a  |  b  |  c  |  d  
-----+-----+-----+-----
  a  |  0  |  .  | ab
  b  |     |  -  | ag

EDIT: changed subqueries to UNION and made the data types fit to the example

The query from the original post uses `GROUP_CONCAT` whereas the query you cite doesn't Your query won't execute if any of the subqueries returns more than one row. — Giorgos Betsos, Nov 03 '16 at 14:21

Mark Adelsberger · Accepted Answer · 2016-11-03T17:14:03.603

1

UPDATE: Well, I originally wrote up a generic SQL solution for this problem, not realizing that MySQL apparently doesn't allow for it.

So if you can create a view, that may be the lightest-weight solution. (The view's defining query would be the same as the select in my original solution's with clause.)

Alternately you could create a temporary table. Maybe a little more resource-intensive if there's much data, but less likely that anyone would restrict the required permissions.

For the record, original solution was as follows:

with my_filtered as (
    select *
      from my_table
     where a = 1 and b = 2 -- and ...
)
-- carry on with your query, using my_filtered instead of my_table

edited Nov 03 '16 at 17:14

answered Nov 03 '16 at 14:21

Mark Adelsberger

42,148
4
35
52

MySql doesnt have `WITH` – Juan Carlos Oropeza Nov 03 '16 at 14:23
Its working the same with temporary tables under mysql – lukstru Nov 03 '16 at 14:36

Select distinct on multiple columns with where statement

1 Answers1