CODEWITHSUNDEEP
sqlpostgresql
B Seven
B Seven

Reputation: 45943

How to write a SQL query to get the records if there is a match or if there are no matches in the second table?

apps:
id     name
--     --------
1      Facebook
2      Twitter
3      Pokemon


markets:
id     app_id    code
--     ------    ----
1      1         US
2      1         CA
3      3         CA

The following query will get the apps for the US:

SELECT *

  FROM apps

  INNER JOIN markets
    ON markets.app_id = apps.id
    AND markets.code = 'US'

But how to get the above records, plus any apps that have no markets?

The query would return apps 1 and 2.

PostgreSQL 9.3.5, 9.3.9 and 9.4.7

Upvotes: 0

Views: 35

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

As a note, you don't seem to want information about apps, so you could do:

select a.*
from apps a
where not exists (select 1 from markets m where m.app_id = a.id) or
      exists (select 1 from markets m where m.app_id = a.id and m.code = 'US');

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Here's one option with an outer join:

select *
from apps
    left join markets
        on markets.app_id = apps.id
where markets.app_id is null or 
    markets.code = 'US'

Upvotes: 2

Related Questions