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? Can someone please write me a stored procedure.
SELECT Table1.UserName, Table1.ServerID, Table2.Date
FROM Table2 RIGHT JOIN Table1 ON (Table2.ServerID = Table1.ServerID) AND (Table2.UserName = Table1.UserName);
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.
select tab1.username
, tab1.surveryid
, tab2.date
from tab1
left
join tab2
on tab2.username = tab1.username
tab2.surveryid = tab1.surveyid
but why is this in two different tables? there appears to be no need unless a user can take a survery more than once. and change the name of your date column to something else. using reserved words as column names is a bad idea.