making custom db queries i sql

[welrachid]

Hi guys
So quick question.
Im trying to make a quick report to see how many of my orders are pickup orders and from that, find out where people live (zip-code-level)


So my sql is this:
SELECT vo.*  FROM `r25vs_virtuemart_orders` vo left join r25vs_virtuemart_userinfos vu on vo.`virtuemart_order_id`=r25vs_virtuemart_userinfos.`virtuemart_order_id` WHERE `order_status` LIKE 'E' AND `virtuemart_shipmentmethod_id` = 2 ORDER BY `virtuemart_order_id` DESC


but i keep getting this error
#1054 - Unknown column 'r25vs_virtuemart_userinfos.virtuemart_order_id' in 'on clause'


which doesnt make sense. so i was wondering if the db tables are set up in a certain way that this cant be done?

thx
wel

[Ghost]

The column you're trying to use does not exist in the table. You should probably be joining #__virtuemart_order_userinfos instead of #__virtuemart_userinfos.

And use alias, change this:

Code Select Expand

r25vs_virtuemart_order_userinfos.`virtuemart_order_id`
to this:

Code Select Expand

vu.`virtuemart_order_id`

[welrachid]

Thx but according to my phpmyadmin that column exists. See image

[Ghost]

It shouldn't but OK. Then just replace table name with alias like shown above.

[welrachid]

SELECT vo.*  FROM `r25vs_virtuemart_orders` vo left join r25vs_virtuemart_order_userinfos vou on vo.`virtuemart_order_id`=vou.`virtuemart_order_id` WHERE `order_status` LIKE 'E' AND `virtuemart_shipmentmethod_id` = 2 ORDER BY `virtuemart_order_id` DESC

sorry i made a mistake of looking at r25vs_virtuemart_userinfos where i should look at r25vs_virtuemart_order_userinfos
sorry about it.
i think i've look at it too much so my brain started giving me false positives