Can't detect error in this query

0
votes

Is this query well done? I tried it but it failed somehow... The ids are referred as unique so the correlation between the tables should work but it doesn't...

$query = "SELECT table1.id,table1.field1,table1.field2,table1.field3 FROM table1,table2 WHERE table1.id='(CASE WHEN '5' = table2.id1 THEN table2.id2 WHEN '5' = table2.id2 THEN table2.id1)' WHERE table2.field6='done'";

ERROR:

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 '1' = relationships.id1 THEN relationships.id2 WHEN '1' = relationships.id2 THEN ' at line 1

2
mysqlsql
Are you getting an error or just unexpected results? Please be more descriptive in your question. - Ahrotahntee
error in 2 where clause? use and for multiple conditions - naveen goyal
In addition to the two WHERE clauses that another commenter mentioned, you have quotes around your CASE expression, which is wrong. - Tim Burch
I don't see any join between table1 and table2... - Vamsi Krishna

2 Answers

1
votes

This is your query, formatted for a human being:

SELECT table1.id, table1.field1, table1.field2, table1.field3
FROM table1, table2
WHERE table1.id='(CASE WHEN '5' = table2.id1 THEN table2.id2 WHEN '5' = table2.id2 THEN table2.id1)'
WHERE table2.field6='done'

This query is broken in so many ways, that I don't know where to start. But, you can start by understanding that case statements shouldn't be surrounded by single quotes. And that SQL queries don't have two where clauses. And you should learn proper join syntax.

This might be what you want:

SELECT t1.id, t1.field1, t1.field2, t1.field3
FROM table1 t1 join
     table2 t2
     on t1.id = 5 and 5 in (t2.id1, t2.id2)
WHERE table2.field6 = 'done'
0
votes

Gordon is right. Maybe this is closer to a working statement:

SELECT table1.id,table1.field1,table1.field2,table1.field3
FROM table1,table2
WHERE table1.id= (CASE WHEN '5' = table2.id1 THEN table2.id2 WHEN '5' = table2.id2 THEN table2.id1)
AND table2.field6='done';