A bit stuck with a MySQL query..

toastyman

toastyman

toastyman

Associate
Joined
30 Dec 2005
Posts
415
Evening all,

I'm having a problem with a query I've written.. not sure what the problem is as I get this error..
#1054 - Unknown column 'replyid' in 'where clause'
Click to expand...

I've underlined the problematic areas...

Code: 
 SELECT `helpdesk_tickets`.`id` , `helpdesk_tickets`.`status` , `helpdesk_tickets`.`subject` , `users`.`first_name` , `users`.`last_name` , `users`.`id` AS `uid` ,

(SELECT `helpdesk_tickets_replies`.`date`
FROM `helpdesk_tickets_replies`
WHERE `helpdesk_tickets_replies`.`tid` = `helpdesk_tickets`.`id`
ORDER BY `helpdesk_tickets_replies`.`date` DESC
LIMIT 1
) AS `replydate` , 

[U](SELECT `helpdesk_tickets_replies`.`id`
FROM `helpdesk_tickets_replies`
WHERE `helpdesk_tickets_replies`.`tid` = `helpdesk_tickets`.`id`
ORDER BY `helpdesk_tickets_replies`.`date` DESC
LIMIT 1
) AS `replyid`[/U]

FROM `helpdesk_tickets`
INNER JOIN `users` ON `helpdesk_tickets`.`uid` = `users`.`id`
WHERE (
`helpdesk_tickets`.`status` = '0'
OR `helpdesk_tickets`.`status` = '2'
)
AND NOT
EXISTS (

SELECT `helpdesk_noti`.`id`
FROM `helpdesk_noti`
WHERE `helpdesk_noti`.`type` = 't'
AND `helpdesk_noti`.`uid` = `users`.`id`
[U]AND `helpdesk_noti`.`nid` = `replyid`[/U]
)
ORDER BY `replydate` ASC

If I take out the problematic where clause replyid does return a value, so I guess it's something todo with the subquery being unable to access replyid..

Any ideas would be appreciated...
 
What happens if you do something like:
FROM `helpdesk_tickets` HT

Then change:
AND `helpdesk_noti`.`nid` = `replyid`
to
AND `helpdesk_noti`.`nid` = HT.`replyid`
 
Sadly that has the same problem..

Code: 
 SELECT HT.`id` , HT.`status` , HT.`subject` , `users`.`first_name` , `users`.`last_name` , `users`.`id` AS `uid` , (

SELECT `helpdesk_tickets_replies`.`date`
FROM `helpdesk_tickets_replies`
WHERE `helpdesk_tickets_replies`.`tid` = HT.`id`
ORDER BY `helpdesk_tickets_replies`.`date` DESC
LIMIT 1
) AS `replydate` , (

SELECT `helpdesk_tickets_replies`.`id`
FROM `helpdesk_tickets_replies`
WHERE `helpdesk_tickets_replies`.`tid` = HT.`id`
ORDER BY `helpdesk_tickets_replies`.`date` DESC
LIMIT 1
) AS `replyid`
FROM `helpdesk_tickets` HT
INNER JOIN `users` ON HT.`uid` = `users`.`id`
WHERE (
HT.`status` = '0'
OR HT.`status` = '2'
)
AND NOT
EXISTS (

SELECT `helpdesk_noti`.`id`
FROM `helpdesk_noti`
WHERE `helpdesk_noti`.`type` = 't'
AND `helpdesk_noti`.`uid` = `users`.`id`
AND `helpdesk_noti`.`nid` = HT.`replyid`
)
ORDER BY `replydate` ASC

MySQL said: Documentation
#1054 - Unknown column 'HT.replyid' in 'where clause'
 
Well replyid is a made up column so you wont be able to reference it.
Code: 
(
SELECT `helpdesk_tickets_replies`.`id`
FROM `helpdesk_tickets_replies`
WHERE `helpdesk_tickets_replies`.`tid` = HT.`id`
ORDER BY `helpdesk_tickets_replies`.`date` DESC
LIMIT 1
) AS `replyid`

so you might be able to do

Code: 
AND NOT
EXISTS (

SELECT `helpdesk_noti`.`id`
FROM `helpdesk_noti`
WHERE `helpdesk_noti`.`type` = 't'
AND `helpdesk_noti`.`uid` = `users`.`id`
AND `helpdesk_noti`.`nid` = (SELECT `helpdesk_tickets_replies`.`id`
                                        FROM `helpdesk_tickets_replies`
                                        WHERE `helpdesk_tickets_replies`.`tid` = HT.`id`
                                        ORDER BY `helpdesk_tickets_replies`.`date` DESC
                                        LIMIT 1
)
ORDER BY `replydate` ASC
but seriously, that is one messed up select! :)
 
Try
Code: 
 SELECT `helpdesk_tickets`.`id` , `helpdesk_tickets`.`status` 
       , `helpdesk_tickets`.`subject` , `users`.`first_name` , `users`.`last_name` 
       , `users`.`id` AS `uid` ,

a.`replydate`, a.`replyid`

From `helpdesk_tickets`
Inner join (SELECT `helpdesk_tickets_replies`.`tid`, `helpdesk_tickets_replies`.`id` as replyid, `helpdesk_tickets_replies`.`date` as replydate
            FROM `helpdesk_tickets_replies`
            WHERE `helpdesk_tickets_replies`.`tid` = `helpdesk_tickets`.`id`
            ORDER BY `helpdesk_tickets_replies`.`date` DESC
            LIMIT 1) as a
ON a.`tid` = `helpdesk_tickets`.`id`
INNER JOIN `users` 
ON `helpdesk_tickets`.`uid` = `users`.`id`
WHERE (`helpdesk_tickets`.`status` = '0'
        OR `helpdesk_tickets`.`status` = '2')

AND NOT
EXISTS (SELECT `helpdesk_noti`.`id`
         FROM `helpdesk_noti`
         WHERE `helpdesk_noti`.`type` = 't'
         AND `helpdesk_noti`.`uid` = `users`.`id`
         AND `helpdesk_noti`.`nid` = a.`replyid`)
ORDER BY a.`replydate` ASC
 
Yeah it is quite a messy query.. you can probably tell I'm not the best at SQL... I only really know the basic syntax!

I like the concept or using inner joins.. eliminating those 2 sub queries which are basically doing the same thing seems like a good idea.

I also read that it's better to use a left outer join instead of not exists..

Code: 
SELECT helpdesk_tickets.id
     , helpdesk_tickets.status
     , helpdesk_tickets.subject
     , users.first_name
     , users.last_name
     , users.id AS uid
     , helpdesk_tickets_replies.date AS replydate
     , helpdesk_tickets_replies.id   AS replyid
  FROM helpdesk_tickets
INNER
  JOIN ( SELECT tid
              , MAX(`date`) AS last_reply_date
           FROM helpdesk_tickets_replies
         GROUP
             BY tid ) AS replies
    ON replies.tid = helpdesk_tickets.id
INNER
  JOIN helpdesk_tickets_replies
    ON helpdesk_tickets_replies.tid = helpdesk_tickets.id
   AND helpdesk_tickets_replies.date = replies.last_reply_date
INNER 
  JOIN users 
    ON users.id = helpdesk_tickets.uid
LEFT OUTER
  JOIN helpdesk_noti
    ON helpdesk_noti.type = 't'
   AND helpdesk_noti.uid = users.id
   AND helpdesk_noti.nid = helpdesk_tickets_replies.id
 WHERE helpdesk_tickets.status IN ( 0 , 2 )
   AND helpdesk_noti.nid IS NULL
ORDER 
    BY helpdesk_tickets_replies.date ASC
 
You must log in or register to reply here.
Back
Top Bottom