在sql 2005中4个表的内部联接 [英] Inner Join of 4 tables in sql 2005

问题描述

我正在尝试对4个表进行内部连接，但是要获得重复的行。我想要小组明智的数据。

我试过group by子句但是它给出了错误。

I am trying to make inner join of 4 tables but getting repetitive rows. I want group wise data.
I tried group by clause but it gives error.

drop proc sp_JobDetails
go
create proc sp_JobDetails
(
	@Ps_ordno varchar(100)
)

as

begin

select 

C.s_cardno , C.n_qty, C.s_formofinner , C.s_ordno ,

A.s_cardno , A.s_particulars, A.n_qty , A.s_ordno,

P.s_cardno , P.s_particular , P.s_Material , P.s_color , P.n_qty ,

E.s_cardno , E.s_particular, E.s_mcolor, E.s_pcolor , E.n_qty

from (((Tb_cardDetails C  inner join tb_addAccessories A on C.s_ordno = A.s_ordno) inner join Tb_ParticularsDetails P on C.s_ordno = P.s_ordno )inner join Tb_envolopeDetails E on C.s_ordno = E.s_ordno)
  
where C.s_ordno = @Ps_ordno


End

推荐答案

在select distinct中使用DISTINCT，以获得不重复的行。您还可以通过更改所加入表的顺序来更改所获得的内容。

Use DISTINCT as in select distinct, to not get duplicate rows. You can also change what you get by changing the order of the tables you join.

您的select语句显示多个duplcate行。



如果你这样做，你的查询将避免重复：

your select statement is showing multiple duplcate rows.

Your query would avoid duplicates if you do this:

    drop proc sp_JobDetails
    go
    create proc sp_JobDetails
    (
	@Ps_ordno varchar(100)
    )
 
    as
 
    begin
 
    select 
 
    C.s_cardno 
   ,C.n_qty
   ,C.s_formofinner
   ,C.s_ordno
   --,A.s_cardno             --Duplicate
   ,A.s_particulars
   --,A.n_qty                --Duplicate
   --,A.s_ordno              --Duplicate
   --,P.s_cardno             --Duplicate
   ,P.s_particular         --May be a duplicate from above as s_particulars
   ,P.s_Material
   ,P.s_color
   --,P.n_qty                --Duplicate
   --,E.s_cardno             --Duplicate
   --,E.s_particular         --Duplicate
   ,E.s_mcolor
   ,E.s_pcolor
   --,E.n_qty                --Duplicate
from (((Tb_cardDetails C 
    inner join tb_addAccessories A 
        on C.s_ordno = A.s_ordno) 
    inner join Tb_ParticularsDetails P 
        on C.s_ordno = P.s_ordno )
    inner join Tb_envolopeDetails E 
        on C.s_ordno = E.s_ordno)  
where C.s_ordno = @Ps_ordno
 
End

如果你想要避免重复数据，你可以使用

If you want to avoid duplicate data, you can use

SELECT DISTINCT --Followed by your needs

然而你告诉回调带回重复。



希望有所帮助。

however you are telling the callback to bring back duplicates.

hope that helps.

这篇关于在sql 2005中4个表的内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！