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
– Tigger, Commented Feb 26, 2019 at 9:06
too easy and dublicate, answers like this exists on stackoverflow. — Sayed Mohd Ali
– Sayed Mohd Ali, Commented Feb 26, 2019 at 9:15
Possible duplicate of What is the difference between "INNER JOIN" and "OUTER JOIN"? — Tigger
– Tigger, Commented Feb 26, 2019 at 9:18

Sayed Mohd Ali · Accepted Answer · 2019-02-26 14:27:02Z

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, 2019 at 14:27

Sayed Mohd Ali

2,2523 gold badges14 silver badges31 bronze badges

answered Feb 26, 2019 at 9:05

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

3 Comments

M K Garwa Over a year ago

Can you do the same without using sub-query?

Fahmi Over a year ago

@MKGarwa, I've added it without subquery - you can check

Sayed Mohd Ali Over a year ago

@MKGarwa '%S%' should be 'S%'

Sayed Mohd Ali · Accepted Answer · 2019-02-26 12:06:34Z

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, 2019 at 12:06

answered Feb 26, 2019 at 9:10

Sayed Mohd Ali

2,2523 gold badges14 silver badges31 bronze badges

4 Comments

Sagar Chauhan Over a year ago

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

Sayed Mohd Ali Over a year ago

@SagarChauhan the query is working now, I have missed the output part he wants...

Sagar Chauhan Over a year ago

Please add more description regarding how this code works.

Sayed Mohd Ali Over a year ago

@SagarChauhan Okay, I have given good description of OPs question and answer which he do not mentioned and just gave output...

Zaynul Abadin Tuhin · Accepted Answer · 2019-02-26 09:28:59Z

-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, 2019 at 9:28

answered Feb 26, 2019 at 9:05

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

2 Comments

P.Salmon Over a year ago

Question specifically says 'using outer join' are you suggesting an outer join is not required?

mighty_dev Over a year ago

@P.Salmon Left join is left outer join.

Collectives™ on Stack Overflow

a complex sql query with outer join

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related