Repeating same rows from two tables in SQL?
I have two tables.
1) empDetails
Field(id,section,name,domain)
Records('E_01','IT','RAN','AUTOMATION')
Records('E_01','IT','SAMU','EMBEDED')
Records('E_02','MECH','RAJ','AUTO')
2) empAddress
Field(id,se开发者_开发问答ction,address)
Records('E_01','IT','BANGALORE')
Records('E_01','IT','BANGALORE')
My query is
SELECT t1.name,t1.domain,t2.address
FROM empDetails as t1 ,
empAddress as t2
WHERE t1.id = 'E_001'
AND t2.id='E_001'
AND t1.section = 'IT'
AND t2.section = 'IT'**
But I am getting repeating rows as given below:
RAN AUTOMATION BANGALORE
RAN EMBEDED BANGALORE
SAMU AUTOMATION BANGALORE
SAMU EMBEDED BANGALORE
How to fetch correctly?
I need
RAN AUTOMATION BANGALORE
SAMU EMBEDED BANGALOR
Please anyone help me?
select t1.name, t1.domain, t2.address
from empDetails as t1, empAddress as t2
where t1.id = 'E_001' and t2.id='E_001' and t1.section = 'IT' and t2.section = 'IT'
The problem is the from ..., ...
. Using a comma like this does a cross join, but you probably want an inner join.
select t1.name, t1.domain, t2.address
from empDetails as t1 INNER JOIN empAddress as t2
ON --join condition goes here
where t1.id = 'E_001' and t2.id='E_001' and t1.section = 'IT' and t2.section = 'IT'
SELECT t1.name,t1.domain,t2.address
FROM empDetails as t1
INNER JOIN empAddress as t2
ON t1.id=t2.id
WHERE t1.id = 'E_001'
AND t2.id='E_001'
AND t1.section = 'IT'
AND t2.section = 'IT'
GROUP BY t1.name
Try this..
SELECT t1.name,t1.domain,t2.address
FROM empDetails as t1
INNER JOIN empAddress as t2 on t1.id=t2.id
WHERE t1.section = 'IT'
AND t2.section = 'IT';
精彩评论