Jump to content

A Simple Sql Query Question (i Think)


bkana

Recommended Posts

Hi all,I hope I'm posting this question in the right section.I have a query setup to pull people, courses, course codes, course dates, etc. The pull (using VB and SRS Reporting) gives me a seprate page for each person with all the courses they have taken for the year. Now, I need to modify the query to pull everyone who has taken a certain course (8FLA-100). I know what your thinking - just use the WHERE clause. The catch is that I still need to pull the other courses they have taken. So I need everyone who has taken course 8FLA-100 but also include the other courses they have taken as well. Make sense, I know I'm missing something and it's probably simple, I just can't get around it. I thought about doing a SORT or ORDER BY and just pull out the ones I need, but it's about 4100 people.Thanks,w_K

Link to comment
Share on other sites

Hi, it would be a lot of help if you could show us the tables (fields), also are you trying to do it in just one query? or could you do more?in the case of more than one i would try to bring all the people of that course and with their ids bring all their info.hope this helps.

Link to comment
Share on other sites

Thanks for responding, below is the query I'm using. Is this enough info to work with:As it stands now the below query brings back all the people and thier courses, course dates, course codes, etc. for a particular year and having purchased the "CE Registry"When I add this line to the WHERE clause (PA_educationhistory.PA_coursecode = '8FLA-100'), it only brings back that course and everyone who took it. I need to bring back everyone who took this course and also list the other courses they took. Basically only people who have this course, but list the other courses they have taken as well.DECLARE @from datetime, @to datetimeSET @from = CONVERT(varchar(11), @MinDate, 1)SET @to = CONVERT(varchar(11), @Maxdate, 1)SELECT DISTINCT Contact.FirstName, Contact.LastName, Contact.MiddleName, Contact.ftpsiteurl, PA_educationhistory.PA_coursecode, PA_educationhistory.pa_courseidName, PA_educationhistory.PA_activitydate, PA_course.AAA_StartDate, PA_course.AAA_EndDate, PA_educationcredit.pa_creditcategoryidName, PA_educationcredit.PA_ceus, PA_educationhistory.PA_educationhistoryId, Contact.ContactId FROM FilteredInvoiceDetail INNER JOIN FilteredInvoice ON FilteredInvoiceDetail.invoiceid = FilteredInvoice.invoiceid INNER JOIN PA_educationhistory INNER JOIN Contact ON PA_educationhistory.pa_contactid = Contact.ContactId INNER JOIN PA_educationcredit ON PA_educationhistory.PA_educationhistoryId = PA_educationcredit.pa_educationhistoryid INNER JOIN PA_course ON PA_educationhistory.pa_courseid = PA_course.PA_courseId ON FilteredInvoice.contactid = Contact.ContactIdWHERE (CONVERT(varchar(11), PA_course.AAA_StartDate, 1) >= @from) AND (CONVERT(varchar(11), PA_course.AAA_EndDate, 1) <= @to) AND (FilteredInvoiceDetail.productidname = N'CE Registry 2008') ORDER BY Contact.LastName

Link to comment
Share on other sites

Hi, the solution for this is using alias for the tables, your query is maybe missing parts or you have a bigger knowledge of sql than i do.anyway, with alias you can join the same table multiple times, i add alias to all the tables so is kind of shorter:SELECT DISTINCT C.ContactId, C.FirstName, C.LastName, C.MiddleName, C.ftpsiteurl, PAeh.PA_coursecode, PAeh.pa_courseidName, PAeh.PA_activitydate, PAeh.PA_educationhistoryId, PAc.AAA_StartDate, PAc.AAA_EndDate, PAec.pa_creditcategoryidName, PAec.PA_ceusFROM FilteredInvoiceDetail FIDINNER JOIN FilteredInvoice FI ON FID.invoiceid = FI.invoiceidINNER JOIN PA_educationhistory PAehINNER JOIN PA_educationhistory PAehTWOINNER JOIN Contact C ON PAeh.pa_contactid = C.ContactId INNER JOIN PA_educationcredit PAec ON PAeh.PA_educationhistoryId = PAec.pa_educationhistoryid INNER JOIN PA_course PAc ON PAeh.pa_courseid = PAc.PA_courseId ON FI.contactid = C.ContactIdWHERE (CONVERT(varchar(11), PAc.AAA_StartDate, 1) >= @from) AND (CONVERT(varchar(11), PAc.AAA_EndDate, 1) <= @to) AND (FID.productidname = N'CE Registry 2008') AND (PAehTWO.PA_coursecode = '8FLA-100')ORDER BY C.LastNamei got no clue how did you successfully (if you did) did the first underlined join ... but anyway since you already have all the others it must be no problem.the thing now is since you are no longer using the table name, you can now bring all the courses the people take from the first join and bring only the people that took that course from the second join.i sure finishing this query must be easier now. (especially executing it)hope this helps

Link to comment
Share on other sites

Hi bkanaI'm sorry if this is wrong, but I think you need to use an outer join on education history:DECLARE @from datetime, @to datetimeSET @from = CONVERT(varchar(11), @MinDate, 1)SET @to = CONVERT(varchar(11), @Maxdate, 1)SELECT DISTINCT Contact.FirstName, Contact.LastName, Contact.MiddleName, Contact.ftpsiteurl, eduhist.PA_coursecode, eduhist.pa_courseidName, eduhist.PA_activitydate, pcourse.AAA_StartDate, pcourse.AAA_EndDate, educred.pa_creditcategoryidName, educred.PA_ceus, eduhist.PA_educationhistoryId, Contact.ContactId FROM FilteredInvoiceDetail INNER JOIN FilteredInvoice USING (invoiceid)INNER JOIN Contact ON eduhist.pa_contactid = Contact.ContactId INNER JOIN PA_educationcredit AS educred ON eduhist.PA_educationhistoryId = educred.pa_educationhistoryid INNER JOIN PA_course AS pcourse ON eduhist.pa_courseid = pcourse.PA_courseId LEFT JOIN PA_educationhistory AS eduhist ON eduhist.pa_contactid = Contact.ContactIdWHERE (CONVERT(varchar(11), PA_course.AAA_StartDate, 1) >= @from) AND (CONVERT(varchar(11), PA_course.AAA_EndDate, 1) <= @to) AND (FilteredInvoiceDetail.productidname = N'CE Registry 2008') AND (eduhist.PA_coursecode = '8FLA-100')ORDER BY Contact.LastNameYou had this line after the course-join:ON FilteredInvoice.contactid = Contact.ContactIdI'm not sure if you can join twice on one line. I think this might help since it will (in theory) include all the fields from PA_history.I have not tried it.Apologies if I'm wrong.Enthusiastic

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...