CODEWITHSUNDEEP
mysqlsql
0LLiena
0LLiena

Reputation: 135

Can't detect error in this query

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

Upvotes: 0

Views: 88

Answers (2)

SebastianH
SebastianH

Reputation: 2182

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';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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'

Upvotes: 1

Related Questions