Group by question

I am still new to queries and coldfusion and hope someone could help me. Thank you very much!

Lets say I created two tables:

1st Table called "tState" with primary key "nState_ID", and 2 other columns "sState_Name" and "nCountry_ID"

2nd Table called "tCountry" with primary key "nCountry_ID", and 1 other column "sCountry_Name"

These two tables are joined by the "nCountry_ID" key.

How do I write a query that will output this?

USA
california
new york
washington

CHINA
shanghai
beijing
nanjing

INDIA

AUSTRALIA

In the above example output, 'USA','CHINA','INDIA', 'AUSTRALIA' are found in the tCountry table, and 'california', 'new york', 'washington', 'shanghai', 'beijing', 'nanjing' are found in the tState table. Since INDIA and AUSTRALIA had no states inserted into the tState table, they are listed with no states below them. I know it has to be done with a 'group by' syntax, but I just cannot remember...

I tried this query, but it only listed the countries with States attached to them, and did not list the countries with no states.

<cfquery name="locations" datasource="nonprofit">
SELECT *
FROM tState, tCountry
WHERE tState.nCountry_ID = tCountry.nCountry_ID
ORDER BY sCountry_Name, sState_Name
</cfqueury>

<UL>
<cfoutput query="locations" group="sCountry_Name">
<Li>#sCountry_Name#</li>
<UL>
<cfoutput>
<li>#sState_Name#</li>
</cfoutput>
</UL>

</UL>
</cfoutput>

Thank you so much for your insight!

 

You need to use an outer join

http://www.w3schools.com/Sql/sql_join_left.asp

 

Thanks....I will go do some research on using outer joins.

Will it be possible to give me a hint on how to use an outer join in the above example?

Thanks again

 

Did you read the link? It is very easy to understand and explains exactly how to use an outer join

 

OOps....! Sorry, I did not see the link. Thanks again ..

 

Thanks cfStarlight....it worked, and I am starting to understand joins better now.