[mysql] problemen met dubbel joinen van een tabel

[marty]

Het probleem:

Ik heb een tabel ActieLijst, waar records in kunnen bestaan met gewoon wat tekst en waar records in kunnen bestaan met een verwijzing naar een andere tabel. Op dit moment zijn dat er nog maar 2 maar dat kunnen er eventueel meer worden. Ik heb die verwijzing opgelost door twee velden op te nemen: TableName en TableID - dat levert opzich geen probleem op met joinen

MySQL:

 
CREATE TABLE ActieLijst (
  ActieLijstID bigint(20) unsigned NOT NULL auto_increment,
  UserID int(11) unsigned NOT NULL default '0',
  TableID int(10) unsigned default NULL,
  TableName enum('opdrachten','rbs') default NULL,
  Title varchar(255) default NULL,
  Body text,
  EventDate date default NULL,
  EventStatus enum('','done','deleted') NOT NULL default '',
  Warning datetime default NULL,
  Added datetime default NULL,
  Edited timestamp(14) NOT NULL,
  Moved text,
  PRIMARY KEY  (ActieLijstID),
  UNIQUE KEY ActieLijstID (ActieLijstID),
  KEY UserID (UserID),
  KEY TableID (TableID)
) TYPE=MyISAM;

In de huidige situatie kan het zijn dat er een verwijzing is naar de opdrachten tabel of de rbs tabel. Geen punt. Alleen nu komt het: de rbs tabel is een vrij belangrijke omdat daar bedrijfsinformatie in staat. De opdrachten tabel moet naar deze tabel kijken om dingen als bedrijfsnaam te weten te komen - dit wordt uiteraard gedaan dmv 1 veld die met de PK uit de rbs tabel overeenkomt.

MySQL:

# alleen relevante velden
CREATE TABLE opdrachten (
    id bigint(20) unsigned NOT NULL auto_increment,
    bedrijfs_id bigint(20) unsigned default NULL,
    functie varchar(250) default NULL,
    PRIMARY KEY  (id),
    KEY obid (bedrijfs_id),
) TYPE=MyISAM;

CREATE TABLE rbs (
    id bigint(20) unsigned NOT NULL auto_increment,
    bedrijfsnaam varchar(100) NOT NULL default '',
    reden varchar(255) default NULL,
    PRIMARY KEY  (id),
) TYPE=MyISAM;

Probleem: Ik wil met 1 query de volgende informatie:
- alles uit de ActieLijst tabel
- id en functie uit de opdrachten tabel als de TableName op opdracht staat
- id en reden uit de rbs tabel als de TableName op rbs staat
- een bedrijfsnaam als TableName op opdracht of rbs staat,
Het punt is dat ik in het geval van een rbs-verwijzing gelijk de bedrijfsnaam zou kunnen benaderen, maar in een opdrachten geval niet. Dan moet ik ook de rbs-tabel er nog eens op joinen.

Ik ben tot deze query gekomen:

MySQL:

    SELECT
        AL.*,
        DATE_FORMAT(EventDate, '%d-%m-%Y') AS Date,
        IF(o.bedrijfs_id, o.bedrijfs_id, rbs.id) AS bedrijfs_id,
        o.id AS oid, o.functie,
        b.id AS bid, b.bedrijfsnaam,
        rbs.reden
    FROM ActieLijst AS AL
        LEFT JOIN opdrachten AS o ON o.id = AL.TableID AND AL.TableName='opdrachten'
        LEFT JOIN rbs ON rbs.id = AL.TableID AND AL.TableName='rbs'
        LEFT JOIN rbs AS b ON b.id = bedrijfs_id
    WHERE
        UserID = 15 AND
        EventStatus != 'deleted'
        AND EventDate='2004-05-10'

hiermee:
IF(o.bedrijfs_id, o.bedrijfs_id, rbs.id) AS bedrijfs_id
zorg ik er voor dat als er een bedrijfs_id in 1 van beide tabellen bekend is, dat deze onder de alias bedrijfs_id bekend is. En die alias gebruik ik later dan weer om de rbs tabel op je joinen (LEFT JOIN rbs AS b ON b.id = bedrijfs_id)
Ik vind het zelf ook een beetje gekunstel, maar het is de enige manier omdat ik geen subqueries kan gebruiken.

Het gekke is alleen, dat dit in het geval van een opdracht wel goed gaat en in het geval van een rbs niet. Als er een opdracht in staat dan krijg ik netjes een bedrijfsnaam daarbij en in het geval van een rbs blijft ie leeg (er wordt dus niets gejoined). Dat is zeer vreemd, want in m'n resultset heeft bedrijfs_id wel een getal in beide gevallen. Als ik dat getal (bedrijfs_id) hard inklop in m'n query (dus LEFT JOIN rbs AS b ON b.id = 1234) dan blijkt ie 'm wél gewoon te pakken. Op 1 of andere manier gaat er dus iets fout bij de 'rbs-records' : bedrijfs_id heeft wel de juiste waarde, maar toch gebruikt ie die waarde niet om te joinen - terwijl dit op exact dezelfde wijze bij een 'opdracht-record' wel goed gaat.



Ik hoop dat het duidelijk is, het is een beetje vaag probleem
en hoop ook dat iemand een oplossing weet

[BrZ]

Haal die AND dingen uit je LEFT JOINS, en laat je client (PHP?) bepalen of het een "rbs" of "opdracht" is

[marty]

BrZ schreef op 23 januari 2004 @ 19:35:
Haal die AND dingen uit je LEFT JOINS, en laat je client (PHP?) bepalen of het een "rbs" of "opdracht" is

Dan moet ik dus 3 queries doen: 1 om van alle punten te bepalen of het rbs op opdracht is en vervolgens nog eens 2 om ze gescheiden op te halen. En dat wordt er bij iedere tabel die er eventueel nog bij komt een query meer. Dat wil ik dus liever niet.

[BrZ]

marty schreef op 23 januari 2004 @ 20:43:
[...]


Dan moet ik dus 3 queries doen: 1 om van alle punten te bepalen of het rbs op opdracht is en vervolgens nog eens 2 om ze gescheiden op te halen. En dat wordt er bij iedere tabel die er eventueel nog bij komt een query meer. Dat wil ik dus liever niet.

Nee, je kan ze gewoon joinen, als een veld leeg is boeit het niet (left join), en welke kolom(men) je uitleest bepaal je aan de hand van de waarde van "Tablename".

[marty]

maar hoe kan ik volgens jou in php bepalen wat in een mysql-database staat zonder meer dan 1 query te doen? ik denk dat je het probleem niet helemaal begrijpt

of ik snap jou gewoon niet , maar zou je dan wat preciezer kunnen zijn?

[ Voor 20% gewijzigd door marty op 23-01-2004 21:27 ]

[BrZ]

Gewoon beide tabellen left joinen op tableid, en dan aan de hand van tablename bepalen welke kolommen je nodig hebt. Dat de waardes in de kolommen van opdrachten leeg of verkeerd zijn als je rbs nodig hebt maakt niet uit, aangezien je alleen de andere gebruikt.

[marty]

en hoe tover ik dan de bedrijfsnaam tevoorschijn? da's nou juist het probleem!

[Verwijderd]

Je doet een SELECT AL.* // FROM ActieLijst AS AL.
Hier is o.id en b.id nog niet bekend omdat de FROM alleen Actielijst AS AL bevat.

Nadat het SELECT // FROM blok is afgesloten wordt de LEFT JOIN uitgevoerd.

[BrZ]

Verwijderd schreef op 24 januari 2004 @ 00:02:
Je doet een SELECT AL.* // FROM ActieLijst AS AL.
Hier is o.id en b.id nog niet bekend omdat de FROM alleen Actielijst AS AL bevat.

Nadat het SELECT // FROM blok is afgesloten wordt de LEFT JOIN uitgevoerd.

nee?
SELECT wordt als laatste gedaan, als er dmv de rest van de query een "tabel" is gevormd waar hij een regel van inleest.

marty schreef op 23 januari 2004 @ 23:18:
en hoe tover ik dan de bedrijfsnaam tevoorschijn? da's nou juist het probleem!

Ik zie niet in waarom je die laatste LEFT JOIN die je doet dan opeens niet meer zou kunnen zoen?
Dat het er dan soms dubbel in staat is dan jammer, maar als je dat niet wil zou je je hele database om moeten gooien.

[marty]

BrZ schreef op 24 januari 2004 @ 00:16:
Ik zie niet in waarom je die laatste LEFT JOIN die je doet dan opeens niet meer zou kunnen zoen?
Dat het er dan soms dubbel in staat is dan jammer, maar als je dat niet wil zou je je hele database om moeten gooien.

Volgens mij praten we langs elkaar heen. Zou je anders gewoon eens de query kunnen maken. Niet omdat ik lui ben hoor, maar dan zie ik precies wat je bedoelt. Ik vermoed namelijk dat je het precieze probleem niet ziet (wat ik ook niet gek vind, want het is een zeer vaag probleem)

[BrZ]

code:

SELECT
        AL.*,
        DATE_FORMAT(EventDate, '%d-%m-%Y') AS Date,
        o.bedrijfs_id, rbs.id,
        o.id AS oid, o.functie,
        b.id AS bid, b.bedrijfsnaam, rbs.bedrijfsnaam,
        rbs.reden
    FROM ActieLijst AS AL
        LEFT JOIN opdrachten AS o ON o.id = AL.TableID
        LEFT JOIN rbs ON rbs.id = AL.TableID
        LEFT JOIN rbs AS b ON b.id = o.bedrijfs_id
    WHERE
        UserID = 15 AND
        EventStatus <> 'deleted'
        AND EventDate='2004-05-10'

Daarna bepaal je per regel of Tablenaam rbs is of een opdracht is, en daaruit bepaal je of je de kolom b.bedrijfsnaam of rbs.bedrijfsnaam gebruikt.

[ Voor 4% gewijzigd door BrZ op 24-01-2004 09:12 ]

[marty]

aaaahhhhh, nu snap ik wat je bedoelt. Sorry, ik zat aan heel iets anders te denken.
Ik zal hier alleen wel een kleine wijziging op moeten maken, omdat je niet 2 dezelfde veldnamen in je resultset kan hebben, maar da's het probleem niet.

Tja, echt mooi vind ik het niet, maar nu heb ik in iedergeval een oplossing.

Maar eigenlijk wil ik ook graag weten hoe het komt dat in mijn bovenstaande query, dat ondanks dat bedrijfs_id een waarde krijgt, hij voor een opdracht wel gejoined kan worden, maar voor een rbs niet Dat vind ik echt vaag.
Zou het een bug kunnen zijn?

edit
Ik ben er al achter!
het komt omdat de alias bedrijfs_id ook al bestaat als veld -> maar alleen in de opdrachten tabel en niet in de rbs tabel. Hij pakt dus helemaal de alias niet zoals ik eerst dacht. (stom trouwens dat ik me dat niet eerder had bedacht, want daar ben ik al veel vaker tegenaan gelopen )

De correcte query wordt dan dus:

MySQL:

    SELECT
        AL.*,
        DATE_FORMAT(EventDate, '%d-%m-%Y') AS Date,
        IF(o.bedrijfs_id, o.bedrijfs_id, rbs.id) AS bedrijfs_id,
        o.id AS oid, o.functie,
        b.id AS bid, b.bedrijfsnaam,
        rbs.reden
    FROM ActieLijst AS AL
        LEFT JOIN opdrachten AS o ON o.id = AL.TableID AND AL.TableName='opdrachten'
        LEFT JOIN rbs ON rbs.id = AL.TableID AND AL.TableName='rbs'
        LEFT JOIN rbs AS b ON b.id = IF(o.bedrijfs_id, o.bedrijfs_id, rbs.id)
    WHERE
        UserID = 15 AND
        EventStatus != 'deleted'
        AND EventDate='2004-05-10'

En dit werkt precies zoals ik het wil

[ Voor 48% gewijzigd door marty op 24-01-2004 21:23 ]