CODEWITHSUNDEEP
mysql
ana
ana

Reputation: 653

How to select from multiple tables with no matching columns

I want to query between three tables that have no matching columns .I have used UNION but it replace idDisplayNetwork and idDevice with idCountryin the result.

SELECT idcountry, 
       name 
FROM   country 
UNION 
SELECT iddevice, 
       name 
FROM   device 
UNION 
SELECT name, 
       iddisplaynetwork 
FROM   displaynetwork

I want this kind of output

   { idCountry: '18ec1e07-9c89-4d2c-aefe-a7bd8966fab4',
    Name: 'Russia' },
   { idCountry: '31ceda51-9ce8-4380-bd41-c95f660e2ad4',
    Name: 'Turkey' },
   { idDevice: 'c0180c24-2df5-4082-b050-94dfaf1d5174',
    Name: 'Desktop and Laptops' },
  { idDisplayNetwork: '771434a0-0e6d-4206-90f5-f6abf85690d1',
    Name: 'search network' },

Any help will be appreciated.

Upvotes: 0

Views: 1011

Answers (3)

Daniel W.
Daniel W.

Reputation: 32290

It does not make any sense to append resultsets to each other if they don't share the same columns (kind of data). It's a semantic error, nothing MySQL related.

If you want to connect each table instead of appending the results, use JOIN statement then. This assumes all tables share at least 1 key for referential integrity.

Example

SELECT n.*, d.*, c.* FROM displaynetwork n
JOIN device d
ON n.name = d.name
JOIN country c
ON n.name = c.name

Update:

Try UNION ALL instead of UNION

Update:

Having 2 tables, colors and names, with names(name,randomNumber) and colors(id,color), the following statement is exactly your output:

SELECT id, color FROM colors
UNION ALL
select randomNumber, name FROM names

So for me, it doesnt have to be the same alias or name of a column. But im using MariaDB, don't know if this behaves different.

Upvotes: 0

Aboelseoud
Aboelseoud

Reputation: 555

If those IDs are the same, you should rename your output columns in the query similar to this:

SELECT idcountry AS ID, 
       name 
FROM   country 
UNION 
SELECT iddevice AS ID, 
       name 
FROM   device 
UNION 
SELECT 
       iddisplaynetwork AS ID,
       name 
FROM   displaynetwork

The result that you just posted is impossible to have, but instead you can do something like this:

SELECT idcountry AS ID, 
           name,
           'Country' AS Type
    FROM   country 
    UNION 
    SELECT iddevice AS ID, 
           name,
           'Device' AS Type
    FROM   device 
    UNION 
    SELECT 
           iddisplaynetwork AS ID,
           name,
           'Display Network' AS Type
    FROM   displaynetwork

The output will be like this: 

{ ID: '18ec1e07-9c89-4d2c-aefe-a7bd8966fab4',
    Name: 'Russia', type: 'Country' },
   { ID: '31ceda51-9ce8-4380-bd41-c95f660e2ad4',
    Name: 'Turkey', type: 'Country' },
   { ID: 'c0180c24-2df5-4082-b050-94dfaf1d5174',
    Name: 'Desktop and Laptops', type: 'Device' },
  { ID: '771434a0-0e6d-4206-90f5-f6abf85690d1',
    Name: 'search network', type: 'Display Network' },

Upvotes: 4

Sven Schneider
Sven Schneider

Reputation: 295

UNION statements need to share the same columns to work. As specified the first statements' column names are taken for the other result sets.

Upvotes: 0

Related Questions