SQL filter out ids that have multiple values

31
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

16
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

31
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

23
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

19