[MySql] select per motore di ricerca

[pgm]

Ciao a tutti

In un'applicazione ho un piccolo motore di ricerca; per brevità dirò che ha due campi 'nome' e 'cognome' e serve per ricercare quanti clienti (e i loro dati relaitvi) sono gestiti da alcune aziende.

I dati relativi ai clienti sono memorizzati in tabella1 che contiene appunto i dati come nome, cognome, ecc. i record di tabella1 sono legati a tabella2 dove sono contenuti i codici delle aziende e i codici dei clienti.
Faccio un esempio pratico

tabella anagrafica (tabella1)
Id    |    Nome     |   Cognome  
1         mario         rossi
2         luigi         verdi

L'id di tabella1 è univoco

tabella gestione clienti (tabella2)

Cod_Azienda   |   Cod_Cli

1                 1
1                 2
2                 1
2                 2
3                 2

Il mio metodo non è performante e neppure elegante. Vorrei risolvere il tutto con una query se possibile.
Infatti ricerco in tabella1 i nominativi, ciclo il risultato ed estraggo i codici delle aziente legate ai nominativi.

Si può fare una sola query?

[piero.mac]

con una normalissima query ...

SELECT * 
FROM anagrafica a
left join gestione g 
ON g.cod_cli = a.id
where ...
order by ...

[pgm]

ho provato ma senza il left join


grazie mille

[piero.mac]

[supersaibal]Originariamente inviato da pgm


ho provato ma senza il left join
grazie mille [/supersaibal]

se fai from taba, tabb

e' sempre una join, ma INNER JOIN...

[pgm]

grazie ancora

[pgm]

dunque , sicuramente sbaglio ancora qualcosa ma in locale mysql nu ce la fa e si impalla. quindi mi rivolgo a voi ancora una volta postando la struttura delle tabelle in questione.

Anagrafica: in questa tabella si apoggiano i campi della ricerca (ragione sociale, data di nascita e codice conservatoria cioè cod_cons)

CREATE TABLE `anag_dat` (
  `num_prog` int(10) NOT NULL default '0',
  `rag_soc_1` varchar(40) NOT NULL default '',
  `rag_soc_2` varchar(40) NOT NULL default '',
  `data_nascita` int(8) NOT NULL default '0',
  `luogo` varchar(30) NOT NULL default '',
  `cod_cons` varchar(4) NOT NULL default '',
  UNIQUE KEY `num_prog` (`num_prog`)
) TYPE=MyISAM;

Gestione

CREATE TABLE `gest_dat` (
  `cod_abi` int(5) unsigned zerofill NOT NULL default '00000',
  `cod_cab` int(5) unsigned zerofill NOT NULL default '00000',
  `num_prog` int(6) unsigned zerofill NOT NULL default '000000',
  `cod_cons` varchar(4) NOT NULL default ''
) TYPE=MyISAM;

Una banca entra nell'applicazione, quindi loggandosi io so il suo codice abi e codice cab, che stanno nella tabella gest_dat. accede al motore di ricerca, e inserisce i valori nei campi ragione sociale, data di nascita e conservatoria, che stanno nella tabella anag_dat.
Devo estrarre tutti i record da anag_dat e gest_dat che sono legati all'utente loggato tramite abi e cab, che sono legati tra loro tramite num_prog e cod_cons (nel primo post avevo detto che erano legati solo da un id, scusate ) e che soddisfano i criteri di ricerca.

Io ho usato questa query sotto il consiglio di piero.mac ma sicuramente o sbagliato qualcosa.

SELECT *
FROM anag_dat a
LEFT JOIN gest_dat a
ON g.num_prog = a.num_prog AND g.cod_cons = a.cod_cons
WHERE a.rag_soc_1 = '$rag1' AND 
a.rag_soc_2 = '$rag2' AND
a.cond_cons = '$cons' AND
a.data_ nascita = '$data' AND
g.cod_abi = '$abi' AND
g.cod_cab = '$cab'

mi potreste dare un mano?

[piero.mac]

guardando al volo direi che i campi id dovrebbero essere identici.

ON g.num_prog = a.num_prog
AND g.cod_cons = a.cod_cons

`num_prog` int(10) NOT NULL default '0',
`num_prog` int(6) unsigned zerofill NOT NULL default '000000'

poi quella doppia chiave e' inutile. i record devono essere selezionati in base ad una chiave univoca. Un reference lavora al meglio quando non ha significato nel contenuto della tupla ...

Se num_progr e' univoco a nulla serve la AND con cod_cons e si puo' tranquillamente eliminare. Se invece l'univocita' fosse data da num_prog + cod_cons sarebbe un errore strutturale.

quindi una chiave unica che raccordi le due tabelle con cui prelevare i record da confrontare e poi le eccezioni verranno gestite nel where.

[pgm]

ciao piero, grazie delle delucidazioni.

in realtà in gest_dat esiste una chiave univoca che racchiude questi campi:
num_prog,cod_abi,cod_cab,cod_cons

e poi esiste una chiave univoca, come hai visto su num_prog in anag_dat.

esistono queste due chiavi nelle rispettive tabelle perchè il database è alimentato importando file di testo, nella fattispecie anag.txt e gest.txt. nelle procedure di importazione uso il replace; quindi se esiste un record identico viene sovrascritto. questo il perchè delle due chiavi unique.

Poi il num_prog mi arriva differente dei tue file txt, uno col lunghezza 10 e l'altro con lunghezza 6...dimmi te, dovrei spaccare la testa al cliente.

Quindi in anag_da esistono record univoci per num_prog, invece in gest_dat esistono recordi univoci per num_prog,cod_abi,cod_cab,cod_cons.

Come posso fare allora?

[piero.mac]

in entrambe le tabelle ti manca una chiave primaria. Sicuramente sarebbe da mettere in entrambe le tabelle.

Poiche' la tabella principale (che puo' vivere in modo autonomo) e' anag_dat andra' collegata a gest_dat tramite l'id chiave primaria (attualmente mancante). La tabella anag_dat sara' in rapporto 1 a molti con la tabella gest_dat.

La query potrebbe diventare:

SELECT *
FROM anag_dat a
LEFT JOIN gest_dat g
ON g.id_anag = a.id_anag    <--- campi da aggiungere
WHERE 
a.rag_soc_1 = '$rag1' 
AND a.rag_soc_2 = '$rag2' 
AND a.cond_cons = '$cons' 
AND a.data_ nascita = '$data' 
AND g.cod_abi = '$abi' 
AND g.cod_cab = '$cab'

Attento che copiando la query ho visto che erroneamente assegni lo stesso alias "a" ad entrambe le tabelle. Hai voglia di bloccare la query..... questo nulla toglie alle osservazioni precedenti sull'ottimizzazione. Ma l'errore di certo non aiuta.

[pgm]

non tenendo conto per il momento delle informazioni che mi hai dato, ho provato così e il locale funziona bene ma sul server è molto lento

select 
a.num_prog,a.rag_soc_1,a.rag_soc_2,a.data_nascita, 
a.luogo,a.cod_cons,g.cod_abi,g.cod_cab 
from anag_dat a 
left join gest_dat g 
on g.num_prog = a.num_prog and g.cod_cons = a.cod_cons 
where a.rag_soc_1 like 'XXXXX%' and 
a.cod_cons = 'XXXX' and 
g.cod_abi = XXXX 
order by cod_abi

un'altra cosa, come faccio ad assegnare l'id univoco alle due tabelle affinchè siano legate, visto che non posso usare num_prog, che non è univoco?