Mysql Unknown column on view
Im having a bad time trying to understand how Views, Unions and joins works,
I have this view:
SELECT id, CODE, NAME, type, quantity FROM items UNION ALL SELECT id, CODE, NAME, type, price, quantity FROM inventory_items
The table items doesnt have a column named quantity, but i still need to get that value from inventory_items, if i remove the select on quantity from items i get:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near FROM items
If i leave the select i get:
[Err] 1054 - Unknown column 'quantity' in field list'
What exactly am I trying to accomplish? i want to select all data from those tables using unique columns, i dont want like name1,name2 columns etc.
What is the right way of doing this?
UNION expects the same number of columns in each set of results. If you have a column that will always be empty, you can simply select NULL instead of a real column. Note that the column names are taken from the first SELECT, so you will need to add an alias for your NULL (or just switch the SELECTs around)
SELECT id, CODE, NAME, type, NULL AS price, NULL AS quantity FROM items UNION ALL SELECT id, CODE, NAME, type, price, quantity FROM inventory_items