SQL Server 2000 - Query for asp page - search page with 7 tables

[buck1107]

Hi,
I'm working on an ASP (classic) search page that needs to query 7 tables. The tables have in common a StudentID. I've experimented several different ways, including a page with 7 separate queries, as well as nested loops, etc. I would like to, if possible, only have one query. Thus, I've tested such a query in Query Analyzer and have wound up with a huge set of results. In order to eliminate redundant data (many rows for each student), and have just one row for each student, is there a recommended technique? I've been looking at the coalesce function - are there other ways to do this?

Thanks for any help - much appreciated. :)

Here's the query (as it will contain search conditions, I've left those parts out of the query for now):

SELECT Distinct
t.StudentID, t.StudentName, t.Birthdate, t.[Mar Status], t.ethnicity, t.sex,
t.Institution, t.Degree, t.[External GPA],
t.[Test ID], t.Component, t.Score, t.[Test Dt],
t.[Strt Level], t.Term, t.Grade, t.[Grade Base], t.Subject, t.Catalog, t.Descr,
t.[Admit Term], t.[Org ID], t.[Ext Term], t.[Year], t.zz_EXT_CRS_Combined_Descr, t.zz_EXT_CRS_Combined_Subject
FROM
(
SELECT zz_BIO.[StudentID] as [StudentID], zz_BIO.name as [StudentName], zz_BIO.Birthdate, zz_BIO.[Mar Status], zz_BIO.ethnicity, zz_BIO.sex,
ZZ_EXT_ED.Institution, ZZ_EXT_ED.Degree, ZZ_EXT_ED.[External GPA],
zz_PCAT.[Test ID], zz_PCAT.Component, zz_PCAT.Score, zz_PCAT.[Test Dt],
zz_Grades.[Strt Level], zz_Grades.Term, zz_Grades.Grade, zz_Grades.[Grade Base], zz_Grades.Subject, zz_Grades.Catalog, zz_Grades.Descr,
zz_EXT_CRS_Combined.[Admit Term], zz_EXT_CRS_Combined.[Org ID], zz_EXT_CRS_Combined.[Ext Term], zz_EXT_CRS_Combined.[Year], zz_EXT_CRS_Combined.Descr as zz_EXT_CRS_Combined_Descr, zz_EXT_CRS_Combined.Subject as zz_EXT_CRS_Combined_Subject
FROM zz_BIO INNER JOIN zz_EXT_ED ON zz_BIO.[StudentID] = zz_EXT_ED.ID
INNER JOIN
zz_PCAT ON zz_BIO.[StudentID] = zz_PCAT.ID
INNER JOIN
zz_Grades ON zz_BIO.[StudentID] = zz_Grades.ID
INNER JOIN
zz_EXT_CRS_Combined ON zz_BIO.[StudentID] = zz_EXT_CRS_Combined.ID
WHERE ((zz_EXT_ED.Institution is not null) and (zz_EXT_ED.Degree is not null)) 
)t
order by t.StudentID

[syber]

1. The outer query doesn't seem to do anything for you, just use the aliases you need in the interior (derived) query.

2. Since you are wanting to display columns from seven tables, you must join seven tables. Each one-to-many JOIN is going to bring back additional rows.

3. One way you can reduce the amount of rows being returned is to include the search conditions in the JOIN statement. For instance you could say:

FROM zz_BIO INNER JOIN zz_EXT_ED ON zz_BIO.[StudentID] = zz_EXT_ED.ID  
 AND zz_EXT_ED.Institution IS NOT NULL AND zz_EXT_ED.Degree IS NOT NULL 
INNER JOIN 
 zz_PCAT ON zz_BIO.[StudentID] = zz_PCAT.ID AND <search cond. for zz_PAT> 
INNER JOIN 
 zz_Grades ON zz_BIO.[StudentID] = zz_Grades.ID AND <search cond. for zz_GRADES> 
INNER JOIN 
 zz_EXT_CRS_Combined ON zz_BIO.[StudentID] = zz_EXT_CRS_Combined.ID  
 AND <search cond. for zz_CRS_Combined> 

[buck1107]

Many thanks for your input in this - I hadn't thought that was possible.
I'll be sure to try it - thanks again! :)