Sorting data from 8 tables based on 1 common column in all of them

Question

0.00/5 (No votes)

See more:

Hello good people,

I have a database with 8 tables. Each table has a column called "Country", and then some other columns like Population or GDP or whatever (depending on the table).

Now the problem is, not all tables have exactly the same list and order or countries. Some have less, some more.

I need to put together all this data in one table. I need a query that will return data based on the column "Country" across all tables. I can't find a way to get unions to do that.

Any suggestions? Have I been able to explain myself?

Thanks!

EDIT: Here's a picture of 4 tables to explain my point
<a href="https://drive.google.com/file/d/1F0YxG6c6rcLE3d9GIFnmXcjfYl3THwTm/view?usp=sharing"">EXAMPLE.png - Google Drive

What I have tried:

I've tried a select across all tables, but it's not working.

Posted 16-Nov-17 23:39pm

Member 13526494

Updated 17-Nov-17 1:42am

CHill60

v4

Add a Solution

Comments

CHill60 17-Nov-17 5:42am

Post the select you tried and explain what "not working" means

Also some sample data and what you expect would be helpful

Member 13526494 17-Nov-17 5:52am

Hey there,

Thanks for your comment. Unfortunately, I didn't save the SELECT query I tried. But it was obviously wrong. I've added a picture of 4 tables. Please look at it and see if it makes sense. Thanks!

OriginalGriff 17-Nov-17 6:00am

No, regenerate the query, test it, and tell us what it does that is wrong: we aren't here to do your homework for you! :laugh:

CHill60 17-Nov-17 6:01am

Put the table structure and some sample data into your question. Google Drive is a blocked site for me

Member 13526494 17-Nov-17 6:02am

Well if you really wanna look at this failure, here you go. I don't know why it doesn't return anything at all.

SELECT Group, GDP, Exportsgdp, gdpcapita, Literacy, Manfgdp, Populationdensity, Tax
FROM CONT, GDPMAIN, EXPORTS, GDPC, LITERACYRATE, MANGDP, POPULATION, TAXRATE
WHERE GDPMAIN.[Country]='Austria' And GDPC.Country='Austria' And GDPMAIN.[Country]='Austria' And LITERACYRATE.Country='Austria' And MANGDP.Country='Austria' And POPULATION.Country='Austria';

Member 13526494 17-Nov-17 6:08am

It'd be really better if you saw the screenshot instead of text.

https://imgur.com/a/iyqYe

Can you see it now?

Laxmidhar tatwa technologies 17-Nov-17 7:34am

use left outer join

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Laxmidhar tatwa technologies · Answer 1 · 2017-11-17T01:42:00

SELECT CONT.GDPMAIN.Group, EXPORTS.GDP, LITERACYRATE.Exportsgdp, MANGDP.gdpcapita, LITERACYRATE.Literacy, MANGDP.Manfgdp, POPULATION.Populationdensity, TAXRATE.Tax
FROM CONT
LEFT OUTER JOIN GDPMAIN on CONT.Country = GDPMAIN.Country
LEFT OUTER JOIN EXPORTS on CONT.Country = EXPORTS.Country
LEFT OUTER JOIN GDPC on CONT.Country = GDPC.Country
LEFT OUTER JOIN LITERACYRATE on CONT.Country = LITERACYRATE.Country
LEFT OUTER JOIN MANGDP on CONT.Country = MANGDP.Country
LEFT OUTER JOIN POPULATION on CONT.Country = POPULATION.Country
LEFT OUTER JOIN TAXRATE on CONT.Country = TAXRATE.Country
WHERE CONT.Country = 'Austria'

Sorting data from 8 tables based on 1 common column in all of them

1 solution

Solution 1

Add your solution here

Preview 0

Sorting data from 8 tables based on 1 common column in all of them

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us