Case returns more than one value with join

问题:

I have a problem when I'm using case statement with a join.

I have two tables. Tbl_a:

and Tbl_b:

I'm running the following query:

SELECT
    tbl_a.id,
    (
        CASE
           WHEN tbl_b.param_type = 'Ignition' Then param_value
           WHEN tbl_b.param_type = 'Turn' Then param_value
           WHEN tbl_b.param_type = 'Speed' Then param_value
           WHEN tbl_b.param_type = 'Break' Then param_value 
        END 
    ) as value
FROM 
    public.tbl_a 
    JOIN public.tbl_b on tbl_b.id = tbl_a.id

I want to get for each id in tbl_a the first match from tbl_b. If there is an id in tbl_b that his paramtype is 'Ignition' then I want to get his value. If there isn't, then I want to get the value of the paramtype 'Turn' and so on. But when I run the query I get all possible matches:

I understand why I get those results, but I don't know how to get the results I want. Any help will be appreciated, thanks!

回答1:

I think this approach does what you want:

SELECT a.id,
       COALESCE( MAX(CASE WHEN b.param_type = 'Ignition' THEN a.param_value END), 
                 MAX(CASE WHEN b.param_type = 'Turn' THEN a.param_value END),
                 MAX(CASE WHEN b.param_type = 'Speed' THEN a.param_value END),
                 MAX(CASE WHEN b.param_type = 'Break' THEN a.param_value END)
               ) as value
FROM public.tbl_a a JOIN
     public.tbl_b
     ON b.id = a.id
GROUP BY a.id;

回答2:

You could GROUP and pull the first item...

SELECT 
    id, 
    FIRST(value) 
FROM 
    public.tbl_a AS a 
    INNER JOIN 
    public.tbl_b AS b 
        ON  a.id = b.id 
GROUP BY 
    a.id 

The problem you'll have here, though, is that there is no guarantee that your values are always going to be in the correct order unless there's some kind of ordering mechanism, such as a time or incremental ID.

NOTE:

I've written this for standard T-SQL, so if Amazon RedShift doesn't quite follow the standard, you may need to look up the syntax.