CODEWITHSUNDEEP
mysqlsql
Kavya Shree
Kavya Shree

Reputation: 1032

get a common value from multiple tables using SQL

Below is my values in each table

Reading - Table name

student_id - student ID

1
4
5
7
6
9
11

speaking - table

9
10
12
13
14
15

writing - table

1
2
3
4
5
6
7
9

Listening - table

1
2
3
4
5
6
7
8
9

My query will return the student_id's which is common in 4 tables . for example my query will return for this example is

only 9 'id is common for all tables so the query will return the count as 1. I tried with inner join and some distinct way of queries but in result I'm getting wrong answer.Please anyone help me to get out of this issue.Thanks in advance.

Upvotes: 0

Views: 1322

Answers (4)

santhoshverma
santhoshverma

Reputation: 32

select count(distinct a.student_id)

from reading_table a

join speaking_table b on a.student_id=b.student_id

join writing_table c on b.student_id=c.student_id

join listening_table d on c.student_id=d.student_id

Upvotes: 1

unrelentingfox
unrelentingfox

Reputation: 64

SELECT t1.id FROM table1 t1
INNER JOIN table2 AS t2 ON t1.id = t2.id
INNER JOIN table3 AS t3 ON t1.id = t3.id
INNER JOIN table4 AS t4 ON t1.id = t4.id

This will return the id's that are common within all 4 tables.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

A simple INNER JOIN with COUNT DISTINCT will do:

SELECT COUNT(DISTINCT studentID)
FROM Student AS st
INNER JOIN Speaking AS s ON st.studentID = s.studentID
INNER JOIN Writing AS w ON s.studentID = w.studentID
INNER JOIN Listening AS l ON w.studentID = l.studentID

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93764

Use INTERSECT 

select count(1) 
from 
(
select student_id from Reading
INTERSECT 
select student_id from speaking 
INTERSECT 
..
) a

If student_id is duplicated in any of these tables and if you don't want to count the duplicates then 

select count(Distinct student_id) 
from 
(
select student_id from Reading
INTERSECT 
select student_id from speaking 
INTERSECT 
..
) a

Upvotes: 2

Related Questions