SQL query design not returning expected results

Question

I have two tables in a SQL db named tbl_country and tbl_seaport

I am trying to create a query that returns all possible combinations of both tables using a join on field CountryCode.

tbl_country

Fields: CountryID, Country, CountryCode


tbl_seaport

Fields: PortID, PortName, RoutingCode, CountryCode

I started with the below but I can only get it to return 250 rows which is the actual table row count. I thought it would return 62500 (250 x 250) rows of data.

SELECT s.Country, m.Country 
FROM tbl_country AS s
LEFT JOIN tbl_country AS m
ON s.CountryID = m.CountryID

Any ideas on how to achieve this?

Answer 1

Try this

SELECT s.Country, m.Country FROM tbl_country AS s cross JOIN tbl_country AS m 

EDIT: with regards to your comment, you can use below query to join 3rd table.make sure to remove any duplication column names.

with cte
as
(
    SELECT s.Country as sCountry , m.Country as mCountry FROM tbl_country AS s cross JOIN tbl_country AS m
)

select * from cte cross join tbl_seaport

but, please reconsider your design

Answer 2

As mentioned already it is a CROSS JOIN that joins two tables unconditionally such as to build a cartesian product of the tables.

However, now that you clarified your request, it becomes clear that you do want a condition. Only the condition is not some column must match another as it is usually the case when joining tables, but the opposite: A record must not match itself.

Then it is actually all seaport combinations you seek. The seaports' countries are implicit, i.e. a seaport belongs to a country, so you can use a normal join to get it.

select 
  port1.portname as port1_name,
  country1.country as port1_country,
  port2.portname as port2_name,
  country2.country as port2_country  
from tbl_seaport port1
join tbl_seaport port2 on port2.portid <> port1.portid
join tbl_country country1 on country1.countryid = port1.countryid
join tbl_country country2 on country2.countryid = port2.countryid;

The above still gives you combinations twice, e.g. PORT_A-PORT_B and PORT_B-PORT_A. If you want to omit this, then Change the ON clause to port2.portid > port1.portid.