02. oktober 2012 - 09:34Der er
9 kommentarer og 1 løsning
Sql problem vedr. join af to tabeller
Kære forum
Jeg er igang med at programmere nogle hjemmelavede fix til mit phpbb-forum, men jeg er stød tpå et database-problem, jeg har svært ved at løse. Håber, der er nogle kvikke/erfarne hoveder, der kan komme med et fornuftigt bud på, hvordan jeg kan løse problemet.
Min database indeholder følgende to tabeller (skåret ind til benet!):
mysql> explain database topics;
-----------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------------+------+-----+---------+-------+ | topic_id | mediumint(8) unsigned | NO | PRI | 0 | | +-----------+-----------------------+------+-----+---------+-------+
mysql> explain database topics_track;
+-----------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------------+------+-----+---------+-------+ | user_id | mediumint(8) unsigned | NO | PRI | 0 | | | topic_id | mediumint(8) unsigned | NO | PRI | 0 | | | mark_time | int(11) unsigned | NO | | 0 | | +-----------+-----------------------+------+-----+---------+-------+
Table topics indeholder alle topics på forummet, og topics_track indeholder alle læste topics for brugerne, hvor mark_time angiver tidspunktet for læsningen. Har en bruger ikke læst en topic, figurer han dog ikke i tabellen.
Jeg ønsker at vise en liste, der indeholder både læste og ulæste topics for en specifik bruger med en status læst/ulæst tilknyttet hver topic.
Hvis jeg benytter følgende sql (fx for bruger med id 53):
SELECT T.topic_id, TT.mark_time FROM topics as T LEFT JOIN topics_track as TT ON TT.topic_id=T.topic_id WHERE TT.user_id=53 order by T.topic_id;
så vil jeg ikke få de topics med, som brugeren ikke har læst endnu, da disse ikke figurer i topics_track. Hvis jeg på en eller anden måde kunne få dem med, men hvor mark_time fx havde værdien NULL på de topics, der ikke var læste, så ville jeg have noget brugbart. Har dog lidt svært ved at lure, hvordan det lige kan lade sig gøre.
Håber, der er nogen, der kan komme med et fornuftigt bud!
Jeg vil foreslå en UNION query, hvor du først søger efter de topic_ids læst af bruger 53, og derefter søger efter de topics der ikke i topic_tracs har en bruger_id 53. Såsom (ikke testet):
SELECT T.topic_id, TT.mark_time, 'laest' FROM topics T JOIN topics_track TT ON TT.topic_id = T.topic_id WHERE TT.user_id = 53 UNION SELECT topic_id, '', 'ulaest' FROM topics WHERE topic_id NOT IN(SELECT topic_id FROM topic_track WHERE user_id = 53) ORDER BY topic_id
Tak for buddet, Christian. Har selv overvejet UNION. Det skal dog nævnes, at listen bliver genereret globalt under forummet, så sql'en skulle gerne være nogenlunde hurtig, hvis databasen skal kunne holde til den. Brug af både UNION og IN er temmelig hårde kald for databasen (specielt sidstnævne). Jeg var mere på udkig efter en god JOIN løsning, hvor man kunne få alle topics med i table topics også selvom der ikke var en tilsvarende værdi i table topics_track - og i disse tilfælde få en NULL-værdi el.lign. ud som mark_time. Er slet ikke klar over, om problemet kan løses. I så fald skal jeg gerne give point for dit svar :-)
Om det kan løses uden UNION ved jeg ikke (jeg ventede med dette svar for at se, om andre i mellemtiden havde en løsning). Jeg eksperimenterede med IF, men fandt ikke noget der virkede. Jeg opretter svar, som du bad om.
SELECT t.topic_id, tt.mark_time FROM topics as t LEFT JOIN topics_track as tt ON tt.topic_id=t.topic_id WHERE (tt.user_id=53 OR tt.user_id IS NULL) order by T.topic_id;
Tak for buddet, Arne. Sql'en virker dog ikke, men crasher databasen. Table topics_track er selvfølgelig meget stor, og ved at benytte "user_id IS NULL" medtages alle resultater på samtlige user_id, der endnu ikke forekommer i topics_track - samt resultater på user_id 53. Det er en meget stor mængde data!
Er du vitterlig ikke nogen, der kan løse denne sql udfordring? Det er jo en relativ generel problematik indenfor anvendelse af sql JOINs:
Der skal uddrages alle poster fra tabel A med en tilhørende værdi V fra tabel B, hvor de to tabeller har en id relation. Hos de poster fra A uden sammenfald med B skal returneres NULL i stedet for V.
Det må da være en typisk problemstilling rigtig mange før mig er stødt på?
mysql> mysql> SELECT t.topic_id, tt.mark_time -> FROM topics t JOIN topics_track tt ON t.topic_id=tt.topic_id -> ORDER BY t.topic_id; +----------+-----------+ | topic_id | mark_time | +----------+-----------+ | 1 | Mandag | | 1 | Onsdag | | 2 | Torsdag | | 2 | Tirsdag | +----------+-----------+ 4 rows in set (0.00 sec)
mysql> mysql> SELECT t.topic_id, tt.mark_time -> FROM topics t JOIN topics_track tt ON t.topic_id=tt.topic_id -> WHERE tt.user_id=53 -> ORDER BY t.topic_id; +----------+-----------+ | topic_id | mark_time | +----------+-----------+ | 1 | Onsdag | | 2 | Torsdag | +----------+-----------+ 2 rows in set (0.01 sec)
mysql> mysql> SELECT t.topic_id, tt.mark_time -> FROM topics t LEFT JOIN topics_track tt ON t.topic_id=tt.topic_id -> ORDER BY t.topic_id; +----------+-----------+ | topic_id | mark_time | +----------+-----------+ | 1 | Mandag | | 1 | Onsdag | | 2 | Torsdag | | 2 | Tirsdag | | 3 | NULL | +----------+-----------+ 5 rows in set (0.00 sec)
mysql> mysql> SELECT t.topic_id, tt.mark_time -> FROM topics t LEFT JOIN topics_track tt ON t.topic_id=tt.topic_id -> WHERE tt.user_id=53 ORDER BY t.topic_id; +----------+-----------+ | topic_id | mark_time | +----------+-----------+ | 1 | Onsdag | | 2 | Torsdag | +----------+-----------+ 2 rows in set (0.00 sec)
mysql> mysql> SELECT t.topic_id, tt.mark_time -> FROM topics t LEFT JOIN topics_track tt ON t.topic_id=tt.topic_id -> WHERE (tt.user_id=53 OR tt.user_id IS NULL) -> ORDER BY t.topic_id; +----------+-----------+ | topic_id | mark_time | +----------+-----------+ | 1 | Onsdag | | 2 | Torsdag | | 3 | NULL | +----------+-----------+ 3 rows in set (0.00 sec)
Jeg tror ikke, vi kommer videre. Men jeg synes, du er den, der kommer tættest på et svar, så hvis du vil åbne op for en pointgivning for sidste svar, så godkender jeg det.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.