CODEWITHSUNDEEP
mysqldistinct
Papa Razzo
Papa Razzo

Reputation: 41

MYSQL Advanced select distinct values with corresponding ID which are not in anothe table

EDITED:

I have a huge problem with SELECT DISTINCT values from one of my table.

Table 1 :T1

pid      thing       sub-thing     tnumber     
-------------------------------------------
1       A1212       A01A00001     123456      
2       A1212       A01A00002     123457     
3       A1212       A01A00002     123458      
4       A1214       A01B00001     123459     
5       A1214       A01B00002     123460        
6       A1214       A01B00001     123461         
7       A1217       C01A00001     123462

Table 2 :T2

id        pid      thing       sub-thing         tnumber    h1 
-------------------------------------------------------------- 
1          3            A1212       A01A00002     123458   False

I need to SELECT all DISTINCT sub-things for every thing from Table T1 which are NOT in Table T2 and, most important - only sub-thing with the highest tnumber (for ex. with max(id)).

Result Should leave records 2, 4 from T1 because there are identical sub-things with higher tnumber and of course leave record 3 from T1 because it's in the T2. Final result should be like this

id      thing       sub-thing     tnumber     
-------------------------------------------
1       A1212       A01A00001     123456                 
5       A1214       A01B00002     123460        
6       A1214       A01B00001     123461         
7       A1217       C01A00001     123462

Thanks to everyone here, the syntax I've tried is:

SELECT DISTINCT t1.pid, t1.thing, t1.subthing, t1.tnumber 
FROM t1 INNER JOIN
(SELECT t1.thing, t1.subthing, max(t1.tnumber) as tnumber FROM t1 LEFT OUTER JOIN t2 o ON t1.pid=o.pid WHERE o.pid IS NULL
AND t1.added>'2015-10-31' 
GROUP BY t1.subthing, thing) 
as b using (subthing, thing, tnumber) ;

Now I need to add a condition that the records from T1 where T1.pid is NOT in T2.pid AND t2.h1=false should NOT be given in final result.

Upvotes: 3

Views: 110

Answers (5)

Mateo Barahona
Mateo Barahona

Reputation: 1391

Try something like that ?

SELECT *
FROM T1
WHERE CONCAT_WS('-', thing, `sub-thing`, tnumber) IN
(SELECT CONCAT_WS('-', thing, `sub-thing`, max_tnumber)
FROM (
    SELECT MAX(tnumber) AS max_tnumber, thing, `sub-thing`
    FROM T1
    WHERE T1.`sub-thing` NOT IN (SELECT `sub-thing` FROM T2)
    GROUP BY thing, `sub-thing`
) as view
);

Upvotes: 0

Papa Razzo
Papa Razzo

Reputation: 41

Some modification I've made based on @LFJ syntax.

'changed `t1.id` column to `t1.pid`'
'changed `t2.id_table_t1` column to `t2.pid`'
'and JOINed tables via `pid` columns'
SELECT DISTINCT t1.pid, t1.thing, t1.subthing, t1.tnumber 
FROM t1 INNER JOIN
(SELECT t1.thing, t1.subthing, max(t1.tnumber) as tnumber FROM t1 LEFT OUTER JOIN t2 o ON t1.pid=o.pid WHERE o.pid IS NULL
AND t1.added>'2015-10-31' 
GROUP BY t1.subthing, thing) 
as b using (subthing, thing, tnumber) ;

Result is the same but comparision is via pid column (old ID) , not tnumber column, which means even if in the T2.tnumber is whatever value the result will be the same :)

I think this is the end. Maybe it'll help somebody in future. Thanks a lot.

Upvotes: 0

Fujiao Liu
Fujiao Liu

Reputation: 2253

try left outer join:

    select distinct t1.id, t1.thing, t1.subthing, t1.tnumber from t1 inner join (SELECT t1.thing, subthing, max(t1.tnumber) as tnumber 
FROM t1 left outer join t2 using (subthing) where t2.subthing is NULL GROUP BY subthing, thing) as b using (subthing, thing, tnumber) ;

create a concatenated Index:

create index index_name t1(subthing, thing, tnumber);

Upvotes: 1

Papa Razzo
Papa Razzo

Reputation: 41

I Found One issue in following LFJ syntax:

SELECT t1.id, t1.thing, subthing, max(t1.tnumber) as tnumber
FROM t1 left outer join t2 using (subthing) where t2.subthing is NULL GROUP BY subthing, thing;

will result 

4       A1214       A01B00001     123459

in stead of

6       A1214       A01B00001     123461

id=4 is the first ID for that sub-thing but number 123461 is from the max tnumber but max tnumber is in record id=6.

I think syntax should start with:

SELECT max(t1.id), t1.thing (...)

Upvotes: 0

fthiella
fthiella

Reputation: 49059

This query will return the MAX(id) for every subthing:

SELECT subthing, MAX(id) AS max_id
FROM t1
WHERE subthing NOT IN (SELECT subthing FROM t2) 
GROUP BY subthing;

(i think that you want to exclude all subthings that are present in t2)

Then with this query you can retrieve all the other columns for the maximum id:

SELECT t1.*
FROM t1
WHERE ID IN (
    SELECT MAX(id) AS max_id
    FROM t1
    WHERE subthing NOT IN (SELECT subthing FROM t2) 
    GROUP BY subthing
  )

Please see a fiddle here. If yow want better performances, you could try with a LEFT JOIN:

SELECT ta.*
FROM
  t1 AS ta LEFT JOIN t1 AS tb
  ON ta.subthing=tb.subthing
     AND ta.id<tb.id
WHERE
  ta.subthing NOT IN (SELECT subthing FROM t2)
  AND tb.id IS NULL

(please make sure that t1.id is indexed, and also an index on subthing on both tables should help).

Upvotes: 2

Related Questions