Thread: table joining duplicate records
hi all, somebody please help me with my sql statement. or point me to the right documentation that i need to read. what i want to working on right now is to create a db for a survey app my tables look this surveys survey_id:survey ---------------- 1 : survey1 categories category_id:category ---------------- 1 :category1 questions question_id:question ---------------- 1 :question1 answers answer_id:answer ---------------- 1 :answer1 2 :answer2 questions_answers question_answer_id:survey_id:category_id:question_id:answer_id 1 :1 :1 :1 1 :1 :1 :2 and my query looks like this select s.survey, c.category, q.question, a.answer from questions_answers qa, answers a, questions q, categories c, surveys s where qa.answer_id = a.answer_id and qa.question_id = q.question_id and qa.category_id = c.category_id and qa.survey_id = s.survey_id; after executing the code, i get results like this. --query results-- survey :category :question :answer survey1:category1:question1:answer1 survey1:category1:question1:answer2 how can i elimate duplicates on my query results? an also am i using the right 'table joining' or table design for my survey app? any help would be appriciated. thanks in advance. -- "A whim, a thought, and more is sought... awake, my mind... thy will be wrought!"
In your case Survey , Categories, Questions and Answers TABLES are parents tables, and Question_answers TABLE is a Child Table.
Since you want to have survery, from Survey Table, Category from Category Table, Question from Question Table and Answer from Answer Table and alll these Atributes are on Question_answer Table, in your case you can use the following:
SELECT * FROM question_answer;
Try this STATEMENT
onin <jnana@mobius.ph> wrote:
hi all,
somebody please help me with my sql statement.
or point me to the right documentation that i need to read.
what i want to working on right now is to create a db for a survey app
my tables look this
surveys
survey_id:survey
----------------
1 : survey1
categories
category_id:category
----------------
1 :category1
questions
question_id:question
----------------
1 :question1
answers
answer_id:answer
----------------
1 :answer1
2 :answer2
questions_answers
question_answer_id:survey_id:category_id:question_id:answer_id
1 :1 :1 :1
1 :1 :1 :2
and my query looks like this
select s.survey, c.category, q.question, a.answer from questions_answers
qa, answers a, questions q, categories c, surveys s
where qa.answer_id = a.answer_id and qa.question_id = q.question_id and
qa.category_id = c.category_id and qa.survey_id = s.survey_id;
after executing the code,
i get results like this.
--query results--
survey :category :question :answer
survey1:category1:question1:answer1
survey1:category1:question1:answer2
how can i elimate duplicates on my query results?
an also am i using the right 'table joining' or table design for my
survey app?
any help would be appriciated.
thanks in advance.
--
"A whim, a thought, and more is sought... awake, my mind... thy will be wrought!"
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Try the all-new Yahoo! Mail . "The New Version is radically easier to use" The Wall Street Journal
> --query results-- > survey :category :question :answer > survey1:category1:question1:answer1 > survey1:category1:question1:answer2 > > how can i elimate duplicates on my query results? > an also am i using the right 'table joining' or table design for my > survey app? What duplicates? You have two unique answers for question1. Regards, Richard Broersma Jr.
> can i have a result set. just like the outer join returns. > but on my case. return null on duplicates. > like this one. > > survey :category :question :answer > ----------------------------------- > survey1:category1:question1:answer1 > : : :answer2 hmmm.. I am no sure that you are going to get what you want from a simple select query. A reporting software could do this easily however. also be sure to include the list in your replies :-). Regards, Richard Broersma Jr.