[SOLVED] Simple if else in query wont work....

[dlate]

Hi, im trying to do a simple query with a case.

 

What im trying to do:

if GroupID > 0 then it should match core_users.GroupID = core_pages_access.GroupID else match core_users.ID with core_pages_access.userid.

 

The problem i have at the moment,

Whenever in the table another row exists with a groupID greater than 0  it will return results.

 

This is what i have so far:

 

SELECT * FROM `core_users`, `core_pages_access` WHERE core_users.Username = 'Admin' AND core_users.Password = 'hash' 
AND
CASE WHEN core_users.GroupID > 0 THEN core_users.GroupID = core_pages_access.GroupID  ELSE core_users.ID = core_pages_access.userID END

 

Im pretty new to if else in mysql so any help is greatly appreciated

[akitchin]

first line of questioning: do you receive an error?  if so, what does SQL have to say about your statement?

 

second, you may need to individually specify the * fields, like so:

 

core_users.*, core_pages_access.*

[dlate]

first line of questioning: do you receive an error?  if so, what does SQL have to say about your statement?

 

second, you may need to individually specify the * fields, like so:

 

core_users.*, core_pages_access.*

 

Nope no error.

The problem is that the case should only work on the row ive selected (Where username & password = ) but instead it checks on all the rows.

 

(if groupID > 0 in the row where password and username = myvalue then dothis else dothis)

[dlate]

Am i clear in what i need? if not please let me know so i can further explain what im trying to do as well as provide screenshots

[akitchin]

rather naively, i might suggest you put parentheses around the CASE statement, as well as around the two username/password stipulations.

 

if you just want to match one of either the userID or the groupID (if it exists), perhaps an OR would be more useful, assuming it doesn't introduce too little discrimination:

 

SELECT * FROM `core_users`, `core_pages_access` WHERE core_users.Username = 'Admin' AND core_users.Password = 'hash' 
AND
(core_users.GroupID = core_pages_access.GroupID OR core_users.ID = core_pages_access.userID)

[dlate]

Thanks for ur replies akitchin, ive used the query u showed me in ur last post and that was exactly what i wanted it to do, so thank you very much for that