CODEWITHSUNDEEP
mysql
user1820110
user1820110

Reputation: 57

mysql query to select rows without any relation

hi i have table a with column

id desc rate  rates_id

1   NYC  0.1    0001
2   NYC  0.25   0003
3   KYC  0.12   0001
4   KYC  0.15   0003

i need to display rows in this manner

desc  rate where rates_id = 0001  rate where rates_id=0003
NYC    0.1                        0.25
KYC    0.12                       0.15

i wroe this query but not working

SELECT desc,
       rate,
       (SELECT rate
        FROM   tablea p
        WHERE  rates_id LIKE '0003'
               AND p.desc = t.desc) AS rate2
FROM   tablea t
WHERE  rates_id LIKE '0001';

any suggesion ???

Upvotes: 0

Views: 151

Answers (2)

xlecoustillier
xlecoustillier

Reputation: 16361

Try:

SELECT `desc`,
    MAX(CASE WHEN rates_id = '0001' THEN rate ELSE NULL END) AS `rate where rates_id = 0001`,
    MAX(CASE WHEN rates_id = '0003' THEN rate ELSE NULL END) AS `rate where rates_id = 0003`
FROM table a
GROUP BY `desc`

If you want to display only rows where there's at least one value, you can append the following to your query:

HAVING MAX(CASE WHEN rates_id = '0001' THEN rate ELSE NULL END) IS NOT NULL
    OR MAX(CASE WHEN rates_id = '0003' THEN rate ELSE NULL END) IS NOT NULL

or filter directly on the relevant rates_id:

SELECT `desc`,
    MAX(CASE WHEN rates_id = '0001' THEN rate ELSE NULL END) AS `rate where rates_id = 0001`,
    MAX(CASE WHEN rates_id = '0003' THEN rate ELSE NULL END) AS `rate where rates_id = 0003`
FROM table a
WHERE rates_id IN ('0001', '0003')
GROUP BY `desc`

Upvotes: 5

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

Your problem is that your main query filters out the results for the sub query.

You can either select both rates as subselects.

SELECT distinct `desc`,
   (SELECT rate
    FROM   tablea p
    WHERE  rates_id LIKE '0001'
           AND p.`desc` = t.`desc`) AS rate1,
   (SELECT rate
    FROM   tablea p
    WHERE  rates_id LIKE '0003'
           AND p.`desc` = t.`desc`) AS rate2
FROM   tablea t;

heres the sqlfiddle

or you use joins to subeselect the rates.

SELECT DISTINCT t.`desc`, rate1.rate, rate2.rate
FROM tablea t
LEFT JOIN tablea rate1 ON t.`desc` = rate1.`desc` AND rate1.rates_id = '0001'
LEFT JOIN tablea rate2 ON t.`desc` = rate2.`desc` AND rate2.rates_id = '0003'

sqlfiddle for the joins

I would recommend you use joins, but you may want to use EXPLAIN for both queries and see what has the lower cost in your environment.

Upvotes: 1

Related Questions