[MySql] Subquery naar join vertalen

[Verwijderd]

Ik heb nogal een complexe DB structuur in MySql 5.0.16 met volgende tabel

PK newsCatID
PK languageID
PK version
catName
statusID
visibility

Omdat er meerdere records met newsCatID 1 kunnen bestaan heb ik de volgende query gebouwd om enkel de laatste version weer te geven van een ID waar de status op 4 staat (published) en de visibility op 1 (visible)

code:

SELECT mnc1.catName FROM modnewscategory mnc1 
WHERE mnc1.version=(SELECT MAX(mnc2.version) 
                     FROM modnewscategory mnc2 
                     WHERE mnc2.statusID='4' AND mnc2.visibility='1' 
                     AND mnc2.newsCatID=mnc1.newsCatID) 
GROUP BY mnc1.newsCatID

Dit werkt overigens perfect op MySql 5.0.16

Nu moet dit ding ook kunnen draaien op MySql 4.0.x (die geen subqueries ondersteund) maar ik krijg het ding niet naar JOINS geport zoals we dat mooi kunnen zeggen.

Iemand suggesties?

[NMe]

SQL:

SELECT newsCatID, catName, MAX(version) AS maxversion
FROM modnewscategory mnc1 
WHERE statusID = 4 AND visibility = 1
GROUP BY newsCatID, catName
HAVING version = MAX(version)

[Verwijderd]

-NMe- schreef op woensdag 22 februari 2006 @ 00:26:

SQL:

SELECT newsCatID, catName, MAX(version) AS maxversion
FROM modnewscategory mnc1 
WHERE statusID = 4 AND visibility = 1
GROUP BY newsCatID, catName
HAVING version = MAX(version)

Spijtig maar helaas werkt dit niet, dit geeft namelijk alle rijen terug die als statusID 4 hebben en als visibility 1

Er moet trouwens nog version bij in de select anders krijgt ie een error.

Ik krijg nu het volgende terug van MySql

code:

newsCatID         catName                                  version      maxversion
1                   eerste cat                             1.00     1.00
1                   eerste cat aangepast                 1.01     1.01
2                   tweede cat                            1.00         1.00
2                   tweede cat aangepast                1.01     1.01
2                   tweede cat nog eens aangepast  1.02         1.02

En zou enkel de tweede en de 5 de rij willen terug krijgen uit dit result (zoals met de subquery)

[Coltrui]

Logisch als catName verandert, ook al hebben ze hetzelfde ID.

[Verwijderd]

Coltrui schreef op woensdag 22 februari 2006 @ 08:27:
Logisch als catName verandert, ook al hebben ze hetzelfde ID.

inderdaad, maar dit heeft me nog niet echt vooruit geholpen. Is elke subquery dan niet te vertalen naar een JOIN?

[NMe]

Verwijderd schreef op woensdag 22 februari 2006 @ 08:43:
inderdaad, maar dit heeft me nog niet echt vooruit geholpen. Is elke subquery dan niet te vertalen naar een JOIN?

Nee, niet allemaal nee. Maar deze ziet er niet ondoenbaar uit. Ik zal straks ales ik wat meer tijd heb nog eens kijken of ik wat kan verzinnen.

[Verwijderd]

Dat zou in ieder geval heel welkom zijn ja. Kom er echt niet uit.

[ACM]

Als je het van 1 item wilt kan je het vast wel oplossen door zonder joins, maar met de where uit je huidige subquery, te sorteren op version en dan limit 1. Als je van een hele rits items steeds de hoogste wilt wordt het lastig om het in 1 query te doen.

[ Voor 13% gewijzigd door ACM op 22-02-2006 10:58 ]

[Verwijderd]

ACM schreef op woensdag 22 februari 2006 @ 10:58:
Als je het van 1 item wilt kan je het vast wel oplossen door zonder joins, maar met de where uit je huidige subquery, te sorteren op version en dan limit 1. Als je van een hele rits items steeds de hoogste wilt wordt het lastig om het in 1 query te doen.

Ik wil dus een hele rits

[-FoX-]

Verwijderd schreef op woensdag 22 februari 2006 @ 12:00:
[...]
Ik wil dus een hele rits

Hmm, deze moet toch wel haalbaar zijn denk ik.

Probeer volgende eens:

SQL:

SELECT mnc1.newsCatId, mnc1.catName, mnc1.version
FROM modnewscategory mnc1, modnewscategory mnc2
WHERE mnc1.statusId=4 AND mnc1.visibility=1
GROUP BY mnc2.newsCatId, mnc1.version
HAVING mnc1.version = MAX(mnc2.version)

Misschien wel iets minder performant, maar het zou toch moeten voldoen...

En anders updaten die handel!

[ACM]

Verwijderd schreef op woensdag 22 februari 2006 @ 12:00:
Ik wil dus een hele rits

Ik gok dat een temporary table het handigst is dan, ala:

SQL:

CREATE TEMPORARY TABLE max_versions AS
  SELECT ID, MAX(version) as version FROM ... WHERE ... GROUP  BY ID

SELECT ... FROM ... t 
  JOIN max_versions m ON t.ID = m.ID AND t.version = m.version

[Verwijderd]

-FoX- schreef op woensdag 22 februari 2006 @ 12:30:
[...]

Hmm, deze moet toch wel haalbaar zijn denk ik.

Probeer volgende eens:

SQL:

SELECT mnc1.newsCatId, mnc1.catName, mnc1.version
FROM modnewscategory mnc1, modnewscategory mnc2
WHERE mnc1.statusId=4 AND mnc1.visibility=1
GROUP BY mnc2.newsCatId, mnc1.version
HAVING mnc1.version = MAX(mnc2.version)

Misschien wel iets minder performant, maar het zou toch moeten voldoen...

En anders updaten die handel!

spijtig maar helaas, deze geeft 2 maal dezelfde rij terug ipv 1 per ID (hij gaf 2 rijen met ID 2 terug)

Updaten zou ik met alle plezier done maar de server wordt niet door mezelf gehost dus ...

offtopic:
yeww FoX, klaar voor de revanche over 14 dagen

[Verwijderd]

ACM schreef op woensdag 22 februari 2006 @ 13:10:
[...]

Ik gok dat een temporary table het handigst is dan, ala:

SQL:

CREATE TEMPORARY TABLE max_versions AS
  SELECT ID, MAX(version) as version FROM ... WHERE ... GROUP  BY ID

SELECT ... FROM ... t 
  JOIN max_versions m ON t.ID = m.ID AND t.version = m.version

Dan kan ik beter een lusie maken in mijn script als we het toch iet wat performant willen houden

[-FoX-]

Verwijderd schreef op woensdag 22 februari 2006 @ 13:50:
[...]
spijtig maar helaas, deze geeft 2 maal dezelfde rij terug ipv 1 per ID (hij gaf 2 rijen met ID 2 terug)

Updaten zou ik met alle plezier done maar de server wordt niet door mezelf gehost dus ...

offtopic:
yeww FoX, klaar voor de revanche over 14 dagen

Bist du sicher?

Heb uw tabelletje in de mate van de info die ik heb, hier eens gereproduceerd en ik krijg wel mooi de juiste data terug:

code:

+-----------+--------------------------+---------+
| newsCatId | catName                  | version |
+-----------+--------------------------+---------+
|         1 | eerste cat aangepast     |    1.01 |
|         2 | tweede cat opn aangepast |    1.02 |
+-----------+--------------------------+---------+
2 rows in set (0.00 sec)

offtopic:
Damn sure... (had al stiekem gehoopt dat je me zou laten winnen na deze 'tip' )

[Verwijderd]

Ik krijg dit terug

code:

+-----------+-------------------------------+---------+
| newsCatId | catName                       | version |
+-----------+-------------------------------+---------+
|         2 | tweede cat nog eens aangepast |    1.02 |
|         2 | tweede cat nog eens aangepast |    1.02 |
+-----------+-------------------------------+---------+

Als ik een select op de table doe krijg ik deze records, mss helpt het wel

code:

+-----------+------------+---------+-------------------------------+----------+------------+
| newsCatID | languageID | version | catName                       | statusID | visibility |
+-----------+------------+---------+-------------------------------+----------+------------+
|         1 |          1 |    1.00 | eerste cat                    |        4 |          1 |
|         1 |          1 |    1.01 | eerste cat aangepast          |        4 |          1 |
|         1 |          1 |    1.02 | eerste cat nog eens aangepast |        3 |          1 |
|         2 |          1 |    1.00 | tweede cat                    |        4 |          1 |
|         2 |          1 |    1.01 | tweede cat aangepast          |        4 |          1 |
|         2 |          1 |    1.02 | tweede cat nog eens aangepast |        4 |          1 |
+-----------+------------+---------+-------------------------------+----------+------------+

[ACM]

Verwijderd schreef op woensdag 22 februari 2006 @ 13:51:
Dan kan ik beter een lusie maken in mijn script als we het toch iet wat performant willen houden

Jij denkt dat een lus in php sneller is dan een temp table in mysql aanmaken?
Ik zou het es testen voor je het gelijk maar afschrijft als niet-performant, sowieso is het minder werk kwa code om te maken. Bedenk wel dat als je er heeel veel wilt, het handig is de temp table eerst te definieren en er indices op aan te leggen.

[ Voor 13% gewijzigd door ACM op 22-02-2006 14:26 ]

[-FoX-]

Verwijderd schreef op woensdag 22 februari 2006 @ 14:14:
Ik krijg dit terug

code:

+-----------+-------------------------------+---------+
| newsCatId | catName                       | version |
+-----------+-------------------------------+---------+
|         2 | tweede cat nog eens aangepast |    1.02 |
|         2 | tweede cat nog eens aangepast |    1.02 |
+-----------+-------------------------------+---------+

Als ik een select op de table doe krijg ik deze records, mss helpt het wel

code:

+-----------+------------+---------+-------------------------------+----------+------------+
| newsCatID | languageID | version | catName                       | statusID | visibility |
+-----------+------------+---------+-------------------------------+----------+------------+
|         1 |          1 |    1.00 | eerste cat                    |        4 |          1 |
|         1 |          1 |    1.01 | eerste cat aangepast          |        4 |          1 |
|         1 |          1 |    1.02 | eerste cat nog eens aangepast |        3 |          1 |
|         2 |          1 |    1.00 | tweede cat                    |        4 |          1 |
|         2 |          1 |    1.01 | tweede cat aangepast          |        4 |          1 |
|         2 |          1 |    1.02 | tweede cat nog eens aangepast |        4 |          1 |
+-----------+------------+---------+-------------------------------+----------+------------+

Ahja, natuurlijk

SQL:

SELECT mnc1.newsCatId, mnc1.catName, mnc1.version
FROM modnewscategory mnc1, modnewscategory mnc2
WHERE (mnc1.statusId=4 AND mnc1.visibility=1)
AND   (mnc2.statusId=4 AND mnc2.visibility=1)
GROUP BY mnc2.newsCatId, mnc1.version
HAVING mnc1.version = MAX(mnc2.version)

Maar ik weet eigenlijk niet ofdat dit nu zo'n mooie oplossing is. Ik denk wel dat het sneller is dan zelf te gaan lopen in je applicatie logica. Zeker als de DB server op een aparte machine draait.

Je kan de gewone query er in commentaar laten bijstaan. Als ze dan ooit gaan upgraden heb je in no-time geswitcht naar de nieuwe versie.

edit:
Waarom staan WHERE en AND eigenlijk in het blauw en de rest van de keywords in het groen?

[ Voor 3% gewijzigd door -FoX- op 22-02-2006 14:28 ]

[Verwijderd]

-FoX- schreef op woensdag 22 februari 2006 @ 14:26:
[...]

Ahja, natuurlijk

SQL:

SELECT mnc1.newsCatId, mnc1.catName, mnc1.version
FROM modnewscategory mnc1, modnewscategory mnc2
WHERE (mnc1.statusId=4 AND mnc1.visibility=1)
AND   (mnc2.statusId=4 AND mnc2.visibility=1)
GROUP BY mnc2.newsCatId, mnc1.version
HAVING mnc1.version = MAX(mnc2.version)

Maar ik weet eigenlijk niet ofdat dit nu zo'n mooie oplossing is. Ik denk wel dat het sneller is dan zelf te gaan lopen in je applicatie logica. Zeker als de DB server op een aparte machine draait.

Je kan de gewone query er in commentaar laten bijstaan. Als ze dan ooit gaan upgraden heb je in no-time geswitcht naar de nieuwe versie.

wiii deze doet het perfect.

offtopic:
consider that game as won next week

[Verwijderd]

Nog even voor de geïnteresseerden onder ons.

De 2de query (zonder subquery) blijkt sneller te zijn na enkele metingen. DIt in ieder geval bij kleine hoeveelheden data in de tabel. Naarmate de grootte van de tabel groeit blijkt de subquery een beter performance te halen.