CODEWITHSUNDEEP
sql
Raidmaster
Raidmaster

Reputation: 613

SQL Query on 3 tables

I've got three tables:

Galaxies, solar Systems and Planets.

All of them are connected by Id's in an obvious way (Galaxies may have xx Solar Systems, SS can have xx planets). Let's say they have such structure:

    Galaxy: id, name
    Solar System: id, id_galaxy, name
    Planet: id, id_solar_system, name

I'm really struggling with creating a query to do the following:

Select names of galaxies that do not have planets at all. (They can still have empty Solar systems).

Please ignore the fact that something may seem unlikely science-wise ;)

I figured it must be something with left outer join, I can easily select Solar Systems that do not have any planets, but I seem to struggle to "propagate' it to Galaxies. I think I need some more complex query here, maybe a subquery).

Please help, I'd really appreciate it!

Upvotes: 2

Views: 98

Answers (2)

user533832
user533832

Reputation:

I find using not exists the most readable for this sort of problem:

select name
from galaxy g
where not exists( select * 
                  from solarsystem s join planet p on s.id=p.id_solar_system
                  where id_galaxy=g.id )

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You need a having clause:

SELECT G.id, G.name
FROM Galaxy G LEFT OUTER JOIN
     SolarSystem SS
     ON G.id = SS.id_galaxy LEFT OUTER JOIN
     Planet P
     ON SS.id = P.id_solar_system
group by g_id, g.name
having max(P.id) IS NULL

The left outer joins bring together all planets and solar systems. The having clause chooses the ones that have no planets at all in the galazy.

Upvotes: 3

Related Questions