[Mysql] Select con Join su 3 tabelle

[Jack991]

buongiorno a tutti.

ho un db con 3 tabelle: Animali, Luoghi e una taballa di associazione (animali_luoghi).

La relazione tra animali e luoghi e di tipo N:M (molti a molti) e la tabella di associazione che si chiama animali_luoghi contiene le associazioni tra animali e luoghi.

Ogni animale può vivere in più luoghi così come in ogni luogo possono vivere più animali.

Ho bisogno di estrarre dal database, con una sola select: tutti gli animali che abitano in uno specifico luogo E tutti gli altri animali escludendo quelli che abitano nello specifico luogo.

Cioè deve risultare una lista di tutti gli animali e se uno di questi animali vive nello specifco luogo il campo luogo dovrà contenere il nome del luogo, altrimenti NULL.

schema tabelle

animali(id, nome)
luoghi(id, nome)
animali_luoghi(id,id_animale, id_luogo)

esempio contenuto

_ANIMALI
_id         _nome
1           gatto
2           cane
3           mucca

_LUOGHI
_id         _nome
1           casa
2           fattoria

_ANIMALI_LUOGHI
_id       _id animale     _id_luogo
1         1                  1
2         2                  1
3         2                  2
4         3                  2

Risultato voluto con luogo = 'fattoria'

_animale     _luogo
gatto         NULL
cane          fattoria
mucca        fattoria

esempio di query possibile:

(
 SELECT animali.nome, luogo.nome 
 FROM animali 
 LEFT JOIN animali_luoghi ON animali.id = animali_luoghi.id_animali 
 LEFT JOIN luoghi ON animali_luoghi.id = luoghi.id 
 WHERE luogo.nome = 'fattoria'
) 
UNION 
(
 (
  SELECT distinct(animali.nome), luogo.nome 
  FROM animali 
  LEFT JOIN animali_luoghi ON animali.id = animali_luoghi.id_animali 
  LEFT JOIN luoghi ON animali_luoghi.id = luoghi.id 
 )
 MINUS
 (
  SELECT animali.nome, luogo.nome 
  FROM animali 
  LEFT JOIN animali_luoghi ON animali.id = animali_luoghi.id_animali 
  LEFT JOIN luoghi ON animali_luoghi.id = luoghi.id 
  WHERE luogo.nome = 'fattoria'
 )
)

Fondamentalmente la query sopra prende 'tutti gli animali che abitano in fattoria' UNION ('distinct tutti gli animali' MINUS 'gli animali che abitano in fattoria').

Il problema in questo caso è che MYSQL non gestisce MINUS, me la dovrei cavare con le join!!! Come posso fare?

Grazie di cuore a chi mi darà una manoa vincere questa sfida

[Joe Taras]

Originariamente inviato da Jack991
buongiorno a tutti.

ho un db con 3 tabelle: Animali, Luoghi e una taballa di associazione (animali_luoghi).

La relazione tra animali e luoghi e di tipo N:M (molti a molti) e la tabella di associazione che si chiama animali_luoghi contiene le associazioni tra animali e luoghi.

Ogni animale può vivere in più luoghi così come in ogni luogo possono vivere più animali.

Ho bisogno di estrarre dal database, con una sola select: tutti gli animali che abitano in uno specifico luogo E tutti gli altri animali escludendo quelli che abitano nello specifico luogo.

Cioè deve risultare una lista di tutti gli animali e se uno di questi animali vive nello specifco luogo il campo luogo dovrà contenere il nome del luogo, altrimenti NULL.

schema tabelle

animali(id, nome)
luoghi(id, nome)
animali_luoghi(id,id_animale, id_luogo)

esempio contenuto

_ANIMALI
_id         _nome
1           gatto
2           cane
3           mucca

_LUOGHI
_id         _nome
1           casa
2           fattoria

_ANIMALI_LUOGHI
_id       _id animale     _id_luogo
1         1                  1
2         2                  1
3         2                  2
4         3                  2

Risultato voluto con luogo = 'fattoria'

_animale     _luogo
gatto         NULL
cane          fattoria
mucca        fattoria

esempio di query possibile:

(
 SELECT animali.nome, luogo.nome 
 FROM animali 
 LEFT JOIN animali_luoghi ON animali.id = animali_luoghi.id_animali 
 LEFT JOIN luoghi ON animali_luoghi.id = luoghi.id 
 WHERE luogo.nome = 'fattoria'
) 
UNION 
(
 (
  SELECT distinct(animali.nome), luogo.nome 
  FROM animali 
  LEFT JOIN animali_luoghi ON animali.id = animali_luoghi.id_animali 
  LEFT JOIN luoghi ON animali_luoghi.id = luoghi.id 
 )
 MINUS
 (
  SELECT animali.nome, luogo.nome 
  FROM animali 
  LEFT JOIN animali_luoghi ON animali.id = animali_luoghi.id_animali 
  LEFT JOIN luoghi ON animali_luoghi.id = luoghi.id 
  WHERE luogo.nome = 'fattoria'
 )
)

Fondamentalmente la query sopra prende 'tutti gli animali che abitano in fattoria' UNION ('distinct tutti gli animali' MINUS 'gli animali che abitano in fattoria').

Il problema in questo caso è che MYSQL non gestisce MINUS, me la dovrei cavare con le join!!! Come posso fare?

Grazie di cuore a chi mi darà una manoa vincere questa sfida

Innanzitutto la prima query è errata. Se devi prendere tutti gli animali che vivono in un luogo è una INNER JOIN non una OUTER JOIN.

X la MINUS vedi qui: http://www.bitbybit.dk/carsten/blog/?p=71

[Jack991]

si ma io voglio tutti gli animali che vivono in un luogo e anche quelli che non vivono in quel luogo ma comunque indipendentemente da dove vivono li voglio una ed una sola volta.

ho trovato la soluzione mettendo la condizione nella join

select animali.nome, luogo.nome from animali left join animali_luoghi on (animali.id = animali_luoghi.id_animale AND animali_luoghi.id_luogo = 2);