Hi Everybody,
i have this multi UNION select query performing on 1 table. However it doesn't seem to be working.
__________________________
__________
__________
___
My goal is this:
eg.:
Table looks a little like this:
____________________
fname ;artcode;klrcode
;D1309;1
;D1309;2
D1309-8.jpg ;D1309;8
;D1310;1
D1310-2.jpg ;D1310;2
;D1310;8
D1312-2.jpg ;D1312;2
;D1313;1
D1313.jpg ;D1313;2
;D1314;1
D1314-2.jpg ;D1314;2
;D1318;1
;D1318;2
;D1318;5
;D1319;1
;D1319;2
D1319-5.jpg ;D1319;5
Query result should be like this:
in this case 7 rows and
if an artcode has fname <> '' then the first one with an fname
if none of a specific artcode has an fname then the first without an fname
_________________________
D1309-8.jpg ;D1309;8
D1310-2.jpg ;D1310;2
D1312-2.jpg ;D1312;2
D1313.jpg ;D1313;2
D1314-2.jpg ;D1314;2
;D1318;1
D1319-5.jpg ;D1319;5
When query containes only this:
D1309
D1310
D1312
D1313
D1314
D1318
D1319
When i started building i used a testing code like this one:
SELECT *, '3' As ExTFld, '0' As ExTFld2 FROM productlist WHERE artcode LIKE 'D1309' LIMIT 0,1 UNION
SELECT *, '3' As ExTFld, '0' As ExTFld2 FROM productlist WHERE artcode LIKE 'D1310' LIMIT 0,1 UNION
SELECT *, '3' As ExTFld, '0' As ExTFld2 FROM productlist WHERE artcode LIKE 'D1312' LIMIT 0,1 UNION
SELECT *, '3' As ExTFld, '0' As ExTFld2 FROM productlist WHERE artcode LIKE 'D1313' LIMIT 0,1 UNION
SELECT *, '3' As ExTFld, '0' As ExTFld2 FROM productlist WHERE artcode LIKE 'D1314' LIMIT 0,1 UNION
SELECT *, '3' As ExTFld, '0' As ExTFld2 FROM productlist WHERE artcode LIKE 'D1318' LIMIT 0,1 UNION
SELECT *, '4' As ExTFld, '6' As ExTFld2 FROM productlist WHERE artcode LIKE 'D1319' ORDER BY ExTFld2 ASC, fname DESC LIMIT 0, 7
ExtFld and ExtFld2 are 2 extra fields that are 2 be shown as a seperate fields
I've been trying and experimenting with all kinds of things
- my first thought was to use this ORDER BY fname DESC
ONLY this only works on the last SELECT because the others are LIMITED
- conditional WHERE clause was my second geuss althought it seems like i can't get it to work
eg:
SELECT *, '3' As ExTFld, '0' As ExTFld2 FROM productlist WHERE
CASE WHEN artcode LIKE 'D1309' AND fname <> '' THEN artcode LIKE 'D1309' AND fname <> '' ELSE artcode LIKE 'D1309' END
LIMIT 0,1 UNION
...
....
This is not good cause every line is shown when i would take away LIMIT 0,1 so in this case every first result is shown
Maybe i'm overlooking the problem.
Anybody got any clues?
