[mysql] problema su UNION SELECT e GROUP BY

[Petro_suse91]

Buongiorno a tutti

Ho una query SQL che è piuttosto complessa, e non si comporta come vorrei... vorrei sapere dove sbaglio.
Innanzitutto spiego:
Devo unire due SELECT, ognuna di queste SELECT ha due JOIN su altre tabelle, e va raggruppata in base a un campo.
Alla fine, vorrei ordinare i risultati in base al valore di uno dei campi presenti nelle tabelle joinate (è lo stesso campo).
Sembra che il problema sia il GROUP BY: prende per ogni id un record a caso, e quindi alla fine non mi ritrovo più con l'ordinamento...

Ecco la query:

(
	SELECT
		users_cats.catid,
		users_cats.cat_title,
		users_cats.siteurl,
		users_subscribedcats.userid,
		users_subscribedcats.catid,
		users_subscribedcats.in_sitecats,
		users_arts.date,
		users_arts.artid,
		users_arts.artlink,
		users_arts.arttitle,
		users_arts.text,
		users_arts.author
	FROM
		users_subscribedcats

	LEFT JOIN
		users_cats
	ON users_subscribedcats.catid = users_cats.catid

	LEFT JOIN
		users_arts
	ON users_cats.catid = users_arts.catid

	WHERE users_subscribedcats.userid = $userid
	AND users_subscribedcats.in_sitecats =0
	GROUP BY users_subscribedcats.catid

) UNION (

	SELECT
		cats.catid,
		cats.cat_title,
		cats.siteurl,
		users_subscribedcats.userid,
		users_subscribedcats.catid,
		users_subscribedcats.in_sitecats,
		arts.date,
		arts.artid,
		arts.artlink,
		arts.arttitle,
		arts.text, 
		arts.author
	FROM
		arts

	LEFT JOIN
		cats
	ON users_subscribedcats.catid = cats.catid

	LEFT JOIN
		users_subscribedcats
	ON users_subscribedcats.catid = arts.catid

	WHERE users_subscribedcats.userid = $userid
	AND users_subscribedcats.in_sitecats =1
	GROUP BY arts.catid
)

ORDER BY date DESC LIMIT 20

Sono sicuro che c'è un errore concettuale, più che di distrazione, ma non riesco a capire qual è... nemmeno con l'aiuto del manuale.

Mi dareste una mano, per cortesia?

[Petro_suse91]

[Petro_suse91]

:rollo:

[Mix]

non me ne intendo tantissimo, e non saprei spiegartelo, ma col comando union perme non te la cavi, io ho sito un metodo in uno script che dava una lettera alle cose del tipo

SELECT m.id, l.nome, l.cognome FROM m.USER l.INFO

ma non sò dirti altro.

Spero di esserti stato di aiuto. Mix

[Petro_suse91]

Ti ringrazio della risposta, Mix, però purtroppo non mi è di molto aiuto...
attendo altri pareri

[skidx]

la query dice tutto e dice niente.

Spiega per sommi capi le tabelle e cosa vuoi estrarre, vediamo se possiamo esserti d'aiuto a riformularla meglio.

[platone]

La UNION serve ad unire due query che stampano esattamente lo stesso set di risultati.

Prima di tutto devi verificare che le due select girino correttamente senza UNION o se danno problemi.
Se l'errore è il GROUP BY con o senza UNION verrà fuori di nuovo.

[platone]

Alcune domande per capire...

users_cats.catid è un ID univoco/primary?
users_subribedcats è un ID univoco/primary?

e poi....

Hai notato che estrai tanti campi da molte tabelle?
Facendo un group by si possono estrarre SOLO campi che alla presenza di più "catid" uguali tra loro, i campi selezionati non cambiano. Perchè se cambiano mysql non sa quale visualizzarti (trova magari 4 artid per 1 solo catid e non sa quale dei 4 visualizzarti).

Considerando il numero di campi che estrai...

users_cats.catid,
users_cats.cat_title,
users_cats.siteurl,
users_subscribedcats.userid,
users_subscribedcats.catid,
users_subscribedcats.in_sitecats,
users_arts.date,
users_arts.artid,
users_arts.artlink,
users_arts.arttitle,
users_arts.text,
users_arts.author

.... direi che il GROUP BY è impossibile.

Perchè:
Può funzionare solo se i campi CATID sono tutti primary e univoci in tutte le tabelle (alquanto improbabile) e anche se fosse in questo determinato caso un group by sarebbe totalmente inutile, si otterrebbe cmq 1 solo record per ogni CATID (e quindi perchè gruppare?) anche senza usarlo.

Spiega perchè vuoi effettuare il GROUP BY a tutti i costi e potremo darti una mano.

[Petro_suse91]

le due SELECT che ho intenzione di unire, con relativi JOIN al seguito, sono tra loro equiparabili: in realtà una volta JOINATE, nei campi selezionati ho lo stesso identico tipo di dati.
Solo che la prima SELECT (con relativi JOIN) si riferisce alle tabelle con prefisso USER_ e nella seconda SELECT (con relativi join) si riferisce alle stesse tabelle senza tale prefisso.
Questo succede perchè i dati che devo pescare possono essere mischiati su più tabelle (non per caso, è voluto per evitare grandi moli di dati duplicati)
Allora io uso il campo 'in_sitecats' WHERE =1 o WHERE =0 per distinguere se devo pescare i dati in 'USER_tabella' o in 'tabella'.


In un esempio che spero serva per semplificare:
le tabelle t1 e t2 sono ciascuna il risultato di una delle 2 select con i join:

t1: id, nome, cognome, data, sesso
     1   pio    rossi    04      m
     3   gio    verdi    05      m
     4   pio   bianchi   98      m
     8   pio      blu    01      m
----------------------------------
t1 ha pescato dati da USER_cats

t2: id, nome, cognome, data, sesso
     2   lea     rosa    03      f
     3   lea    rossi    06      f
     7  alia     mici    02      f
----------------------------------
t2 ha pescato dati da cats

il campo 'sesso' è quello che viene dalla condizione WHERE, ovvero quello che contraddistingue e differenzia ciascuna delle due SELECT. (sarebbe 'in_sitecats' della query reale).

il campo 'nome' è quello che voglio raggruppare ('catid'): per ogni nome voglio avere un solo record che rappresenta. Però vorrei PRIMA ordinare ognuna delle due tabelle per 'data', in modo che per ogni 'catid' ci sia un solo record e sia quello più recente.

Il risultato finale, nelle intenzioni, dovrebbe essere il seguente:
(Tanto per chiarire, la query funziona (sì catid è primary key ovunque), non restituisce errori SQL e anzi restituisce una cosa che assomiglia a questo risultato di esempio.)

tx: id, nome, cognome, data, sesso
     3   lea    rossi    06      f
     1   pio    rossi    04      m
     7  alia     mici    02      f
     8   pio      blu    01      m

Io una cosa del genere riesco, più o meno, ad ottenerla... i campi sono dove dovrebbero essere, e i tipi di dati sono ognuno nel proprio campo.

IL PROBLEMA E' che, nel raggruppare, invece di prendere il record più recente, prende quello che sembra essere un record a caso!!!

Per cui ottengo

tx: id, nome, cognome, data, sesso
     3   gio    verdi    05      m
     4   pio   bianchi   98      m
     2   lea     rosa    03      f
     7  alia     mici    02      f

Se non faccio GROUP BY ma solo ORDER BY nome, data ottengo il risultato voluto ma ovviamente ho un sacco di record in più (tutti quelli con lo stesso nome e data + vecchia) che non mi servono.
Non posso fare LIMIT perchè non so a priori quanti 'nome' contiene la tabella col risultato finale.


Spero di essere riuscito a spiegarmi in modo più o meno chiaro....

[platone]

Sbagli! in tutti e due i casi prende risultati a caso.
E' solo un caso che ti prenda risultati recenti ma se non fai OPTIMIZE TABLE e se la tabella è soggetta a numerosi DELETE allora anche questa certezza verrà a meno.

Quando prendi i campi:

cognome,
data,
sesso

che possono comparire in diverse e svariate forme per ogni nome, mysql prende un record a caso, con altissime probabilità il più recente (ma senza alcuna certezza in realtà).

quindi:

lea, rossi, 01, F
lea, bianchi, 02, M

è assolutamente casuale quale di questi due prenderà, presumibilmente l'ultimo inserito ma non è detto assolutamente, la UNION poi fa saltare questa certezza perchè fonde due tabelle e in questo caso i dati vengono presi completamente in base a come viene eseguito e senza un controllo da parte della SELECT.

Per estrarre particolari numeri in GROUP BY puoi usare le funzioni MIN(), MAX() queste funzioni restituiscono all'interno del gruppo il campo con la data più alta o più bassa.

Per i campi testuali invece non esiste un metodo ad hoc per estrarre un campo particolare, poichè non è numerica la scelta e quindi è impossibile chiedere a mysql se:

rossi è >, <, o = a bianchi

Però puoi provare a cercare qualche soluzione usando la funzione IF() che permette di effettuare dei controlli, tuttavia con il GROUP BY e i campi testuali non vai troppo lontano.

Se puoi usa il GROUP_CONCAT e poi estrai il record con la data maggiore.