IF, CASE, WHEN Statements Returning different rows of a table depending on a certain value in SQL-CodePudding

I want to write a SQL query which returns a part of another table (not a new table).

An If, Case or whatever statement should decide which rows are returned.

The statement should look at a specific value in a column 'comments' (type VARCHAR) of a specific ID which can be 'new' or 'played'.

If the 'comments' is LIKE '%new%' then the query should select ID 1, 2 and 3, else if the 'comments' is LIKE '%played%' then the query should select ID 4

I cannot get this running cause I only find examples which are changing values in a table but not selecting rows.

I hope someone can help me :)

The code could be the following...

IF (SELECT comments FROM table WHERE ID = 2) LIKE 'new'
    SELECT ID, title, comments 
    FROM table 
    WHERE ID = 1 OR ID = 2 OR ID = 3

IF (SELECT comments FROM table WHERE ID = 2) LIKE 'played'
    SELECT ID, title, comments 
    FROM table 
    WHERE ID = 4

Input table1:

ID   title   comments
---------------------
1    title1  sth
2    title2  new
3    title3    
4    title4  sth

Expected Output:

ID   title   comments
---------------------
1    title1  sth
2    title2  new
3    title3

Input table2:

ID   title   comments
---------------------
1    title1  sth
2    title2  played
3    title3    
4    title4  sth

Expected Output:

ID   title   comments
---------------------
4    title4  sth

CodePudding user response：

Id you don't want to use proceddure with its flow control.

you can always switch to dynamic sql

SELECT IF ((SELECT comments FROM table_a WHERE ID = 2) LIKE 'new',
@sql := 'SELECT ID, title, comments FROM table_a WHERE ID = 1 OR ID = 2 OR ID = 3',
IF ((SELECT comments FROM table_a WHERE ID = 2) LIKE 'played', @sql :=
'SELECT ID, title, comments FROM table_a WHERE ID = 4', @sql := 'SELECT SLEEP(0)'));

  PREPARE Stnt FROM @sql;
  EXECUTE Stnt;
  DEALLOCATE PREPARE Stnt;

CodePudding user response：

Simply JOIN the tables:

SELECT t1.ID, t1.title, t1.comments 
FROM table1 t1
  JOIN table2 t2
WHERE ((t1.ID = 1 OR t1.ID = 2 OR t1.ID = 3) AND t2.comments LIKE 'new') OR
       (t1.ID = 4 AND t2.comments LIKE 'played'))
  AND t2.ID = 2