Using CONCAT for 2 tables

[bdichiara]

Currently I'm using this SQL query to connect 2 tables but now, I need to add a 3rd table in the same manner as the "links" table in the following query.

SELECT * FROM tracker LEFT JOIN links ON tracker.location = CONCAT('link[', links.linkid, ']') ORDER BY tracker.occurred DESC LIMIT 0, 15

My 3rd table is called knowledge and would end up something like:

LEFT JOIN knowledge ON tracker.location = CONCAT('kb[', knowledge.knowledgeid, ']')

How can I do this where the main table is tracker, and the linked tables (that are connected simply to find the name of the page) are the LINKS and KNOWLEDGE tables?

_______________
_brian.

 

[r937]

go ahead and join the third table

(why do those columns have those square brackets?)

what was the question?

r937.com | rudy.ca

 

[bdichiara]

because the location in my database can either be a search, a record from the table LINKS or a record from the table KB. So i distinguish that by:

link[LINKID]
kb[KNOWLEDGEID]
search[KEYWORD in AREA]

This will also be useful when we setup more big projects as separate tables. For example, the next one will be:

hb[HANDBOOKID]

_______________
_brian.

 

[r937]

make sure you do EXPLAINs on your queries, because using CONCAT and comparing keys alphabetically as strings may not use indexes properly

if it were me i wouldn't design links like that

good luck

r937.com | rudy.ca

 

[bdichiara]

I tried:

SELECT * FROM tracker LEFT JOIN links ON tracker.location = CONCAT('link[', links.linkid, ']') LEFT JOIN knowledge ON tracker.location = CONCAT('kb[', knowledge.knowledgeid, ']') ORDER BY tracker.occurred DESC LIMIT 0, 15

but it gives me a list of 15 of the same record.

_______________
_brian.

 

[r937]

are your sure your data is right?

r937.com | rudy.ca

 

[bdichiara]

Ok, I tried:

SELECT tracker.location, links.description AS link_description, knowledge.description AS kb_description FROM tracker LEFT JOIN links ON tracker.location = CONCAT('link[', links.linkid, ']') LEFT JOIN knowledge ON tracker.location = CONCAT('kb[', knowledge.knowledgeid, ']') WHERE tracker.location <> 'home' LIMIT 0,15

ok, i'm stupid....

...

I've been testing this for so long my history has been the same for the past 15 pages... It's working now. Thanks! I feel so dumb...

_______________
_brian.