CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
Failed Scientist
Failed Scientist

Reputation: 2027

Selecting Distinct Fields From Joined Rows

I have a table ANIMAL and another table CALF_PARENT

Design of ANIMAL table is:

ID PK IDENTITY(1,1),
TagNo varchar(30)

and other columns...

Design of CALF_PARENT is:

ID PK IDENTITY(1,1),
Parent int (FK from Animal),
IsMother varchar(1)

I am writing following query to join two tables:

SELECT a.[TagNo]
  ,a.ID   
 ,a2.TagNo
,isnull(cp.Parent,0)   
 ,a3.TagNo 
,isnull(cp.Parent,0)    

FROM [dbo].[ANIMAL] a

  LEFT JOIN [CALF_PARENT] cp
  ON a.ID = cp.Calf

  LEFT JOIN ANIMAL a2
  ON a2.ID = cp.Parent AND cp.IsMother = 'Y' AND a2.ID IS NOT NULL

  LEFT JOIN ANIMAL a3
  ON a3.ID = cp.Parent AND cp.IsMother = 'N' AND a3.ID IS NOT NULL

Its returning me record like this:

Tag, CalfID, FatherTagNo, FatherID, MotherTagNo, MotherID

FA-56   21   AB-670         3          
FA-56   21                            CW-59         7

I want it to return me single row like this:

Tag, CalfID, FatherTagNo, FatherID, MotherTagNo, MotherID

FA-56   21   AB-670         3          CW-59         7

What would be the simplest way to implement it.

Upvotes: 2

Views: 49

Answers (2)

A ツ
A ツ

Reputation: 1267

try this:

SELECT a.*, cpmd.*, cpfd.*
FROM dbo.ANIMAL a    
  LEFT JOIN CALF_PARENT cpm ON a.ID = cpm.Calf AND cpm.IsMother = 'Y'
  LEFT JOIN ANIMAL cpmd     ON cpmd.ID = cpm.Parent
  LEFT JOIN CALF_PARENT cpf ON a.ID = cpf.Calf AND cpf.IsMother = 'N'
  LEFT JOIN ANIMAL cpfd     ON cpfd.ID = cpf.Parent

and your life would be easier if your calf_parent table would consist of 3 columns:

Animal_ID (PK), Father_ID, Mother_ID

Upvotes: 2

mohan111
mohan111

Reputation: 8865

SELECT DISTINCT a.[TagNo] As Tag
  ,a.ID  As CalfID  
 ,MAX(a2.TagNo) As FatherTagNo
,MAX(isnull(cp.Parent,0))   As FatherID
 ,MAX(a3.TagNo)  As MotherTagNo
,MAX(isnull(cp.Parent,0))As MotherID    

FROM [dbo].[ANIMAL] a

  LEFT JOIN [CALF_PARENT] cp
  ON a.ID = cp.Calf

  LEFT JOIN ANIMAL a2
  ON a2.ID = cp.Parent AND cp.IsMother = 'Y' AND a2.ID IS NOT NULL

  LEFT JOIN ANIMAL a3
  ON a3.ID = cp.Parent AND cp.IsMother = 'N' AND a3.ID IS NOT NULL

  GROUP BY a.[TagNo] ,a.ID

and with example 

declare @t table (id varchar(10),cal int,father varchar(10),fatherid int,mother varchar(20),motherid int)

insert into @t(id,cal,father,fatherid,mother,motherid) values ('FA-56',21,'AB-670',3,NULL,NULL)
insert into @t(id,cal ,father,fatherid,mother,motherid) values ('FA-56',21,null,null, 'CW-59',21)

select distinct id As i,cal,MAX(father),MAX(fatherid),MAX(mother),MAX(motherid) from @t
group by  id,cal

Upvotes: 3

Related Questions