Retrieving data from a database with multiple conditions

Question

I have a table below:

ID Type Date Status1 Status2 Status3 1 458 2018-01-01 15:04:06 AAA AB 2 471 2017-12-31 15:04:06 AAA AB 3 458 2018-01-15 15:04:06 BBC CD 5 458 2018-01-18 15:04:06 AAA XD 

There is a lot of the same value in the Type column, I want to get data with the following condition:

• If any value in Type (must have more than once in the table) has Status1 as AAA, and other lines with the same value in Type larger than the previous Date , with Status2 as C and Status3 as D together, or Status3 should be D.

Output:

 ID Type Date Status1 Status2 Status3 1 458 2018-01-01 15:04:06 AAA AB 3 458 2018-01-15 15:04:06 BBC CD 5 458 2018-01-18 15:04:06 AAA XD 

I use the below query:

 SELECT a.ID,a.Type,a.Date,b.Status1,a.Status2,a.Status3 From Table1 a inner join Table2 b on a.abc=b.xyz inner join Table2 c on a.efg=c.xyz GROUP BY a.Type HAVING Count(a.Type)>0 AND b.Status1='AAA' UNION SELECT a.ID,a.Type,a.Date,b.Status1,a.Status2,a.Status3 FROM Table1 a inner join Table2 b on a.abc=b.xyz inner join Table2 c on a.efg=c.xyz GROUP BY a.Type HAVING Count(a.Type)>0 AND a.Date > (SELECT Date From Table1 GROUP BY Type HAVING b.Status1='AAA' AND Count(Type)>0) AND ( Status2='A' AND Status3='D' ) OR Status3='D'; 

Answer 1

You can try something like below

 SELECT t.* FROM `t` INNER JOIN ( SELECT `type` , COUNT(`type`) ct FROM t GROUP BY `type` HAVING ct > 1 ) t1 ON t1.type = t.type ORDER BY DATE ASC 

I hope this returns what you want.

Answer 2

Try this request. Hope this gives you a way out of desire.

 SELECT ID , Type , Date , Status1 , Status2 , Status3 FROM [Tabel_Name] GROUP BY Type HAVING Count(Type)>0 AND Status1='AAA' UNION SELECT ID , Type , Date , Status1 , Status2 , Status3 FROM [Tabel_Name] GROUP BY Type HAVING Count(Type)>0 AND Date > (SELECT Date From [Tabel_Name] GROUP BY Type HAVING Status1='AAA' AND Count(Type)>0) AND ( Status2='C' AND Status3='D' ) OR Status3='D'