CODEWITHSUNDEEP
mysql
user2553072
user2553072

Reputation: 1

getting repeated data from mysql select statement

My name is Srinivas i am doing a school management software in php using mysql as a db.

pl any one help me, i have stucked in my sql query.

I'll explain my prob . I have two tables one is student and another table is marks

Each table has 5 rows when i execute select statement both tables results 5 records in each result. this is the select statement i had wrote

For table Student : SELECT ta.rollno, ta.StdNm FROM tbl_student ta WHERE ta.Cls =22

For table Marks : SELECT tc.telugu FROM tbl_marks tc WHERE tc.Cid =22

both resulting 5 records in each statement but when i combine them both it results 25 records instead of displaying 5 records

this is combined sql select:

SELECT DISTINCT tc.rollno, tc.english, ta.rollno, ta.StdNm FROM tbl_marks tc, tbl_student ta WHERE tc.Cid =22 AND ta.Cls =22

pl any one tell me where i am doing mistake in this statement

Upvotes: 0

Views: 94

Answers (5)

Snehal
Snehal

Reputation: 1

Use Joins. Your query generates cross-product of individual queries thus resulting in 25 results.

Upvotes: 0

kevinm
kevinm

Reputation: 475

Try this...

SELECT DISTINCT tc.rollno, tc.english, ta.rollno, ta.StdNm 
FROM tbl_marks tc, tbl_student ta 
WHERE tc.Cid =22 AND ta.Cls =22 AND ta.rollno=tc.rollno;

Upvotes: 2

Plamen Nikolov
Plamen Nikolov

Reputation: 2733

You need JOIN SQL query: Try this:

SELECT tc.rollno, tc.english, ta.rollno, ta.StdNm 
FROM tbl_marks AS tc JOIN tbl_student AS ta ON tc.Cid = ta.Cls 
WHERE tc.Cid =22

Upvotes: 0

flaschenpost
flaschenpost

Reputation: 2235

SELECT tc.rollno, tc.english, ta.rollno, ta.StdNm 
FROM tbl_marks tc
inner join tbl_student ta on ta.Cls = tc.Cid
WHERE ta.Cls =22

Upvotes: 0

Stephan
Stephan

Reputation: 8090

Try this:

SELECT  
    tc.rollno, 
    tc.english, 
    ta.rollno, 
    ta.StdNm 
FROM 
    tbl_marks tc
LEFT JOIN tbl_student ta 
    ON ta.Cls = tc.Cid
WHERE 
    tc.Cid =22

Upvotes: 0

Related Questions