CODEWITHSUNDEEP
mysqlsqljoin
Alexander G.
Alexander G.

Reputation: 332

SQL Query to select multiple rows

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?

Upvotes: 0

Views: 201

Answers (4)

Rick Hoving
Rick Hoving

Reputation: 3575

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

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 181087

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.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29071

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 |

Upvotes: 0

eggyal
eggyal

Reputation: 126055

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'

Upvotes: 1

Related Questions