MySql - Ignore Data from Selecting Query where have specific element

Question

I have query like this:

mysql> SELECT
    -> ir.id, ir.no_surat, link.condition_id
    -> FROM inspection_report ir
    ->
    -> LEFT JOIN link_item_condition_ir_to_ir link
    -> ON link.inspection_id = ir.id
    ->
    -> WHERE ir.no_surat LIKE '%E67%'
    -> ORDER BY ir.no_surat ASC, link.condition_id DESC;

+------+---------------+--------------+
| id   | no_surat      | condition_id |
+------+---------------+--------------+
| 7561 | E6779/10/2018 |            1 |
| 7562 | E6780/10/2018 |            5 |
| 7562 | E6780/10/2018 |            1 |
| 7563 | E6781/10/2018 |            5 |
| 7563 | E6781/10/2018 |            1 |
+------+---------------+--------------+
5 rows in set (0.03 sec)

Please see in no_surat column.

There is 3 unique no_surat. My Goal is: just select the data that not have condition_id = 5.

I also already try this,

mysql> SELECT
-> ir.id, ir.no_surat, link.condition_id
-> FROM inspection_report ir
->
-> LEFT JOIN link_item_condition_ir_to_ir link
-> ON link.inspection_id = ir.id
->
-> WHERE ir.no_surat LIKE '%E67%'
-> AND
-> condition_id != 5
->
-> ORDER BY ir.no_surat ASC, link.condition_id DESC;
+------+---------------+--------------+
| id   | no_surat      | condition_id |
+------+---------------+--------------+
| 7561 | E6779/10/2018 |            1 |
| 7562 | E6780/10/2018 |            1 |
| 7563 | E6781/10/2018 |            1 |
+------+---------------+--------------+
3 rows in set (0.02 sec)

But still not work, Please advise.

The result that I expected is, Ignoere all 'no_surat' columnt that have condition_id = 5

+------+---------------+--------------+
| id   | no_surat      | condition_id |
+------+---------------+--------------+
| 7561 | E6779/10/2018 |            1 |
+------+---------------+--------------+

Thanks

How about this query:

mysql> SELECT
    -> ir.id, ir.no_surat, link.condition_id
    -> FROM inspection_report ir
    ->
    -> LEFT JOIN link_item_condition_ir_to_ir link
    -> ON link.inspection_id = ir.id
    ->
    -> WHERE ir.no_surat LIKE '%E67%'
    -> GROUP BY ir.id
    ->
    -> HAVING GROUP_CONCAT( link.condition_id ) NOT LIKE "%5%";
+------+---------------+--------------+
| id   | no_surat      | condition_id |
+------+---------------+--------------+
| 7561 | E6779/10/2018 |            1 |
+------+---------------+--------------+
1 row in set (0.00 sec)

It looks good ?

Here is the fiddle : sqlfiddle

sql is working correctly as it should be. explain more what output you want. — Ahmed Sunny
– Ahmed Sunny, Commented Oct 16, 2018 at 11:57
either you give this in where 'E6779' otherwise even you use distinct , it will give you 3 results as it is giving without distinct — Ahmed Sunny
– Ahmed Sunny, Commented Oct 16, 2018 at 12:06

Michał Turczyn · Accepted Answer · 2018-10-16 12:07:36Z

2

Use this condition:

NOT EXISTS(SELECT 1 FROM link_item_condition_ir_to_ir
           WHERE inspection_id  = ir.id AND condition_id = 5)

instead of condition_id != 5.

answered Oct 16, 2018 at 12:07

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-10-16 16:59:51Z

1

use NOT EXISTS

SELECT t.* from     
    (select ir.id, ir.no_surat, link.condition_id
     FROM inspection_report ir   
    LEFT JOIN link_item_condition_ir_to_ir link
    ON link.inspection_id = ir.id
     ) t where 
             NOT EXISTS ( select 1 from link_item_condition_ir_to_ir
                           t1 where t1.inspection_id=t.id and t1.condition_id=5
                         )

http://www.sqlfiddle.com/#!9/647c0e/3

id      no_surat            condition_id
7561    E6779/10/2018         1

edited Oct 16, 2018 at 16:59

answered Oct 16, 2018 at 12:35

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

3 Comments

Fadly Dzil Over a year ago

1 is not a key, sometimes combine with 1,2,3,4,5 . Here is the fiddle: sqlfiddle.com/#!9/647c0e/2/0

Zaynul Abadin Tuhin Over a year ago

@FadlyDzil what is your expected output

Zaynul Abadin Tuhin Over a year ago

@FadlyDzil sqlfiddle.com/#!9/647c0e/3 does not it your expected output?

Raman Kumar · Accepted Answer · 2018-10-17 06:49:40Z

1

Try below Query, I'm using your query for getting the result.

 select id,no_surat,condition_id from 
(SELECT ir.id, ir.no_surat,
 concat('E67',SUBSTR(ir.no_surat,6),condition_id) res, 
link.condition_id FROM inspection_report ir
LEFT JOIN link_item_condition_ir_to_ir link
ON link.inspection_id = ir.id
WHERE ir.no_surat LIKE '%E67%'
ORDER BY ir.no_surat ASC, link.condition_id DESC) tmp 
where condition_id != 5  group by res

If You want to better query then please share your database structure (Schema ) :)

edited Oct 17, 2018 at 6:49

answered Oct 16, 2018 at 12:17

Raman Kumar

1298 bronze badges

1 Comment

Raman Kumar Over a year ago

Thanks for giving me platform to checking the query :)

Abdul khalik · Accepted Answer · 2018-10-19 09:58:35Z

1

SELECT ir.id, ir.no_surat, link.condition_id 
FROM inspection_report ir 
LEFT JOIN link_item_condition_ir_to_ir link
ON link.inspection_id = ir.id
WHERE ir.no_surat LIKE '%E67%' AND
ir.no_surat NOT IN 
 (SELECT ir.no_surat 
  FROM inspection_report ir 
  LEFT JOIN link_item_condition_ir_to_ir link
  ON link.inspection_id = ir.id 
  WHERE condition_id = 5)
ORDER BY ir.no_surat ASC, link.condition_id DESC;

edited Oct 19, 2018 at 9:58

answered Oct 16, 2018 at 12:12

Abdul khalik

642 bronze badges

Collectives™ on Stack Overflow

MySql - Ignore Data from Selecting Query where have specific element

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related