CODEWITHSUNDEEP
mysqlsql
0LLiena
0LLiena

Reputation: 135

Can't figure error in a mysql query

$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...

Upvotes: 0

Views: 52

Answers (4)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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.

Upvotes: 0

eduardosasso
eduardosasso

Reputation: 527

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'

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 0

stUrb
stUrb

Reputation: 6822

You know the right syntax for a case construction is:

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

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

Upvotes: 4

Related Questions