Select z databazi s relaci 1:N

[Honza Pazdziora]

On Fri, Dec 07, 2001 at 03:19:20PM +0100, Zdeněk Böhm wrote:
> 
> Databaze1 ma sloupec Id typu int, Databaze 2 ma sloupec Databaze1_Id typ int
> a sloupec XXX typ int. Ke kazdemu Id v Databazi1 muze existovat 1-5 zaznamu
> v Databazi2 (lise se obsahem sloupce XXX).
> Potrebuju poradit, jak napsat SELECT, ktery vybere zaznamy z Databaze1, ke
> kterym neexistuje v Databazi2 zaznam s nejakym konkretnim cislem (treba 3) v
> sloupci XXX.
> 
> Priklad:
> 
> Databaze1:                    Databaze2:
> 
> Id                                  Databaze1_Id    XXX
> -------------------------------------------------------------------
> 1                                          1                  1
>                                             1                  2
>                                             1                  3
> 2                                          1                  1
>                                             1                  2
> 
> SELECT 1.Id, 2.XXX FROM Databaze1 AS 1, Databaze2 AS 2 WHERE 1.Id = 2.Id AND
> XXX = 3 vrati:
> 
> Id     XXX
> -------------
> 1       3
> 
> SELECT 1.Id,2.XXX FROM Databaze1 AS 1, Databaze2 AS 2 WHERE 1.Id = 2.Id AND
> XXX <> 3 vrati:
> 
> Id     XXX
> -------------
> 1      1
> 1      2
> 2      1
> 2      2
> 
> Jak napsat dotaz, ktery vrati (v pripade XXX=3)
> 
> Id     XXX
> -------------
> 2       1
> 2       2
> 
> Jde to?

Jiste, napriklad

select database1.id, databaze2.xxx
from database1, databaze2
where database1.id = databaze2.databaze_id
and not exists (
	select 1 from databaze2 databaze22
	where database1.id = databaze22.databaze_id
	and databaze22.xxx = 3
	)

Netestovano. Pokud mate server, ktery by neumel not exists, da se
pouzit i not in, pripadne outer join.

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton na fi.muni.cz | http://www.fi.muni.cz/~adelton/
   .project: Perl, DBI, Oracle, MySQL, auth. WWW servers, DBD::XBase.
------------------------------------------------------------------------