CODEWITHSUNDEEP
mysql
Eino T
Eino T

Reputation: 141

MySQL IF THEN in WHERE clause

I have table with a enum column with possible values of 'relative' and 'absolute'. There maybe duplicate rows with the difference being that column.

So normally I would select a row with the 'absolute' value but I need and if caluse to check if there is a 'duplicate' row where the column is 'relative' and then select that row instead (if there is a reletive row, there will always be absolute row too)

pseudo code:

select *
  from table
  where non_unique_col = 123
    and IF (has result row with 'relative'
        then return that row
        else return 'absolute' row)

Upvotes: 3

Views: 171

Answers (4)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

If the row are identical apart from the relative or absolute part and if you require only on row for each non_unique_column value, why not simply using:

select non_unique_col, max(rel_or_abs), extra_data from tbl
group by non_unique_col;

See http://sqlfiddle.com/#!2/b97fc0/2

Upvotes: 0

Carsten Massmann
Carsten Massmann

Reputation: 28196

SELECT * 
FROM table1 
INNER JOIN (
    SELECT non_unique_col nuc, 
           MAX(absolute_relative_col) rec 
    FROM table1 
    GROUP BY non_unique_col 
) AS t ON nuc=non_unique_col AND rec=absolute_relative_col
-- (@Zessx: second condition is still part of the INNER JOIN ...)

EDIT:

But I like the idea with COALESCE (see comment of Strawberry, above!) much better:

SELECT * FROM table1 WHERE id IN ( 
 SELECT COALESCE(b.id,a.id) FROM tablel a 
 LEFT JOIN tbl b ON b.non_unique_col=a.non_unique_col
                AND b.absolute_relative_col='relative'
)

Upvotes: 0

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

You could try this:

SELECT *
FROM `table`
WHERE `non_unique_col` = 123
ORDER BY `absolute_relative_col` DESC
LIMIT 1

This way, if there is exactly one result, no problem the, if there are more, you get the 'relative' one.

EDIT:

As per @Travesty3 suggestion, I'd like to underline that this query is made on the assumption that (non_unique_col + absolute_relative_col) makes a unique_col, which is based on OP statements that

There maybe duplicate rows with the difference being that column

and

if there is a relative row, there will always be absolute row too

EDIT 2:

A more generic solution could be the following:

SELECT *
FROM `table` as t1
JOIN (
  SELECT non_unique_col, absolute_relative_col
  FROM `table`
  WHERE `absolute_relative_col` = 'relative'
) as t2 USING (non_unique_col)
WHERE t2.absolute_relative_col = 'relative' OR (
  t2.absolute_relative_col IS NULL
  AND t1.absolute_relative_col = 'absolute'
)

Upvotes: 2

Sumurai8
Sumurai8

Reputation: 20737

The following (untested) query should return what you want, but as with most queries that use subqueries, I don't think it is very efficient.

SELECT * 
FROM `table` 
WHERE `non_unique_col`=123 
AND ( 
    (`relativity` = 'relative') 
    OR (`relativity` = 'absolute' 
        AND NOT EXISTS (
            SELECT 1 
            FROM `table` 
            WHERE `non_unique_col`=123 
            AND `relativity`='relative'
        )
    )
);

See this documentation.

Edit: If you would want to create a list of this (e.g. without the id), then you would write something like this:

SELECT * 
FROM `table` as a 
WHERE (`relativity` = 'relative') 
OR (
    `relativity` = 'absolute' 
    AND NOT EXISTS (
        SELECT 1 
        FROM `table` as b 
        WHERE a.`non_unique_col`=b.`non_unique_col` 
        AND `relativity`='relative'
    )
);

Upvotes: 0

Related Questions