Hi

I have 2 tables:

[general_libcounty]:
- id_libCounty (PK)
- libCounty_name
- id_libCountry

[general_libtown]:
- id_libTown (PK)
- libTown_name
- id_libCounty (FK)

How to select all counties what don't have any towns in
general_libtown table

Relation:
general_libcounty.id_libCounty=general_libtown.id_ libCounty

Thanks for any

Sebastian

On Tue, 05 Feb 2008 11:11:21 +0100, Sebastian <haja.sebastian@gmail.com>
wrote:

> Hi
>
> I have 2 tables:
>
> [general_libcounty]:
> - id_libCounty (PK)
> - libCounty_name
> - id_libCountry
>
> [general_libtown]:
> - id_libTown (PK)
> - libTown_name
> - id_libCounty (FK)
>
> How to select all counties what don't have any towns in
> general_libtown table
>
> Relation:
> general_libcounty.id_libCounty=general_libtown.id_ libCounty

Assuming there's nu NULL allowed in general_libtown.id_libCounty:

SELECT c.id_libCounty
FROM general_libcounty c
LEFT JOIN general_libtown t
ON c.id_libCounty = t.id_libCounty
WHERE t.id_libCounty IS NULL

If there are NULLs in general_libtown.id_libCounty:

SELECT c.id_libCounty, COUNT(t.id_libTown) as countTowns
FROM general_libcounty c
LEFT JOIN general_libtown t
ON c.id_libCounty = t.id_libCounty
GROUP BY c.id_libCounty
HAVING countTowns = 0
--
Rik Wasmus

On 5 Lut, 10:21, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 05 Feb 2008 11:11:21 +0100, Sebastian <haja.sebast...@gmail.com>
> wrote:
>

> Assuming there's nu NULL allowed in general_libtown.id_libCounty:
>
> SELECT c.id_libCounty
> FROM general_libcounty c
> LEFT JOIN general_libtown t
> ON c.id_libCounty = t.id_libCounty
> WHERE t.id_libCounty IS NULL
>
> If there are NULLs in general_libtown.id_libCounty:
>
> SELECT c.id_libCounty, COUNT(t.id_libTown) as countTowns
> FROM general_libcounty c
> LEFT JOIN general_libtown t
> ON c.id_libCounty = t.id_libCounty
> GROUP BY c.id_libCounty
> HAVING countTowns = 0
> --
> Rik Wasmus

Thank you Rik

I made something very the same like you first example

SELECT
general_libcounty.*,
general_libtown.*
FROM
general_libcounty
LEFT OUTER JOIN
general_libtown
ON
general_libcounty.id_libCounty=general_libtown.id_ libCounty
WHERE
general_libtown.id_libTown IS NULL

Sebastian