Re: LEFT joins

Han wrote:

Hi, I'm having a problem with some joins. My code is : -- SELECT b.fldName, b.fldEmail, b.fldCountryCode, b.fldMobile, a.fldTime as Time, c.fldUsername FROM tblSubscribersChoices a INNER JOIN tblUser c on a.fldChoice=c.fldClientID LEFT JOIN tblSubscribers b on a.fldClientID = b.fldID ORDER BY c.fldUsername ASC Now this works fine. But, I'm trying to tally the country code with it's actual name (which is held in tc_countries). So I thought if I add another LEFT JOIN at the end of the query : -- SELECT b.fldName, b.fldEmail, b.fldCountryCode, d.fldCode as FCode, b.fldMobile, a.fldTime as Time, c.fldUsername FROM tblSubscribersChoices a INNER JOIN tblUser c on a.fldChoice=c.fldClientID LEFT JOIN tblSubscribers b on a.fldClientID = b.fldID LEFT JOIN tc_countries d on b.fldCountryCode = d.fldCode ORDER BY c.fldUsername ASC But this just times out as it's matching too many matches. What am I doing wrong???

your not using the correct SQL, obviously :-) - that said there often many ways to get the result you want. what happens if you change the second SQL statement you quoted to: SELECT DISTINCT b.fldName, b.fldEmail, b.fldCountryCode, d.fldCode as FCode, b.fldMobile, a.fldTime as Time, c.fldUsername FROM tblSubscribersChoices a INNER JOIN tblUser c on a.fldChoice=c.fldClientID LEFT JOIN tblSubscribers b on a.fldClientID = b.fldID LEFT JOIN tc_countries d on b.fldCountryCode = d.fldCode ORDER BY c.fldUsername ASC (I added the keyword DISTINCT to the query) Also you are using an INNER JOIN for the first join and A LEFT JOIN for the second one. what happens when you replace the INNER JOIN with a LEFT JOIN?

Han.