[SQL] Lastige query en LEFT JOIN vervangen

[Raafz0r]

Ik zit voor het vak database ontwerp met een klein probleempje. We moeten een aantal opgaven maken, maar 2 opgaven lukken niet helemaal. De database is trouwens PostgreSQL.

Eerst even een overzichtje van de tabellen:


Nu is de opgave als volgt:
4. Welke script writers hebben geacteerd in al hun films?

Hiervoor hadden wij de volgende query bedacht:

SQL:

SELECT DISTINCT p.name 
FROM Persons p,Writers w, Actors a 
WHERE p.person_id = w.person_id 
AND w.movie_id = a.movie_id 
AND a.person_id = w.person_id;

maar hiermee selecteren we ook script writers die in een paar van hun films hebben geacteerd, en niet in alle. Hoe moet ik deze query aanpassen om het toch werkend te krijgen?

En dan nog een vraagje:

SQL:

SELECT p.name 
FROM 
(SELECT d.person_id,min(m.year)
FROM Directors d, Movies m
WHERE m.movie_id = d.movie_id
GROUP BY d.person_id
HAVING COUNT(*)>1 
ORDER BY COUNT(*) desc) 
AS e 
LEFT JOIN persons p ON p.person_id=e.person_id;

Hoe kan ik die LEFT JOIN in bovenstaande query vervangen, want de begeleiders vinden dat we geen LEFT JOIN moeten gebruiken omdat dat niet in de stof werd uitgelegd.

Hopelijk kan iemand met hiermee helpen.

[Jaspertje]

Welke stof heb je wel gehad? Inner join mag wel?

[justmental]

Zo'n opdracht heb ik al eens gezien:
[rml][ SQL] moeilijke opdracht[/rml]

[Raafz0r]

Jaspertje schreef op 10 november 2003 @ 13:28:
Welke stof heb je wel gehad? Inner join mag wel?

Nee, alleen met SELECTs enzo, geen join. Op zich best raar, want dingen als EXCEPT of MINUS hadden we ook niet gehad, maar konden we toch gebruiken.

[EfBe]

Met subqueries en IN:

WHERE Bla IN
(
SELECT BLA from foo where...
)
AND
Bla In
(
SELECT Bla FROM Bar where...
)

(niet efficient, 2 keer query, maar goed, ze willen geen joins )

[Raafz0r]

Ok, ik heb dat topic doorgelezen en doe een nieuwe poging:
Hun oplossing voor het Oliver Stone probleem:

code:

select a.voornaam
    ,a.achternaam
from   acteur a
where  anr in
    (select r.anr
     from   rol r
         ,film f
         ,regiseur g
     where  r.fnr = f.fnr
     and    f.rnr = g.rnr
     and    g.vnaam='Oliver' AND g.anaam='Stone'
    )
and    not exists
    (select 1
     from   film f
         ,regiseur g
     where  f.rnr = g.rnr
     and    g.vnaam='Oliver' AND g.anaam='Stone'
     and    not exists
         (select 1
          from   rol r
          where  f.anr = a.anr
    )   )

Mijn poging om mijn probleem aan te pakken:

SQL:

select p.name
from   Persons p
where  person_id in
    (select a.person_id
     from   Actors a, Writers w
     where  a.person_id = w.person_id
     and    a.movie_id = w.movie_id
    )
and    not exists
    (select 1
     from   Actors a, Writers w
     where  a.person_id = w.person_id
     and    a.movie_id = w.movie_id
     and    not exists
         (select 1
          from   rol r
          where  w.person_id = a.person_id
    )    )

Zou dit werken?

Alleen snap ik dat select 1 nog niet helemaal. Waarom doe je dat? Hoe werkt sowieso die dubbele not exists eigenlijk, want dat snap ik nog niet helemaal.

[justmental]

Lees nog eens goed in dat topic, in een van m'n eerste replies zeg ik dat je de conditie moet omdraaien, daar komen ook de not exists vandaan.

[Raafz0r]

Ok, dus mijn werkelijke vraag is:
Noem alle writers waarbij geen film bestaat die zij zelf hebben geschreven maar waarin zij niet als acteur voorkomen.

Nog maar een poging om het te begrijpen dan:

code:

select p.name
from Persons p, Writers w
where p.person_id = w.person_id
and not exists
  (select w.person_id
  from Writers
  where w.movie_id IN
    (select w.movie_id
     from Writers
    
     minus
 
     select w.movie_id
     from Writers, Actors a
     where w.movie_id = a.movie_id
     and w.person_id = a.person_id
     )
  )

Nu selecteer ik dus binnenin alle movies, behalve die waarin de writer ook acteur is. Dan selecteer ik alle writers die één van die films hebben gemaakt.
Daarna selecteer ik de namen van alle personen die writer zijn maar niet in het net gemaakte rijtje writers staan.

Volgens mij heb ik hem nu correct.

Trouwens, we mogen wel Outer Joins gebruiken zoals je ziet.

[EfBe]

outer joins zijn left of right join. ik zie in jouw query een non-ansi inner join staan geen outer join (je specificeert geen (*) of (+) )

[Raafz0r]

EfBe schreef op 10 november 2003 @ 16:22:
outer joins zijn left of right join. ik zie in jouw query een non-ansi inner join staan geen outer join (je specificeert geen (*) of (+) )

Welke bedoel je?

Is overigens die laatste query van mij goed om mijn vraag op te lossen? (kan het nu niet testen, dus hopelijk kan iemand dat zien zonder hem uit te voeren)

[EfBe]

Raafz0r schreef op 11 november 2003 @ 15:57:
Welke bedoel je?

Dit is een inner join, altijd:

SELECT * FROM A, B
WHERE A.Foo = B.Foo

Dit is een outer join (left), non ansi, SqlServer
SELECT * FROM A, B
WHERE A.Foo = B.Foo(*)

Oracle:
SELECT * FROM A, B
WHERE A.Foo = B.Foo(+)

Omdat jij geen (+) of (*) opgeeft, is het geen outer left/right join.

[Raafz0r]

Ok, dan mogen we blijkbaar alleen geen LEFT JOIN gebruiken, maar de rest wel.

Kan iemand me zeggen of deze laatste query nu eindelijk doet wat ik wil?

[whoami]

Raafz0r schreef op 14 november 2003 @ 13:50:
Kan iemand me zeggen of deze laatste query nu eindelijk doet wat ik wil?

Probeer het gewoon ff uit?