0

I have 2 tables, app and app_log.

In app table :

id   app_id    app_name
1     1234      aaa
2     2334      bbb
3     9374      ccc

In table app_log :

id   app_id    app_name    app_status_id
1     1234      aaa            NULL
1     1234      aaa            1
1     1234      aaa            2
1     1234      aaa            4
1     1234      aaa            5
2     2334      bbb            1
2     2334      bbb            2
2     2334      bbb            3
2     2334      bbb            4
2     2334      bbb            5
3     9374      ccc            1
3     9374      ccc            2
3     9374      ccc            4
3     9374      ccc            5

How do I find an app id and app name where the app_status_id does not have any status id of 3?

I tried below query but it doesn't show any result although there are app_id that don't have any app_status_id of 3.


    SELECT DISTINCT
    app.app_id,
    app.app_name
    FROM
    app,
    app_log
    WHERE NOT EXISTS (SELECT * FROM app_log WHERE app_status_id=2)

Please kindly help me.

Expected result / result that I want:

app_id    app_name
1234        aaa
9374        ccc
Aya chan
  • 27
  • 3
  • Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (**without errors**), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Nov 17 '22 at 08:11
  • Sample data is great, you should also _specify_ the expected result. – jarlh Nov 17 '22 at 08:12
  • Remove `app_log` from FROM clause. Also remove DISTINCT, it is excess. – Akina Nov 17 '22 at 08:24
  • See the following answer to the duplicate question: https://stackoverflow.com/a/11767601/5389997 – Shadow Nov 17 '22 at 08:35
  • Your question is still unclear. Neither your sample data nor your description tell us if apps should be selected that don't exist at all in the table app_log. – Jonas Metzler Nov 17 '22 at 08:36

0 Answers0