CODEWITHSUNDEEP
phpmysqlsqlselect
Stephen Adelakun
Stephen Adelakun

Reputation: 804

Select from MySQL table without repetition

I have three tables in my MySQL DB : schools, parents and students. students has two columns school_id and parent_id which links each row in students to a single row in parents and and a single row in schools. The structure is such that a parent may have children in a number of schools, yet there should be only one record for that parent. Now I want to select rows from parents such that only one row is pulled off no matter how many rows in students point to that parent. Currently my query looks like :

$stmt = $db->prepare( "SELECT p.* 
                       FROM parents p 
                          INNER JOIN studnts S ON p.id = s.parent_id 
                       WHERE s.school_id = :schoolID" );

$stmt->execute( array( ':schoolID'=> $schoolID ) );

I have even replaced INNER JOIN with LEFT OUTER JOIN. No change.

This query is pulling off a row from parents for every row in students. Any help?

Thanks.

Upvotes: 1

Views: 643

Answers (2)

vikramp
vikramp

Reputation: 46

Simply using distinct in query will help

$stmt = $db->prepare( "SELECT distinct p.* 
                       FROM parents p 
                          INNER JOIN studnts S ON p.id = s.parent_id 
                       WHERE s.school_id = :schoolID" );

$stmt->execute( array( ':schoolID'=> $schoolID ) );

Upvotes: 2

Hytool
Hytool

Reputation: 1368

    SELECT 
            p.*
    FROM 
            parents p INNER JOIN studnts S ON p.id = s.parent_id 
    WHERE 
            s.school_id = :schoolID
    GROUP BY 
            p.parent_id

    or

    SELECT 
            DISTINCt p.parent_id, p.parent_name, p.parent_address ....etc
    FROM 
            parents p INNER JOIN studnts S ON p.id = s.parent_id 
    WHERE 
            s.school_id = :schoolID

to know more about GROUP BY

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

to know more about DISTINCT

http://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html

IF you want both student(s) name, along with parent use GROUP_CONCAT

to know more about GROUP_CONCAT

https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/

Upvotes: 1

Related Questions