constraints in derived table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

constraints in derived table

Author

Topic

amweiss
Starting Member

3 Posts

Posted - 2008-04-15 : 18:59:06

I have 2 tables (tbl_QP3 and tbl_QA_Questions that have userid_fk and qstAccountID as keys that can join the tables.

This statement works fine for getting a count of all records in tbl_QA_Questions that match each record in tbl_QP3

SELECT *, qa
FROM tbl_QP3
INNER JOIN
(SELECT COUNT(*) as qa, qstAccountID, qstID from
tbl_QA_Questions ) tbl
ON (tbl_QP3.userid_fk=tbl.qstAccountID)

The problem comes in when I want to constrain this so that the count is only for the number of records in tbl_QA_Questions that have a qstID less than the qstID_fk in tbl_QP3. This won't work, I know, but I'm not sure what would do the job.

SELECT *, qa
FROM tbl_QP3
INNER JOIN (SELECT COUNT(*) as qa, qstAccountID, qstID from
tbl_QA_Questions where qstID<tbl_QP3.qstid_fk) tbl
ON (tbl_QP3.userid_fk=tbl.qstAccountID)

any ideas on how to get the constraint in there?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 19:05:04

Could you post a data example of what you mean?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

amweiss
Starting Member

3 Posts

Posted - 2008-04-15 : 19:47:24

sure.. tbl_qp3 has data fields like this

userid_fk qstid_fk data1 data2

tbl_QA_questions has data fields like this

qstID qstAccountID otherdata1 otherdata2

all I'm trying to do is go through tbl_qp3 and for each record count up all the records in tbl_QA_questions that has a matching user (I.e., userid_fk=qstAccountID) but has a question ID number that is less than qstid_fk.

does that help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 19:54:47

Where's the data example?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

amweiss
Starting Member

3 Posts

Posted - 2008-04-15 : 20:37:17

sorry, I guess I'm not clear on what you mean by a data example here. both of these tables have millions of records...would just a few records give you an idea of the structure?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-16 : 12:58:30

5-10 rows from each table involved and the rows need to illustrate your problem. Show us sample rows from the tables and then what the expected output would be using those rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-16 : 15:49:22

You can't have an aggregated column value in a derived table and also single columns without an appropriate GROUP BY...

I can't see how this can possibly work

SELECT		*,
		qa
FROM		tbl_QP3
INNER JOIN	(
			SELECT	COUNT(*) as qa,
				qstAccountID,
				qstID
			from	tbl_QA_Questions
		) tbl ON tbl_QP3.userid_fk = tbl.qstAccountID

E 12°55'05.25"
N 56°04'39.16"

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-17 : 06:04:02

I THINK IT IS SYNTATICALLY WRONG BECAUSE OUTER QUERY ALWAYS USES THE OUTPUT OF INNER QUERY AND YOU ARE TRYING TO DO OPPOSIT.

chandan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources