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.

How can count(distinct path) be different from 1 for a given nid/path pair? — Romain
– Romain, Commented Dec 3, 2009 at 14:26

Trevor · Accepted Answer · 2009-12-03 14:23:51Z

1

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

answered Dec 3, 2009 at 14:23

Trevor

6,6995 gold badges40 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Romain · Accepted Answer · 2009-12-03 14:24:09Z

0

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'

answered Dec 3, 2009 at 14:24

Romain

12.9k3 gold badges43 silver badges54 bronze badges

Comments

Shef · Accepted Answer · 2011-11-25 12:22:37Z

0

Thank you all for your help. Basically, 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

edited Nov 25, 2011 at 12:22

Shef

45.6k15 gold badges82 silver badges91 bronze badges

answered Dec 3, 2009 at 14:43

djeux

191 silver badge3 bronze badges

Collectives™ on Stack Overflow

Mysql multiple tables select

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related