CODEWITHSUNDEEP
sqlselectwhere-clause
user1799842
user1799842

Reputation: 15

Accessing results through WHERE

Well after my first question here I came the below code which is correct and tested:

SELECT DISTINCT
id, title, description, expires,
creator_id,executer_id,
oc_opentask.priority_id,
oc_opentask.status_id, priority_name, status_name,
oc_user1.username AS executer_username,
oc_user2.username AS creator_username
FROM oc_opentask
INNER JOIN oc_opentask_priority 
ON oc_opentask.priority_id=oc_opentask_priority.priority_id
INNER JOIN oc_opentask_status 
ON oc_opentask.status_id=oc_opentask_status.status_id
INNER JOIN oc_user AS oc_user1 
ON oc_opentask.executer_id=oc_user1.user_id
INNER JOIN oc_user AS oc_user2 
ON oc_opentask.creator_id=oc_user2.user_id

My next question is: I want to access those executer_username and creator_username with a WHERE attribute to compare it with a value, so what I am trying which is poping mystake is:

SELECT DISTINCT
id, title, description, expires,
creator_id,executer_id,
oc_opentask.priority_id,
oc_opentask.status_id, priority_name, status_name,
oc_user1.username AS executer_username,
oc_user2.username AS creator_username
FROM oc_opentask
INNER JOIN oc_opentask_priority 
ON oc_opentask.priority_id=oc_opentask_priority.priority_id
INNER JOIN oc_opentask_status 
ON oc_opentask.status_id=oc_opentask_status.status_id
INNER JOIN oc_user AS oc_user1 
ON oc_opentask.executer_id=oc_user1.user_id
INNER JOIN oc_user AS oc_user2 
ON oc_opentask.creator_id=oc_user2.user_id
WHERE oc_opentask.creator_username='bill'

bill does exists in db, but my command to access that value is not correct, can any1 help me on this? Thanks

Upvotes: 0

Views: 62

Answers (3)

Griknok
Griknok

Reputation: 386

If this is MySQL, just using HAVING in place of WHERE. Then you'll never have problems regardless of whether you specify the original column name or the alias you've defined. HAVING also lets you use result of aggregate functions, i.e. HAVING MAX(salary) > 40, while WHERE also fails.

Upvotes: 0

Yogendra Singh
Yogendra Singh

Reputation: 34367

You are using wrong table to get the creator name in the where clause. Use oc_user2 table to get the user name(creator name). Also I am just thinking still there may be a case related issue and hence advice to use LOWER function before comparison and as below:

    SELECT DISTINCT
       id, title, description, expires,
       creator_id,executer_id,
       oc_opentask.priority_id,
       oc_opentask.status_id, priority_name, status_name,
       oc_user1.username AS executer_username,
       oc_user2.username AS creator_username
    FROM oc_opentask
    INNER JOIN oc_opentask_priority 
       ON oc_opentask.priority_id=oc_opentask_priority.priority_id
    INNER JOIN oc_opentask_status 
       ON oc_opentask.status_id=oc_opentask_status.status_id
    INNER JOIN oc_user AS oc_user1 
       ON oc_opentask.executer_id=oc_user1.user_id
    INNER JOIN oc_user AS oc_user2 
       ON oc_opentask.creator_id=oc_user2.user_id
    WHERE LOWER(oc_user2.username)=LOWER('bill')

I just user LOWER on right side to make it case independent for any valid name irrespective of the case.

Upvotes: 0

Dave Richardson
Dave Richardson

Reputation: 4995

You have a column alias:

oc_user2.username AS creator_username

and in you where clause you are using the alias. You need to use the actual column name, so change it to: 

where oc_user2.username = 'bill'

Upvotes: 1

Related Questions