Mysql multiple tables select

Question

I've got a table, called for example, "node", from which i need to return values for as shown:

SELECT nid FROM node WHERE type = "book"

After i get a list of values let's say:

|**nid**|
|123|
|12451|
|562|
|536|

Then I need to take these values, and check another table, for rows where column 'path' has values as "node/123", "node/12451" (numbers the previous request returned) in one joined request. It all would be easier if collumn 'path' had simple numbers, without the 'node/'. And then also count the number of identical i.e. 'node/123' returned. End result would look like:

nid |  path  | count(path) | count(distinct path) 
123 |node/123|  412        |       123        
562 |node/562|  123        |       56

Works fine if done in multiple separated queries, but that won't do :/

Answer 1

select a.nid from node a join othertable b 
on b.path = concat("node/", a.nid) where type='book'

Answer 2

You can probably do something like the following (nid may require additional conversion to some string type):

SELECT *
FROM OtherTable
JOIN node ON path = CONCAT('node/', nid)
WHERE type = 'book'

Answer 3

THanks for help, basicly, the problem was that i didn't know how to get nid and node/ together, but Concat helped. ENd result looks something like :

    SELECT node.nid, accesslog.path, count(accesslog.hostname), 
count(distinct accesslog.hostname) FROM `node`, 
`accesslog` WHERE node.uid=1 AND node.type='raamat' 
AND accesslog.path = CONCAT('node/', node.nid) GROUP BY node.nid