sql, Union query

Question

Please I have 5 tables :

• cosmeticproducts(idp, productname,#idc)
• domesticproducts(iddp,description,reference,#idc)
• users(idu, u_name)
• commands(idc,#userid,date)
• market(idm, #userid, marketname, location)

I want to show all the records:

u_name|date      |marketname|location|productname|description|reference
ali   |12-06-2012|Aldi      |DE      |           | TABLE     |65416
sab   |12-06-2013|Aldi      |DE      | makeup    |           | 
sir   |12-06-2010|MG        |FR      |           | chairs    |416

Can we do this with union? But I think that for the union we should have the same attributes in the tables with the same number or in my case for example cosmeticproducts table contains less attributes then domesticproducts.

Please I need your help. How we can can do this? Thanks in advance :)

Answer 1

Can we do this with union? No!

This is way more than UNION or UNION ALL can achieve. You are expecting data from different tables to align into single rows. That's NOT what UNION does.

Let's say we have the following information:

users
(idu, u_name)
  1     ali

commands
(idc,#userid,date)
  X      1    12-06-2012

So we create the following query:

      select u_name , cast(null as date) dt
      from users
UNION ALL -- union would produce the exact same result
      select null, date
      from commands

This is what you would get as a result:

U_NAME DT
ali    NULL
NULL   12-06-2012

2 rows!

If however you use JOINS you MIGHT get closer, this query:

SELECT u_name , c.date
FROM users u
INNER JOIN commands c ON u.idu = c.#userid

would produce this result:

U_NAME DATE
ali    12-06-2012

BUT it is much more complex over the remaining tables because it isn't clear in all cases how the join, but also because some of the tables may have multiple rows for a join and all of a sudden you get many more rows than you expected.

Let's now say we also have this data:

market
(idm, #userid, marketname, location)
  Y1      1       ALDI         DE
  Y2      1       MG           FR

So we include this into our query using joins:

SELECT u_name , c.date, m.marketname, m.location
FROM users u
INNER JOIN commands c ON u.idu = c.#userid
INNER JOIN market m ON u.idu = m.#userid

NOW we get this result:

U_NAME DATE        MARKETNAME  LOCATION
ali    12-06-2012  ALDI         DE
ali    12-06-2012  MG           FR

Your requirement simply cannot be satisfied fully without supplying some sample data from each table and an "expected result" that relates to the data you provide.

Answer 2

You can union tables that have different/missing columns by using nulls in the select.

For example:

SELECT a.Column1 AS 'Col1',
       a.Column2 AS 'Col2',
       null AS 'Col3',
       a.Column4 AS 'Col4'
FROM TableA AS a
UNION ALL
SELECT b.Column1 AS 'Col1',
       b.Column2 AS 'Col2',
       b.Column3 AS 'Col3',
       null  AS 'Col4'
FROM TableB AS b

I don't know if this will help with your specific question but should help you with unions.