Selecting QA and relating to a user table

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

SQL Server Development (2000)

Selecting QA and relating to a user table

Author

Topic

lancepr
Starting Member

17 Posts

Posted - 2002-06-26 : 12:19:10

I have 3 tables, users, questions, answers

I want to get the questions and answers that each person has taken.
Each person has taken anywhere from 15-20 questions.
I don't need their name and info 15-20 times just their name and 15-20 questions and answers they have.

here is my select with the duplicate user records.

 
select l.*, q.txtquestion, a.txtanswer
from tbl_Answer as A, tbl_Questions as Q, users as L
where A.txtanswer <> '' 
	AND A.numqid = q.numQid 
	AND A.numLeadsID = l.ID
order by l.ID

anyone have a tip to get me moving in the right direction.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-26 : 12:25:44

Relational databases work with sets of data in rows and columns. I can understand you don't 'want' the dudes name 20 times, but what then should be in the name column for the 20 rows worth of questions. Or is it that you want one row for the dude and 20 columns. If so, what then do you want for the question 20 column when the dude only has taken 15 questions. What about the guy that has taken 1 more question than you have columns for?

Give us an example of the rowset you'd like to get out of the database.

<O>

lancepr
Starting Member

17 Posts

Posted - 2002-06-26 : 13:08:18

I just want to be able to put this data into a spread sheet.
This is how I would like my data to be returned

info,q1,q2,q3....,a1,a3,a4

or
Info
q1,a1
q2,a2
.....

nulls would be ok

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-26 : 13:17:00

Presenting denormalized data from a normalized schema is a bear....


select
    l.*,
    q1.txtquestion as q1,
    q2.txtquestion as q2,
    ....
    qn.txtquestion as qn,
    a1.txtanswer as a1,
    a2.txtanswer as a2,
    ....
    an.txtanswer as an
from
    users l
    left outer join tbl_questions q1
        on ....
    left outer join tbl_questions q2
        on ....
    .....
    left outer join tbl_questions qn
        on ....
    left outer join tbl_answer a1
        on ...
    left outer join tbl_answer a2
        on ....
    ....
    left outer join tbl_answer an
        on ....
where
.....

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources