Putting result of 2 different queries in one result column

[binita2908]

Hi all ,

I have the following tables :

mysql> select * from Product;
+-------+-------+---------+
| maker | model | type |
+-------+-------+---------+
| A | 1232 | PC |
| A | 1233 | PC |
| A | 1276 | Printer |
| A | 1298 | Laptop |
| A | 1401 | Printer |
| A | 1408 | Printer |
| A | 1752 | Laptop |
| B | 1121 | PC |
| B | 1750 | Laptop |
| C | 1321 | Laptop |
| D | 1288 | Printer |
| D | 1433 | Printer |
| E | 1260 | PC |
| E | 1434 | Printer |
| E | 2111 | PC |
| E | 2112 | PC |
+-------+-------+---------+
16 rows in set (0.06 sec)

mysql> select * from PC;
+------+-------+-------+------+------+------+-------+
| code | model | speed | ram | hd | cd | price |
+------+-------+-------+------+------+------+-------+
| 1 | 1232 | 500 | 64 | 5 | 12x | 600 |
| 2 | 1121 | 750 | 128 | 14 | 40x | 850 |
| 3 | 1233 | 500 | 64 | 5 | 12x | 600 |
| 4 | 1121 | 600 | 128 | 14 | 40x | 850 |
| 5 | 1121 | 600 | 128 | 8 | 40x | 850 |
| 6 | 1233 | 750 | 128 | 20 | 50x | 950 |
| 8 | 1232 | 450 | 64 | 8 | 24x | 350 |
| 9 | 1232 | 450 | 32 | 10 | 24x | 350 |
| 10 | 1260 | 500 | 32 | 10 | 12x | 350 |
| 11 | 1233 | 900 | 128 | 40 | 40x | 980 |
| 7 | 1232 | 500 | 32 | 10 | 12x | 400 |
+------+-------+-------+------+------+------+-------+
11 rows in set (0.00 sec)

mysql> select * from Laptop;
+------+-------+-------+------+------+-------+--------+
| code | model | speed | ram | hd | price | screen |
+------+-------+-------+------+------+-------+--------+
| 1 | 1298 | 350 | 32 | 4 | 700 | 11 |
| 2 | 1321 | 500 | 64 | 8 | 970 | 12 |
| 3 | 1750 | 750 | 128 | 12 | 1200 | 14 |
| 4 | 1298 | 600 | 64 | 10 | 1050 | 15 |
| 5 | 1752 | 750 | 128 | 10 | 1150 | 14 |
| 6 | 1298 | 450 | 64 | 10 | 950 | 12 |
+------+-------+-------+------+------+-------+--------+
6 rows in set (0.05 sec)

mysql> select * from Printer;
+------+-------+-------+--------+-------+
| code | model | color | type | price |
+------+-------+-------+--------+-------+
| 1 | 1276 | n | Laser | 400 |
| 2 | 1433 | y | Jet | 270 |
| 3 | 1434 | y | Jet | 290 |
| 4 | 1401 | n | Matrix | 150 |
| 5 | 1408 | n | Matrix | 270 |
| 6 | 1288 | n | Laser | 400 |
+------+-------+-------+--------+-------+
6 rows in set (0.06 sec)

I have to do this :

For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and printer.
For each maker, this table must include "yes" if a maker has products of corresponding type or "no" otherwise.
In the first case (yes), specify in brackets (without spaces) the quantity of available distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).

I tried this way , which gives me partial output :

select maker, (case when type='PC' then 'yes' else 'no' end) as pc, (case when type='Laptop' then 'yes' else 'no' end) as laptop, (case when type='Printe r' then 'yes' else 'no' end) as printer from Product;
+-------+------+--------+---------+
| maker | pc | laptop | printer |
+-------+------+--------+---------+
| A | yes | no | no |
| A | yes | no | no |
| A | no | no | yes |
| A | no | yes | no |
| A | no | no | yes |
| A | no | no | yes |
| A | no | yes | no |
| B | yes | no | no |
| B | no | yes | no |
| C | no | yes | no |
| D | no | no | yes |
| D | no | no | yes |
| E | yes | no | no |
| E | no | no | yes |
| E | yes | no | no |
| E | yes | no | no |
+-------+------+--------+---------+
16 rows in set (0.00 sec)

But expected result is :

maker pc laptop printer
A yes(2) yes(2) yes(3)
B yes(1) yes(1) no
C no yes(1) no
D no no yes(2)
E yes(1) no yes(1)

How do I do it ?

Thanks in advance

 

[TheSQL]

This would work - there's almost certainly a more elegant way to handle it than the one I'm suggesting, but it'll work:

 SELECT p.maker, 
CASE ISNULL(pc.PCCount,0)
    WHEN 0 THEN 'No'
    ELSE 'Yes(' + CAST(pc.PCCount AS varchar(10)) + ')'
END AS PC,
CASE ISNULL(lt.LTCount,0)
    WHEN 0 THEN 'No'
    ELSE 'Yes(' + CAST(lt.LTCount AS varchar(10)) + ')'
END AS Laptop,
CASE ISNULL(pr.PRCount,0)
    WHEN 0 THEN 'No'
    ELSE 'Yes(' + CAST(pr.PRCount AS varchar(10)) + ')'
END AS Printer

FROM Product p
LEFT OUTER JOIN (SELECT maker, count(*) AS PCCount
                 FROM Product
                 WHERE type = N'PC'
                 GROUP BY maker) pc ON p.maker = pc.maker
LEFT OUTER JOIN (SELECT maker, count(*) AS LTCount
                 FROM Product
                 WHERE type = N'Laptop'
                 GROUP BY maker) lt ON p.maker = lt.maker
LEFT OUTER JOIN (SELECT maker, count(*) AS PRCount
                 FROM Product
                 WHERE type = N'Printer'
                 GROUP BY maker) pr ON p.maker = pr.maker

 

[binita2908]

Hi ,

Thanks , I will try out the solution given by you.