CODEWITHSUNDEEP
mysqljoin
Moon
Moon

Reputation: 22585

How to query this table?

I have the following two tables.

  1. nodes
  2. attributes

nodes

id  title
1   test
2   test2

attributes

id  node_id  title    value
1   1        featured   1
2   1        age        13
3   2        featured   2

I would like query nodes with attribute title 'featured' along with its all attributes.

I tried to join, but I don't know how to query other attributes at the same time.

Is it possible to make a single query to do this?

Upvotes: 0

Views: 45

Answers (2)

MartinStettner
MartinStettner

Reputation: 29174

You could use a subquery to get the ID's of all nodes with the attribute 'featured'. The outer query would be the JOIN to get the rest of the attributes.

Like:

SELECT n.*, a.* 
FROM nodes n JOIN attributes a ON a.node_id=n.id 
WHERE n.id IN
  (SELECT DISTINCT no.id 
   FROM nodes no JOIN attributes at ON at.node_id=no.id AND at.title='featured')

Upvotes: 1

John Woo
John Woo

Reputation: 263803

I think this is a simple join

SELECT  b.title as NodeTitle, a.title, a.value
FROM    attributes a
        INNER JOIN nodes b
            ON a.node_id = b.id

Upvotes: 1

Related Questions