Select only duplicates from a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select only duplicates from a query

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-01-18 : 04:38:27

Hi.
I have this query

select
  a.ISSUEDATE,
  a.receiptno,
  b.POLICYNO,
  a.ContractID,
  b.MULTICONTRACTID,
  c.CATEGORY,
  c.endorsementaction as endorstype,
  d.ID as CONTRACTINSUREDITEMSID,
  c.INCEPTION,
  c.EXPIRY,
  c.ASSUREDID,
  c.AssuredNAME,
  c.assuredid as PersonViewID,
  e.FATHERNAME,
 (f.address + ' ' + f.streetnumber + ' ' + (select description from area where codeid = f.AREASCODEID)) as Odos,
  (select description from city where id = f.cityID) as city,
 f.zip,
  e.ID as PersonID,
  g.DriverBIRTHDATE as BIRTHDATE,
  e.LICENCENO as LICENCENO,
  c.AssuredAFM as AFM,
  (select phone from personphone where personphone.ID = c.AssuredPhoneid) as Phone,
  g.carno,
  g.FRAMENO,
  g.HP,
  g.VEHICLEHP,
  g.SHEATS,
  g.CC,
  (select SHORTDESC   from carusage where carusage.codeid = g.CarUsageCodeID) as usage,
  (select Description from carmake  where carmake.codeid = g.carmakecodeid)   as MAKEDESCRIPTION,
  (select Description from CarModel where CarModel.ID = g.CarModelid)         as MODELDESCRIPTION,
  g.CONSTRACTIONYEAR
from
  invoices  a Inner Join Contract b             On a.contractid       = b.ID
              Inner Join Multicontract c        On b.multicontractid  = c.id
              Inner Join ContractInsuredItems d On b.id               = d.ContractID
              Inner Join DefCar g               On d.DefBranchID      = g.ID
	      Inner Join person e               On c.AssuredID        = e.id
             Left  Join personaddress  f       On c.AssuredADDRESSID = f.id
where
     -- a.ISSUEDATE between '011107' and '301107'
a.ISSUEDATE between '01/11/2007' and '30/11/2007'
  and b.branchcodeid = 19
  and c.category in (1,2,3)
 and policyno=1910093705
order by
  c.category,
  a.ISSUEDATE

Anyhow, it returns some results.
What i want to do is when to select only rows that a.ContractID has duplicate values
Because i'm better at vb i was thinking to get all records to a dataset in vb and do it from there but is there a way to do it on a script?
With IF or Case perhaps??
Thanks...

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-01-18 : 05:43:36

Hi.
Ok i did it but because now i get 2 rows with the same a.Contractid, i would like to get the distinct row.
P.E.
If i have 2 rows with a.contractid = 8888
8888
8888

i would like to have one row only
8888

I suppose something with distinct somewhere....

here is what gives the double rows..

select 
-- b.id, 
b.MULTICONTRACTID,
 a.ISSUEDATE,
  a.receiptno,
  b.POLICYNO,
  a.ContractID,
  
  c.CATEGORY,
  c.endorsementaction as endorstype,
  d.ID as CONTRACTINSUREDITEMSID,
  c.INCEPTION,
  c.EXPIRY,
  c.ASSUREDID,
  c.AssuredNAME,
  c.assuredid as PersonViewID,
c.assuredid as PersonViewID,
  e.FATHERNAME,
 (f.address + ' ' + f.streetnumber + ' ' + (select description from area where codeid = f.AREASCODEID)) as Odos,
  (select description from city where id = f.cityID) as city,
 f.zip,
  e.ID as PersonID,
  g.DriverBIRTHDATE as BIRTHDATE,
  e.LICENCENO as LICENCENO,
  c.AssuredAFM as AFM,
  (select phone from personphone where personphone.ID = c.AssuredPhoneid) as Phone,
  g.carno,
  g.FRAMENO,
  g.HP,
  g.VEHICLEHP,
  g.SHEATS,
  g.CC,
  (select SHORTDESC   from carusage where carusage.codeid = g.CarUsageCodeID) as usage,
  (select Description from carmake  where carmake.codeid = g.carmakecodeid)   as MAKEDESCRIPTION,
  (select Description from CarModel where CarModel.ID = g.CarModelid)         as MODELDESCRIPTION,
  g.CONSTRACTIONYEAR

from 
 invoices  a Inner Join Contract b             On a.contractid       = b.ID
              Inner Join Multicontract c        On b.multicontractid  = c.id
              Inner Join ContractInsuredItems d On b.id               = d.ContractID
 Inner Join DefCar g               On d.DefBranchID      = g.ID
	      Inner Join person e               On c.AssuredID        = e.id
             Left  Join personaddress  f       On c.AssuredADDRESSID = f.id

where
     -- a.ISSUEDATE between '011107' and '301107'
a.ISSUEDATE between '01/11/2007' and '30/11/2007'
  and b.branchcodeid = 19
  and c.category in (1,2,3)
 -- and policyno=1910093705
and b.id in (
select b.id from 
 invoices  a Inner Join Contract b             On a.contractid       = b.ID
              Inner Join Multicontract c        On b.multicontractid  = c.id
              Inner Join ContractInsuredItems d On b.id               = d.ContractID
where
     -- a.ISSUEDATE between '011107' and '301107'
a.ISSUEDATE between '01/11/2007' and '30/11/2007'
  and b.branchcodeid = 19
  and c.category in (1,2,3)
-- and policyno=1910093705

 GROUP BY b.id
having count(b.id)>1)

ssnaik84
Starting Member

15 Posts

Posted - 2008-01-18 : 06:14:37

If you have invoicesID as PK in invoices table, then try with appending it...

WHERE a.invoicesID IN
(SELECT I1.invoicesID FROM invoices I1, invoices I2
WHERE I1.invoicesID > I2.invoicesID AND I1.contractid = I2.contractid)

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-01-18 : 07:18:24

where exactly in my code?
I used it like this

select 
-- b.id, 
b.MULTICONTRACTID,
 a.ISSUEDATE,
  a.receiptno,
  b.POLICYNO,
  a.ContractID,
  
  c.CATEGORY,
  c.endorsementaction as endorstype,
  d.ID as CONTRACTINSUREDITEMSID,
  c.INCEPTION,
  c.EXPIRY,
  c.ASSUREDID,
  c.AssuredNAME,
  c.assuredid as PersonViewID,
c.assuredid as PersonViewID,
  e.FATHERNAME,
 (f.address + ' ' + f.streetnumber + ' ' + (select description from area where codeid = f.AREASCODEID)) as Odos,
  (select description from city where id = f.cityID) as city,
 f.zip,
  e.ID as PersonID,
  g.DriverBIRTHDATE as BIRTHDATE,
  e.LICENCENO as LICENCENO,
  c.AssuredAFM as AFM,
  (select phone from personphone where personphone.ID = c.AssuredPhoneid) as Phone,
  g.carno,
  g.FRAMENO,
  g.HP,
  g.VEHICLEHP,
  g.SHEATS,
  g.CC,
  (select SHORTDESC   from carusage where carusage.codeid = g.CarUsageCodeID) as usage,
  (select Description from carmake  where carmake.codeid = g.carmakecodeid)   as MAKEDESCRIPTION,
  (select Description from CarModel where CarModel.ID = g.CarModelid)         as MODELDESCRIPTION,
  g.CONSTRACTIONYEAR

from 
 invoices  a Inner Join Contract b             On a.contractid       = b.ID
              Inner Join Multicontract c        On b.multicontractid  = c.id
              Inner Join ContractInsuredItems d On b.id               = d.ContractID
 Inner Join DefCar g               On d.DefBranchID      = g.ID
	      Inner Join person e               On c.AssuredID        = e.id
             Left  Join personaddress  f       On c.AssuredADDRESSID = f.id

where
     -- a.ISSUEDATE between '011107' and '301107'
a.ISSUEDATE between '01/12/2007' and '31/12/2007'
  and b.branchcodeid = 19
  and c.category in (1,2,3)
 -- and policyno=1910093705

and a.ID IN
(SELECT I1.ID FROM invoices I1, invoices I2
WHERE I1.ID > I2.ID AND I1.contractid = I2.contractid)

It brings nothing

I tried this one and it seems to work ( without "in")

select --b.id, 
b.multicontractid,
 a.ISSUEDATE,
  a.receiptno,
  b.POLICYNO,
  a.ContractID,
    c.endorsementaction as endorstype,
  c.CATEGORY,
   c.INCEPTION,
  c.EXPIRY,
  c.ASSUREDID,
  c.AssuredNAME,
  c.assuredid as PersonViewID,
c.assuredid as PersonViewID,
  e.ID as PersonID,
  g.DriverBIRTHDATE as BIRTHDATE,
  e.LICENCENO as LICENCENO,
  c.AssuredAFM as AFM,
 g.carno,
  g.FRAMENO,
  g.HP,
  g.VEHICLEHP,
  g.SHEATS,
  g.CC,
  (select SHORTDESC   from carusage where carusage.codeid = g.CarUsageCodeID) as usage,
  (select Description from carmake  where carmake.codeid = g.carmakecodeid)   as MAKEDESCRIPTION,
  (select Description from CarModel where CarModel.ID = g.CarModelid)         as MODELDESCRIPTION,
  g.CONSTRACTIONYEAR
 from
 invoices  a Inner Join Contract b             On a.contractid       = b.ID
              Inner Join Multicontract c        On b.multicontractid  = c.id
              Inner Join ContractInsuredItems d On b.id               = d.ContractID
Inner Join DefCar g               On d.DefBranchID      = g.ID
	      Inner Join person e               On c.AssuredID        = e.id
             Left  Join personaddress  f       On c.AssuredADDRESSID = f.id
where
     -- a.ISSUEDATE between '011107' and '301107'
a.ISSUEDATE between '01/12/2007' and '31/12/2007'
  and b.branchcodeid = 19
  and c.category in (1,2,3)
 -- and policyno=1910093705
 GROUP BY b.id,b.multicontractid, a.ISSUEDATE,
  a.receiptno,
  b.POLICYNO,
  a.ContractID,
  
  c.CATEGORY,c.endorsementaction,
  c.INCEPTION,
  c.EXPIRY,
  c.ASSUREDID,
  c.AssuredNAME,
  c.assuredid ,
c.assuredid,
  e.ID ,
  g.DriverBIRTHDATE ,
  e.LICENCENO ,
  c.AssuredAFM,
 g.carno,
  g.FRAMENO,
  g.HP,
  g.VEHICLEHP,
  g.SHEATS,
  g.CC,
  g.CarUsageCodeID ,
g.carmakecodeid,
 g.CarModelid       ,
  g.CONSTRACTIONYEAR
having count(b.id)>1

It does not work when i select "d.ID as CONTRACTINSUREDITEMSID"
i removed it and it works ( d.ID as CONTRACTINSUREDITEMSID had different numbers and not same as a.receiptno and b.multicontractid and b.id).

Was that the problem? I'm not really sure but i can't see anything else.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources