Can't figure error in a mysql query

0
votes
$query = "SELECT 
            table1.first_name, 
            table1.id,
            table1.profile_image 
        FROM table1,table2 
        WHERE table1.id = (
                            CASE 
                            WHEN '$id' = table2.id1 
                            THEN 
                                table2.id2 
                            WHEN '$id' = table2.id2 
                            THEN 
                                table2.id1
                            ) 
        AND table2.case='done'";

This query is failing and I can't figure out why... What I want is to obtain a join on ids between table1 and table2. The thing is table2 has two id fields and I don't know if the Id I want is in field id1 or id2. Then join the other id (NOT $id, but the partner from $id in table2) to the id in table1...

4
mysqlsql
If you can't figure it out I'm sure we can't from what you've posted here. Some sample data and results, actual and expected, would help. - user1864610
Failing with what error? - h2ooooooo
@h2ooooooo You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND table2.case='done'' at line 1 - 0LLiena
Seeing as you are taking no data from table2 are you saying $id could exist Table1 but not in id1 or id2 of table2??? - Tony Hopkinson

4 Answers

4
votes

You know the right syntax for a case construction is:

CASE 
  WHEN ... 
     THEN .... 
  WHEN ...
     THEN ....
  ELSE ...
END

Notice the END at the .... end ;)

0
votes

The lack of end in the case is the obvious problem with the query. However, it would be better written as:

SELECT t1.first_name, t1.id, t1.profile_image 
FROM table1 t1 join
     table2 t2
     on ('$id' = table2.id1 and t1.id  = t2.id2) or
        ('$id' = table2.id2 and t1.id  = t2.id1) 
WHERE t2.case = 'done';

Note the use of explicit join syntax, the use of table aliases, and the replacement of case with basic boolean logic.

0
votes
Select first_name, id, profileImage From Table1
inner join (
Select id1 as t2id From Table2 where `case` = 'done' 
union 
Select id2 From Table2 Where `case` = 'done') t2Ids
) On Table1.id = t2Ids.t2id
Where t2Ids.t2Id = $id

Would be another way to do it.

-1
votes

Try this:

SELECT table1.first_name,table1.id,table1.profile_image 
FROM table1,table2 
WHERE (table1.id = table2.id1 or table1.id = table2.id2)
AND table2.case='done'