homepage Welcome to WebmasterWorld Guest from 54.161.192.130
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

10+ Year Member



 
Msg#: 4040120 posted 3:54 pm on Dec 9, 2009 (gmt 0)

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

10+ Year Member



 
Msg#: 4040120 posted 6:57 pm on Dec 11, 2009 (gmt 0)

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

10+ Year Member



 
Msg#: 4040120 posted 11:39 pm on Dec 11, 2009 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved