SQL inner join wrong output from two tables

Question

Students Table:

ID | Family_ID | Student_name | F_name

Families Table:

ID | F_name | Contact_No

i want to get all records from students where family_id is repeating.(basically i want to know students Brothers/Sisters records if there is any in student table.

i tried it this way but got wrong output;

SELECT students.*
FROM students
INNER JOIN families 
    ON families.id = students.family_id
    ORDER BY families.id ASC

my query result in image: as you can see some ids are showing once others are more then once, but i think all should appear more then once.

If they dont exist in the families table and you want them returned you would use a left join. otherwise give more details on wrong data and provide data examples — Brad, Nov 23 '18 at 20:53
If you can show the input and output sample ?Btw inner join means common records for some same ids in both the tables in your case. — Himanshu, Nov 23 '18 at 20:54
This query will give you all the students records whose family ID is present in the family table even if their family ID is not repeated in any other record. — Islam Hassan, Nov 23 '18 at 20:58
here is an example of how you can provide you data sample instead of image http://sqlfiddle.com/#!9/92512f/1 — Alex, Nov 23 '18 at 21:05

score 3 · Accepted Answer · edited Nov 23 '18 at 21:04

3

If you want to see only relevant people you don't need to link it to the families table. You can group the student with family_id. Here is your query :

SELECT * 
FROM Student
WHERE family_id IN (SELECT family_id 
                    FROM students 
                    GROUP BY family_id 
                    HAVING COUNT(1)>1)
ORDER BY family_id

edited Nov 23 '18 at 21:04

Alex

16,739
1
28
51

answered Nov 23 '18 at 20:59

Zeki Gumus

1,484
7
14

@Alex You are right, thanks for double checking. Every day I surprise myself :) – Zeki Gumus Nov 23 '18 at 21:04
Perfect Thankyou @Zeki Gumus – iMatti Nov 23 '18 at 21:09
Group by does all the magic. Even I tried the same but my bad I tried including group by in the students table itself but great you avoid joins for the better. :) – Himanshu Nov 23 '18 at 21:22
@Alex can you please tell me Laravel eloquent of this query? – iMatti Nov 23 '18 at 23:46
@iMatti https://stackoverflow.com/questions/33049511/how-to-execute-raw-queries-with-laravel-5-1 – Alex Nov 24 '18 at 14:33

ScaisEdge · Answer 2 · 2018-11-23T21:15:58.813

2

You could try using a join on subquery for the family_id that have more that one rows in students

SELECT students.*
FROM students
inner join  (
    select students.family_id
    FROM students
    group by students.family_id
    having count(*)>1
) t on t. family_id = students.family_id

edited Nov 23 '18 at 21:15

answered Nov 23 '18 at 21:01

ScaisEdge

131,976
10
91
107

Isn't the join unnecessary here both in the query and the subquery? – Islam Hassan Nov 23 '18 at 21:07
why unncessary? .. a join is equivalent that an IN clause but more performant – ScaisEdge Nov 23 '18 at 21:08
I didn't know that. Thanks for the clarification. – Islam Hassan Nov 23 '18 at 21:13
1

@IslamEl-Rougy . you are welcome .. the use of inner join on a subquery is always preferable to a IN clause .. an IN clause is equivalent to severl OR condition . INNER JOIN don't need the reiteration over the main table for match the rows – ScaisEdge Nov 23 '18 at 21:15
@scaisEdge why your query is retrieving some single ids? – iMatti Nov 23 '18 at 21:21
@iMatti explain better .. the query shold retrive the students that have a family with more than one students – ScaisEdge Nov 24 '18 at 07:02

SQL inner join wrong output from two tables

2 Answers2