开发者

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';
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜