Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two MySQL tables:

╔═══════════════════╗
║ wpss_question_set ║
╠═══════════════════╣
║ id                ║
║ quesset           ║
╚═══════════════════╝

╔════════════════╗
║ wpss_questions ║
╠════════════════╣
║ id             ║
║ question       ║
║ ques_set_id    ║
╚════════════════╝

ques_set_id is the foreign key. The query is:

SELECT
    wpss_quesset.quesset,
    wpss_questions.*
FROM wpss_quesset
JOIN wpss_questions 
    ON wpss_quesset.id = wpss_questions.ques_set_id;

I am getting duplicate results on ques_set_id and quesset. How do I select unique records from 'ques_set_id and quesset?

Screen shot

share|improve this question
    
Please ignore type and quiz_id columns. Answer considering it. –  guest Jul 31 '13 at 7:34
    
The result set of a SQL query is a table, a set of rows and columns. If some of the rows belong with one another based on certain criteria, some of the values must be repeated to indicate that. If you want to avoid the repetition in the output, then you are dealing with a presentation issue, which, accordingly, should be addressed at your presentation level (typically your application). If, however, you are positive that in this case you want the query to be your presentation level and return a result set already prepared for display, please tell/show us what the output should look like. –  Andriy M Aug 1 '13 at 8:02
add comment

2 Answers

if you want to select unique record from other join table which has same FK in multiple row you can use your query as

 SELECT
        wpss_quesset.quesset,
        wpss_questions.*
    FROM wpss_quesset
    JOIN wpss_questions 
        ON wpss_quesset.id = wpss_questions.ques_set_id
   GROUP BY wpss_quesset.id

OR If you want to get all record that of related FK you should use group concat with group by

Example

 SELECT
        wpss_quesset.quesset,
        GROUP_CONCAT( wpss_questions.question)
        wpss_questions.*
    FROM wpss_quesset
    JOIN wpss_questions 
        ON wpss_quesset.id = wpss_questions.ques_set_id
   GROUP BY wpss_quesset.id
share|improve this answer
    
Thanks it is working. –  guest Jul 31 '13 at 7:41
    
I want to also randomize the results. Can i use rand() with group by? –  guest Jul 31 '13 at 7:43
    
Second query execute all the records in one field.How to produce randam single question from that? –  guest Jul 31 '13 at 8:28
add comment
SELECT * from (SELECT
        wpss_quesset.quesset,
        GROUP_CONCAT( wpss_questions.question)
        wpss_questions.*
    FROM wpss_quesset
    JOIN wpss_questions 
        ON wpss_quesset.id = wpss_questions.ques_set_id
   GROUP BY wpss_quesset.id order by rand()) abc limit 1
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.