SQL Query to select multiple rows

Question

i have the following tables:

projects: id, name, language
          1 |test | php
          2 |test | java
          3 |hello| php


attrs:  id, name,      value,   tb1_id
        1  | status    | finish | 1
        2  | reference | 2      | 1

I need a query to select all projects, which have a attribute "reference" to another project and "status" finish.

example output:

id, name      
1 |test

Could you help me?

BTW, the id column in your attrs table appears to serve no purpose — Strawberry
– Strawberry, Commented Dec 6, 2013 at 9:06
@Strawberry A unique primary key is if nothing else often useful for more complex queries, such as removing duplicate attributes. — Joachim Isaksson
– Joachim Isaksson, Commented Dec 6, 2013 at 9:15
@JoachimIsaksson A compound natural PK can be formed on the entity (tb1_id) and attribute (name), in which case the scenario you describe would not occur. In any case, where a surrogate key is used, best practice requires a natural UNIQUE (compound) key to be formed alongside. — Strawberry
– Strawberry, Commented Dec 6, 2013 at 9:42
@Strawberry I see what you're saying, and you have a good point in that a natural key is very (more) useful in that case. I guess I'm just a surrogate key type person, I've almost always lived to regret skimping on them :) — Joachim Isaksson
– Joachim Isaksson, Commented Dec 6, 2013 at 9:57

eggyal · Accepted Answer · 2013-12-06 09:04:35Z

1

You can either:

group the joined tables, filtering such groups with suitable aggregate functions within the HAVING clause:

SELECT   projects.id, projects.name
FROM     projects
    JOIN attrs ON attrs.tb1_id = projects.id
GROUP BY projects.id
HAVING   SUM(attrs.name='reference')
     AND SUM(attrs.name='status' AND attrs.value='finish')

or else join attrs multiple times:

SELECT   p.id, p.name
FROM     projects AS p
    JOIN attrs    AS r ON  r.tb1_id = p.id
                       AND r.name   = 'reference'
    JOIN attrs    AS s ON  s.tb1_id = p.id
                       AND s.name   = 'status'
WHERE    s.value = 'finish'

answered Dec 6, 2013 at 9:04

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

2 Comments

Alexander G. Over a year ago

your 1. query works, can you edit so, so i get also the id of the references project?

eggyal Over a year ago

@AlexanderG.: If you want that, you're probably better off using the second query (and adding r.value to the select list).

Rick Hoving · Accepted Answer · 2013-12-06 09:27:04Z

1

SELECT P.id, P.name
FROM projects P inner join attrs A on P.id = a.tb1_id
WHERE (A.name = 'status' AND value = 'finish')
UNION
SELECT P.id, P.name
FROM attrs A INNER JOIN projects P ON A.value = P.id
WHERE A.name='reference'

See the result in this fiddle

edited Dec 6, 2013 at 9:27

answered Dec 6, 2013 at 8:59

Rick Hoving

3,5853 gold badges31 silver badges50 bronze badges

5 Comments

Prabhakar Manthena Over a year ago

I think it should be A.value='finish'

Rick Hoving Over a year ago

Because value is not an ambiguous column (e.g. it only occurs in the attrs table) you can put value='finish' instead of A.value='finish'. But it works both ways

Alexander G. Over a year ago

The problem is, it's possible that other attributes, e.g. name="created" also can the value ="finish". It's possible to say only name="status" should be value="finish"?

Rick Hoving Over a year ago

I updated my answer, I misread your question. The query now results in all the projects which have an attribute with a name status and the value finish

Joachim Isaksson Over a year ago

You're not taking the reference attribute into account.

Saharsh Shah · Accepted Answer · 2013-12-06 09:10:20Z

0

Try this:

SELECT p.id, p.name
FROM projects p 
INNER JOIN (SELECT tb1_id, MAX(IF(a.name = 'status', a.value, '')) attrStatus, MAX(IF(a.name = 'reference', a.value, '')) attrReference
                FROM attrs a GROUP BY tb1_id) A ON p.id = A.tb1_id AND A.attrStatus = 'finish' 
WHERE EXISTS (SELECT 1 FROM projects p1 WHERE A.attrReference = p1.id)

Check the SQL FIDDLE DEMO

OUTPUT

| ID | NAME |
|----|------|
|  1 | test |

edited Dec 6, 2013 at 9:10

answered Dec 6, 2013 at 9:03

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Comments

Joachim Isaksson · Accepted Answer · 2013-12-06 09:22:12Z

0

You'll need to join attrs twice, once for each attribute;

SELECT p.*, ref_attr.value reference FROM projects p
JOIN attrs ref_attr  ON ref_attr.tb1_id = p.id
                    AND ref_attr.name = 'reference'
JOIN attrs stat_attr ON stat_attr.tb1_id = p.id
                    AND stat_attr.name = 'status'
                    AND stat_attr.value = 'finish'

An SQLfiddle to test with.

edited Dec 6, 2013 at 9:22

answered Dec 6, 2013 at 9:07

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Collectives™ on Stack Overflow

SQL Query to select multiple rows

4 Answers 4

2 Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related