How can I get just one result? SQL: CASE Returns two rows

Question

I have two tables for car information’s to identify which type of engine they have. Therefore I need a second table where all the special equipment of the car is listed. Table 1 distinguishes between Battery and No-Battery. With the key of the special equipment out of table2 it can be identified if the "No-Battery" Cars are Hybrids or Combustion Engine Cars. If they have the special Equipment "ABC" Key, they are Hybrid Cars. If not they are Combustion Cars.

Table 1:

> ID    |Car    |Engine      |
> 1     |RED    |BATTERY     |  
> 2     |YELLOW |NO-BATTERY  |
> 3     |BLUE   |NO-BATTERY  |

Table 2:

-ID-|SPECIAL EQUIPMENT
1   |AAA
1   |BBB
1   |CCC
2   |ABC
2   |BBB
2   |CCC
3   |ABC
3   |BBB
3   |CCC

THE SQL Query is:

SELECT DISTINCT    mytable.id, mytable.car, mytable.engine
FROM
    (SELECT table1.id, table1.car, table1.engine, table1.type,
    
    CASE    WHEN table1.type = 'Battery'    THEN 'BEV'
            WHEN table1.type = 'No-Battery'     THEN (CASE   WHEN special Equipment = 'ABC' THEN 'HYBRID' 
                                                      ELSE 'COMBUSTION' END)                                               
    END AS engine
    
    FROM table1
    INNER JOIN table 2 key ON table1.id = table2.id  
    WHERE (cars.id = 2 )  
)t;

The SQL Code Returns for CAR ID 2 --> 2 Columns, one time with engine type Hybrid and one time with engine type combustion.

RESULT (Current):

> ID    |Car    |type        |engine
> 2     |YELLOW |NO-BATTERY  |HYBRID    
> 2     |YELLOW |NO- BATTERY |COMBUSTION

I WANT TO SHOW:

> ID    |Car    |type        |engine
> 2     |YELLOW |NO-BATTERY  |HYBRID

The Result with Combustion is wrong but is displayed... how can i just get one right return?

May its the inner join? https://stackoverflow.com/questions/12526194/mysql-inner-join-select-only-one-row-from-second-table something in this direction? — JulianBPL, Dec 11 '20 at 09:27

score 2 · Accepted Answer · answered Dec 11 '20 at 14:00

I think you just want exists:

select t1.*,
       (case when exists (select 1
                          from table2 t2
                          where t2.id = t1.id and t2.special = 'ABC'
                         )
             then 'Hybrid' else 'Combustion'
        end) as engine
from table1 t1;

score 0 · Answer 2 · answered Dec 11 '20 at 06:53

I think the issue is that you are asking for

SELECT DISTINCT    mytable.id, mytable.car, mytable.engine

When you only want to show distinct mytable.car

In the results there are two different mytable.engine show it is showing you both as they are distinct.

SELECT DISTINCT    mytable.car

I do not know much about JQL

How can I get just one result? SQL: CASE Returns two rows

2 Answers2