Left Join and Ms-Access

<i><b>Originally posted by : Karthik (karthik@ccipl.com)</b></i><br /><br />Hi,<br /><br />I am running a query from an ASP on an Access database. The query looks like<br />this -<br /><br />select st.id, st.firstname, st.lastname, st.primaryemail, up.score from<br />studentresponses st LEFT OUTER JOIN userprogram up on st.primaryemail =<br />up.primaryemail where st.primaryemail like '%a%' and up.status = 1<br /><br />Let me explain a bit more before i come to what problem i am actually<br />facing.<br /><br />Studentresponses table has first name, last name, primary email of the user<br />and the other table userprogram contains primaryemail (the common field) and<br />score.<br /><br />This should return me all records from the studentresponses table (being a<br />left join) and it should also return the records matching the criteria<br />status =1 from the second table.<br /><br />But this is not happening. What is happening is, i get records having status<br />= 1 alone... i am not getting the remaining records from the first table.<br /><br />Why is this happening ? Does Access have any problem with left / right joins<br />?<br /><br />Thanks and hope to hear from you soon,<br />Karthik.<br /><br /><br />

<i><b>Originally posted by : glhays (glhays@glhdev.com)</b></i><br /><br />Hi,<br />Try adjusting you LIKE query on the Left of the Join you currently have it<br />----where st.primaryemail like '%a%'----<br />try LIKE 'a%' or '%a' or just remove the LIKE query altogether and see if you get any records<br /><br />Good Luck<br /><br />------------<br />Karthik at 3/6/2002 6:48:25 AM<br /><br /><br />Hi,<br /><br />I am running a query from an ASP on an Access database. The query looks like<br />this -<br /><br />select st.id, st.firstname, st.lastname, st.primaryemail, up.score from<br />studentresponses st LEFT OUTER JOIN userprogram up on st.primaryemail =<br />up.primaryemail where st.primaryemail like '%a%' and up.status = 1<br /><br />Let me explain a bit more before i come to what problem i am actually<br />facing.<br /><br />Studentresponses table has first name, last name, primary email of the user<br />and the other table userprogram contains primaryemail (the common field) and<br />score.<br /><br />This should return me all records from the studentresponses table (being a<br />left join) and it should also return the records matching the criteria<br />status =1 from the second table.<br /><br />But this is not happening. What is happening is, i get records having status<br />= 1 alone... i am not getting the remaining records from the first table.<br /><br />Why is this happening ? Does Access have any problem with left / right joins<br />?<br /><br />Thanks and hope to hear from you soon,<br />Karthik.<br /><br /><br />