I have two tables
taxonomy_index
-nid
-tid
and
url_alias
-source
-alias
I need to find url_alias.alias record which have source 'taxonomy/term/' + taxonomy_index.tid and I have only taxonomy_index.nid
Add a comment
|
3 Answers
SELECT url_alias.alias
FROM url_alias, taxonomy_index
WHERE url_alias.source = CONCATENATE('taxonomy/term/', taxonomy_index.tid)
AND taxonomy_index.nid = {given_nid}
Comments
Either use a subquery or a join. With a subquery:
SELECT alias
FROM url_alias
WHERE source =
(SELECT CONCAT('taxonomy/term/',tid)
FROM taxonomy_index
WHERE nid = ?
)
1 Comment
DarkMantis
Just be aware that subqueries alone are very slow, and especially when you are using CONCAT. Just a heads up
This query will do that for you although there may be more efficient way to do this ;)
SELECT
T.nid
,U.*
FROM
url_alias AS U
INNER JOIN (
SELECT
nid
,CONCAT('taxonomy/term/', tid) AS `alias`
FROM
taxonomy_index ) AS T
ON
U.alias = T.alias
