HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Distinct rows from a join

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fromdistinctrowsjoin

Problem

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?

Solution

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

Code Snippets

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
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

Context

StackExchange Database Administrators Q#47326, answer score: 5

Revisions (0)

No revisions yet.