Mykhailo Marufenko Mykhailo Marufenko - 2 months ago 18
MySQL Question

SQL case few row analysis

I need some help with task below:
Table_A contains columns (ID, Name);
Table_B contains columns (ID, Category).

There are two possible category (Category_A, Category_B).

I'd like to get result table in format below:

|Name|Category_A|Category_B|

|Lilu| NO | YES |

But Table_B can contain two row for the same ID that we be meant e.g. "Lilu" belongs for both Catogory_A and Category_B as well. But in result I need one entry:

|Name|Category_A|Category_B|

|Lilu| YES | YES |

I prepare some query, but I miss grouping...

select A.ID, --B.Category
case when B.category = 'Category_A' then 'yes' else 'no' end Category_A,
case when B.category = 'Category_B' then 'yes' else 'no' end Category_B
from system.A_table A, B_table B;


How can I implement it?

Answer

Try to use

select A.name, 
case when exist (Select b.Id from B_table b where b.id=A.id and b.category = 'Category_A') then 'yes' else 'no' end Category_A,
case when exist (Select b.Id from B_table b where b.id=A.id and b.category = 'Category_B') then 'yes' else 'no' end Category_B
from system.A_table A