Je hebt gelijk.. De layout hiervan komt door de query die ik gebruikt hem om aan dit resultaat te komen
Die zal ook ongetwijfeld een stuk beter kunnen. 'k Zal de query eens laten zien
Wellicht zijn er suggesties die voor mij niet al te obvious zijn. Zou zomaar kunnen...
[sql]SELECT `a`.`id`,`a`.`x`,`a`.`y`,`a`.`pos`,`b`.`pos`,`c`.`pos` FROM (
SELECT `id`,`x`,`y`,`pos` FROM (
SELECT `id`-472 AS `id`, `x`-1 AS `x`, `y`+1 AS `y`, '8' AS `pos` FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`-471, `x`, `y`+1, '7' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`-470, `x`+1, `y`+1, '6' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`-1, `x`-1, `y`, '5' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+1, `x`+1, `y`, '4' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+470, `x`-1, `y`-1, '3' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+471, `x`, `y`-1, '2' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+472, `x`+1, `y`-1, '1' FROM `map` WHERE `resource` IN ('resource6')
) AS `` WHERE `id` IN (
SELECT `id` FROM (
SELECT `id`-472 AS `id` FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`-471 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`-470 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`-1 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+1 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+470 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+471 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+472 FROM `map` WHERE `resource` IN ('resource6')
) AS `` GROUP BY `id` HAVING count(`id`)>=2
) GROUP BY `id`,`pos`
) AS `a`,(
SELECT `id`,`x`,`y`,`pos` FROM (
SELECT `id`-472 AS `id`, `x`-1 AS `x`, `y`+1 AS `y`, '8' AS `pos` FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`-471, `x`, `y`+1, '7' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`-470, `x`+1, `y`+1, '6' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`-1, `x`-1, `y`, '5' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+1, `x`+1, `y`, '4' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+470, `x`-1, `y`-1, '3' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+471, `x`, `y`-1, '2' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+472, `x`+1, `y`-1, '1' FROM `map` WHERE `resource` IN ('resource4','resource6')
) AS `` WHERE `id` IN (
SELECT `id` FROM (
SELECT `id`-472 AS `id` FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`-471 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`-470 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`-1 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+1 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+470 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+471 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+472 FROM `map` WHERE `resource` IN ('resource4','resource6')
) AS `` GROUP BY `id` HAVING count(`id`)>=1
) GROUP BY `id`,`pos`
) AS `b`,(
SELECT `id`,`x`,`y`,`pos` FROM (
SELECT `id`-472 AS `id`, `x`-1 AS `x`, `y`+1 AS `y`, '8' AS `pos` FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`-471, `x`, `y`+1, '7' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`-470, `x`+1, `y`+1, '6' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`-1, `x`-1, `y`, '5' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+1, `x`+1, `y`, '4' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+470, `x`-1, `y`-1, '3' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+471, `x`, `y`-1, '2' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+472, `x`+1, `y`-1, '1' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6')
) AS `` WHERE `id` IN (
SELECT `id` FROM (
SELECT `id`-472 AS `id` FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`-471 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`-470 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`-1 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+1 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+470 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+471 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+472 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6')
) AS `` GROUP BY `id` HAVING count(`id`)>=5
) GROUP BY `id`,`pos`
) AS `c` WHERE `a`.`id`=`b`.`id` AND `a`.`id`=`c`.`id` AND `a`.`pos`<>`b`.`pos` AND `a`.`pos`<>`c`.`pos` AND `b`.`pos`<>`c`.`pos` ORDER BY `a`.`id`,`a`.`pos`,`b`.`pos`,`c`.`pos`[/sql]
Ik heb overigens een (tijdelijke?) oplossing gevonden: ik stop 't tabelletje uit de bijlage in een tijdelijke tabel. Daar laat 'k dan deze (inderdaad vreselijk onhandige) query op los:
[sql]SELECT `id`,`x`,`y` FROM (
SELECT * FROM `tmp` WHERE `pos1`='1' OR `pos2`='1' OR `pos3`='1' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='2' OR `pos2`='2' OR `pos3`='2' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='3' OR `pos2`='3' OR `pos3`='3' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='4' OR `pos2`='4' OR `pos3`='4' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='5' OR `pos2`='5' OR `pos3`='5' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='6' OR `pos2`='6' OR `pos3`='6' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='7' OR `pos2`='7' OR `pos3`='7' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='8' OR `pos2`='8' OR `pos3`='8' GROUP BY `id`
) AS `` GROUP BY `id` HAVING count(`id`)=8[/sql]