Syntax

[jaymc]

Query:

SELECT count(*) as count, vni.id, vni.FROM, unread, vni.message, vni.network, vni.READ, DATE_FORMAT(vni.DATE, '%b %e at %l:%i%p') DATE, vn.number
FROM media_1.virtual_number_inbox vni
INNER JOIN media_1.virtual_numbers vn ON vni.vnID = vn.id
INNER JOIN (SELECT count(*) unread FROM media_1.virtual_number_inbox WHERE userID = '1' AND `READ` = '0' AND `FROM` = vni.FROM) as unreadcount
WHERE vni.userID = '1'
GROUP BY vni.FROM

 

Error Line:

INNER JOIN (SELECT count(*) unread FROM media_1.virtual_number_inbox WHERE userID = '1' AND `READ` = '0' AND `FROM` = vni.FROM) as unreadcount

 

Reason:

It wont allow me to use vni.FROM as it is unable to see it even though it is part of the rest of my query. How can I write this query to achieve what I want which is basically the result of the ERROR LINE included in the rest of my query result

[cags]

Either your using some advanced MySQL technique I don't know about, or FROM is a field in your vni table. This is likely to cause problems because FROM is a keyword in mysql and you have it listed in your code without back ticks (at the start where specifying which fields to SELECT, and again near the end of line 4 and again on line 6).

[jaymc]

Either your using some advanced MySQL technique I don't know about, or FROM is a field in your vni table. This is likely to cause problems because FROM is a keyword in mysql and you have it listed in your code without back ticks (at the start where specifying which fields to SELECT, and again near the end of line 4 and again on line 6).

 

I also tried

 

INNER JOIN (SELECT count(*) unread FROM media_1.virtual_number_inbox WHERE userID = '1' AND `READ` = '0' AND `FROM` = `vni.FROM`) as unreadcount

 

Still it can't ackowledge `vni.FROM`

 

And yes from is a field in my table although with the back ticks it never causes a problem

 

Any more ideas?

[cags]

I believe the correct format would be... `vni`.`FROM`.

 

Have you tried...

 

SELECT count(*) as count, `vni`.`id`, `vni`.`FROM`, unread, `vni`.`message`, `vni`.`network`, `vni`.`READ`, DATE_FORMAT(vni.DATE, '%b %e at %l:%i%p') DATE, `vn`.`number`
FROM media_1.virtual_number_inbox vni
INNER JOIN `media_1`.virtual_numbers vn ON `vni.vnID` = `vn.id`
INNER JOIN (SELECT count(*) unread FROM media_1.virtual_number_inbox WHERE userID = '1' AND `READ` = '0' AND `FROM` = `vni`.`FROM`) as unreadcount
WHERE `vni.userID` = '1'
GROUP BY `vni`.`FROM`

 

I'm not entirely sure if the structure of some of this code is right, for exaxmple...

 

FROM media_1.virtual_number_inbox vni

 

Are they two different tables, if so, should they not have a comma between them? Same applies on the next line.

[jaymc]

It is not a syntax error e.g. not enclosing the FROM field in backticks ``

 

It is saying vni.FROM doesnt exist

 

In other words it cant use it in the sub select query..