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?

Answers


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

Need Your Help

nested loop with jquery image slider

php jquery arrays loops slider

I'm using a jquery image slider that, display images 3 by 3. All the images are coming from database. This is my code:

Infrequent Firefox-only glitch: textarea shifted

javascript jquery html css firefox

I got this strange infrequent and firefox-only glitch on my web site.