SQL query question..

registered · Useless Joined: Aug 23, 2002 Posts: 213 Location: Chicago

I have two tables:

Tab1:
Username | SurveyID
RC1|21
RC1|22
RC2|21
RC2|22

Tab2:
Username | SurveyID | Date
RC1|22|05/05/05
RC2|21|05/05/05

Table 1 is a long table that has usernames and surveys assigned.
Table 2 is a subset and for each SurveyID from Tab1, it has a row
as to when the survey was taken.

I want to combine these two tables and produce a report like this:

Report:
Username | SurveyID | Date
RC1|21|N/A
RC1|22|05/05/05
RC2|21|05/05/05
RC2|22|N/A

What should be the query?

Thanks

registered · Posted: Sat Oct 01, 2005 6:38 am

Try this:

Code:




SELECT  * 


FROM  Tab1 a


LEFT JOIN  Tab2 b ON a.username = b.username AND a.surveyID = b.surveyID

This would give you NULL values in the Date field where there is no corresponding record in Tab2. Therefore, as you are cycling through your resultset, you should check if Date is NULL and if so, display "N/A", otherwise show the date.

Hope this helps,
montego

Posted: Sat Oct 01, 2005 7:47 am

Thanks so much man, I'll keep you updated if I needed more help with this.