问题描述:

I have a query as mentioned below:

select a_value as itemname, (

select a_value from attributes where a_name = 'EAN'

) AS EAN

from attributes

where a_name = 'NAME';

Here I want 2 values from same table using different where clauses and I am displaying them in same front end table also. Can I avoid subquery here?

网友答案:

Try this:

SELECT a1.a_value AS itemname, a2.a_value AS EAN 
FROM attributes a1 
LEFT JOIN attributes a2 ON s2.a_name = 'EAN'  
WHERE a_name = 'NAME';

OR

SELECT a_value 
FROM attributes 
WHERE a_name IN ('NAME', 'EAN');

OR

SELECT MAX(IF(a_name = 'NAME', a_value, NULL)) AS itemname, 
       MAX(IF(a_name = 'EAN', a_value, NULL)) AS EAN 
FROM attributes 
WHERE a_name IN ('NAME', 'EAN');
网友答案:

this would return the two values as separate rows :

select a_value as itemname from attributes where a_name in ('EAN', 'NAME')

do you need to have all the results returned as a single row? and do you need completely independent where clauses (rather than just different values)?

网友答案:
SELECT a_name, a_value
FROM attributes
WHERE a_name IN ('EAN', 'NAME');

In your app, fetch two rows, and then pivot it into a single array:

$item = array();
while ($row = fetch()) { -- pseudocode
  $item[ $row["a_name"] ] = $row["a_value"];
}
print_r($item);

You might want to do the operation in SQL, but it looks like this:

SELECT aname.a_value AS itemname, aean.a_value AS EAN
FROM attributes AS aname
JOIN attributes AS aean ON aname.entity = aean.entity
WHERE aname.a_name = 'NAME' AND aean.a_name = 'EAN';

And it gets more complex in the future when you need three attributes. Basically one more self-join for each additional attribute you want. There's a practical limit to how many joins you can do in a single query, so this solution doesn't scale up.

There's also a solution with GROUP BY but it's also hard to write and performs badly.

相关阅读:
Top