CODEWITHSUNDEEP
phpmysqlsql
M K Garwa
M K Garwa

Reputation: 495

a complex sql query with outer join

there are two tables a

------------------------------
id  |  Name
------------------------------
1   |  Alpha
-----------------------------
2   |  Beta
-----------------------------
3   |  Gamma
-----------------------------
4   |  Delta
-----------------------------

and another table b with foreign key of table a

b

-----------------------------
id  | a_id | Film
-----------------------------
 1  |   1  | Bladerunner
-----------------------------
 2  |   1  | Star Wars
-----------------------------
 3  |   3  | Superman
-----------------------------
 4  |   4  | Rollerball
-----------------------------

Write an SQL query using outer join to get all names from table “a” that don't have a film starting with “S”.

Query result should be:

Beta
--------
Delta

Upvotes: 0

Views: 90

Answers (3)

Fahmi
Fahmi

Reputation: 37473

Use correlated subquery with not exists

DEMO

   SELECT * 
FROM   tablea a 
       LEFT JOIN tableb b 
              ON a.id = b.a_id 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   tableb b1 
                   WHERE  b.a_id = b1.a_id 
                          AND film LIKE 'S%')

OR you can use below query to avoid subquery

    SELECT NAME, 
       Sum(CASE 
             WHEN film LIKE 'S%' THEN 1 
             ELSE 0 
           END) 
FROM   t1 a 
       LEFT JOIN t2 b 
              ON a.id = b.a_id 
GROUP  BY NAME 
HAVING Sum(CASE 
             WHEN film LIKE 'S%' THEN 1 
             ELSE 0 
           END) = 0

OUTPUT:

name
Delta
Beta

Upvotes: 3

Sayed Mohd Ali
Sayed Mohd Ali

Reputation: 2254

SELECT a.NAME 
FROM   a 
       LEFT JOIN b 
              ON a.id = b.a_id 
WHERE  a.NAME NOT IN (SELECT a.NAME 
                      FROM   a 
                             LEFT JOIN b 
                                    ON a.id = b.a_id 
                      WHERE  b.film LIKE 's%') 
GROUP  BY a.NAME

output

Beta

Delta

according to the OPs question which he has not mentioned and just gave output part in his question... he wants all the name of the table a where film name do not start with letter S from table b. the above query fullfilled the requirements of OPs completely and in most standard way.

OP just mentioned the join in the question but he do not mentioned that he want those id name also which do not exist in table b in a_id i.e the output part beta.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use substring and join

  select a.name from t1 a left join 
 t2 b on a.id=b.a_id
 where a.id  not in ( select b.a_id  from t2 b
  where 
upper(substring(b.Film,1,1))   like '%S%' and b.a_id is not null
    )

output

name
Delta
Beta

demo link

Upvotes: -1

Related Questions