a complex sql query with outer join

Question

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

Looks a lot like a homework question to me. What have tried? — Tigger, Feb 26 '19 at 09:06
too easy and dublicate, answers like this exists on stackoverflow. — Sayed Mohd Ali, Feb 26 '19 at 09:15
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Tigger, Feb 26 '19 at 09:18

score 3 · Accepted Answer · edited Feb 26 '19 at 14:27

3

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

edited Feb 26 '19 at 14:27

Sayed Mohd Ali

2,156
3
12
28

answered Feb 26 '19 at 09:05

Fahmi

37,315
5
22
31

Can you do the same without using sub-query? – M K Garwa Feb 26 '19 at 09:21
@MKGarwa, I've added it without subquery - you can check – Fahmi Feb 26 '19 at 09:28
@MKGarwa '%S%' should be 'S%' – Sayed Mohd Ali Feb 26 '19 at 10:17

Sayed Mohd Ali · Answer 2 · 2019-02-26T12:06:34.903

0

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.

edited Feb 26 '19 at 12:06

answered Feb 26 '19 at 09:10

Sayed Mohd Ali

2,156
3
12
28

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/22310459) – Sagar Chauhan Feb 26 '19 at 09:47
@SagarChauhan the query is working now, I have missed the output part he wants... – Sayed Mohd Ali Feb 26 '19 at 10:29
Please add more description regarding how this code works. – Sagar Chauhan Feb 26 '19 at 11:27
@SagarChauhan Okay, I have given good description of OPs question and answer which he do not mentioned and just gave output... – Sayed Mohd Ali Feb 26 '19 at 12:07

Zaynul Abadin Tuhin · Answer 3 · 2019-02-26T09:28:59.640

-1

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

edited Feb 26 '19 at 09:28

answered Feb 26 '19 at 09:05

Zaynul Abadin Tuhin

31,407
5
33
63

Question specifically says 'using outer join' are you suggesting an outer join is not required? – P.Salmon Feb 26 '19 at 09:08
@P.Salmon Left join is left outer join. – nice_dev Feb 26 '19 at 09:14

a complex sql query with outer join

3 Answers3