SQL Experts?


#1

trying to come up with a query and its doing my head in!

I have two tables.

Table A is full of rows of facts about a country including a column for capital city and one for total population.
Table B has list of cities their corresponding country and population of said city.

I need to do a query to to return a table basically from TABLE A showing the percentage population of that capital city against the total. Including Country Name, Capital City Name and % Population

This is in PostgreSQL by the way.

I was thinking along the lines of

SELECT CountryName.Table A, CityName.Table A, (Population.TableB/Population.TableA) AS percentage
From TableA, Table B;

Well it doesnt work! I guess I have to somehow make a link between the two cities somehow. A where statement? Arrrg.

Anyone any good with this? rumour has it @n0tch might be?

ta


#2

If it works anything like ssms, i use the editor. So you can bring up what tables you want, do the joins, select what you want to see and then run it. It makes the query for you.


#3

It uses PGAdmin4. Never thought to have a look at athat, as just working with the SHELL. But kinda need to work it out as well. Its doing my head in!


#4

What is the common field between the two tables so you can link them together with a Where Clause?


#5

What Mic says, need to find the common filed and link the tables with it.


#6

@MicJules

So Table A(Name, Capital,Population) – Name bing Name of a Country like France, and Capital Paris.
Table B(Name, Country, Population) – Name being a City Name i.e. Paris, And Country Well France,

So TableA.Capital can = Table B.name ??


#7

List the two tables on here if you can.


#8

It can, as long as the contains match.


#9

nailed it

Thanks @MicJules thanks @Hammy

Didnt have the where statement right.