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.

Answers


    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/


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

Need Your Help

UIKeyboardDidShowNotification called multiple times, and sometimes with incorrect keyboard dimensions

ios uikeyboard nsnotification

I am trying to move a UITextView above the keyboard whenever the keyboard appears/changes. Let's say I have the English keyboard displaying and then switch directly to the Chinese keyboard (which is

What is the new way of checking "callable" methods in python 3.x?

python python-3.x introspection

I was studying introspection in Python, and as I was getting through basic examples, I found out that the callable built-in function is no longer available in Python 3.1.