SQL filter out ids that have multiple values

21
April 16, 2019, at 02:40 AM

I have MySQL table in the following format

id     c1     c2
---------------------
id1    c1v1   c2v1  -> keep id1 even it has duplicates
id1    c1v1   c2v1
id2    c1v2   c2v2  -> filter out id2 because it has various c1, c2
id2    c1v2   c2v3
id3    c1v3   c2v4
....

Expected output:

id     c1     c2
---------------------
id1    c1v1   c2v1
id3    c1v3   c2v4
....

I want to retain only the records where id only has a unique c1, c2 value. I do have a solution but it requires full table scan twice including a join which is very inefficient and I am wondering if there is a better way of doing it.

select 
    distinct id, c1, c2
from table 
inner join 
    (select 
        id, 
        count(distinct c1, c2) as counts
    from table 
    group by id
    having counts = 1) tmp
on table.id = tmp.id
Answer 1

How about just using group by:

select id, min(c1) as c1, min(c2) as c2
from t
group by id
having min(c1) = max(c1) and min(c2) = max(c2)

If you want all the original records, then use not exists

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.id = t.id and
                        (t2.c1 <> t.c1 or t2.c2 <> t.c2)
                 );
Answer 2

You can do this with a self LEFT JOIN on different c1/c2 values, keeping only those rows where there is no matching row (i.e. different values for the same id) in the second table:

SELECT DISTINCT t1.id, t1.c1, t1.c2
FROM test t1
LEFT JOIN test t2 ON t2.id = t1.id AND (t2.c1 != t1.c1 OR t2.c2 != t1.c2)
WHERE t2.id IS NULL

Output:

id      c1      c2
id1     c1v1    c2v1
id3     c1v3    c2v4

Demo on dbfiddle

READ ALSO
How I skip one cell in MySQL query

How I skip one cell in MySQL query

I'm trying to make PHP code update column called "downloads" to zero inside MySQL

9
need help for query code export from 2table (by php) in mysql

need help for query code export from 2table (by php) in mysql

i need code for export favs in tablei make 2 table in mysql one : tbl_post two : tbl_markshow

24
Implementing Auto-Bidding Functionality with JSP/MySQL

Implementing Auto-Bidding Functionality with JSP/MySQL

Currently working on a project and could use some advice for how to approach implementing an Auto-Bidding system for a web application similar to Ebay

17
Calculate duration for bookings in a period between two time slots

Calculate duration for bookings in a period between two time slots

In a booking system I want to calculate the sum of hours a resource has been booked between two dates (2019-01-01 to 2019-01-02) from 8am to 4pm

13