Query Help!

[dyluck]

HI There

 

I am having trouble witt this query:

$query = "SELECT roster.RID, roster.FIRSTN, roster.LASTN, cities.CITY, provstate.PROVSTATE, countries.COUNTRY FROM roster INNER JOIN cities ON roster.CITY = cities.CTID JOIN countries ON cities.COUNTRY = countries.ABBREV JOIN provstate ON cities.PROVSTATE = provstate.ABBREV ORDER BY roster.RID DESC LIMIT $rows,5";

 

The problem is that some rows cities.PROVSTATE is empty.  The current statement ignores rows where PROVSTATE is empty even though cities.PROVSTATE also has an empty value there. 

 

Please help!

 

Thanks

 

 

[kickstart]

Hi

 

$query = "SELECT roster.RID, roster.FIRSTN, roster.LASTN, cities.CITY, provstate.PROVSTATE, countries.COUNTRY 
FROM roster 
INNER JOIN cities ON roster.CITY = cities.CTID 
JOIN countries ON cities.COUNTRY = countries.ABBREV 
LEFT OUTER JOIN provstate ON cities.PROVSTATE = provstate.ABBREV 
ORDER BY roster.RID DESC LIMIT $rows,5";

 

Basically do an outer join on the provstate table means it doesn't matter if there is no match.

 

All the best

 

Keith

[dyluck]

You Rule! thanks kickstart!

Don't fully understand what inner / outer actually means but it works