March 28, 2012 at 2:11 am
Hi All,
I am having trouble with a statement, i am trying to select all data from 2 tables which link together with an id. But i would like to only bring back ones where there is only 1 group. Hope this makes sense.
This is what i am trying to acheive:
SELECT Count(bd.dqlinkid) , bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID
FROM [Branch data 2009 - 2012] AS bd INNER JOIN [main mailing data] AS md ON bd.dqlinkid = md.dqlinkid
GROUP BY bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID
HAVING (((Count(bd.dqlinkid))=2));
Any help would be great, thanks
March 28, 2012 at 2:18 am
So i am looking for 1 Master record which only has 1 duplicate, and then 1 master with 2 dupes, i have already flagged the dupes and linked to the master.
March 28, 2012 at 2:21 am
can you post DDL and sample data along with your expected results as per the 2nd link in my signature block?
March 28, 2012 at 2:25 am
/****** Object: Table [dbo].[Branch data 2009 - 2012] Script Date: 03/28/2012 09:22:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Branch data 2009 - 2012](
[DQID] [nvarchar](255) NULL,
[ID] [int] NOT NULL,
[Salutation] [nvarchar](255) NULL,
[First Name] [nvarchar](255) NULL,
[Middle Name] [nvarchar](255) NULL,
[Surname] [nvarchar](255) NULL,
[Full Name] [nvarchar](255) NULL,
[Title] [nvarchar](255) NULL,
[Phone number] [nvarchar](255) NULL,
[Email] [nvarchar](255) NULL,
[Web Address] [nvarchar](255) NULL,
[Organisation] [nvarchar](255) NULL,
[Address Line 1] [nvarchar](255) NULL,
[Address Line 2] [nvarchar](255) NULL,
[Address Line 3] [nvarchar](255) NULL,
[Address Line 4] [nvarchar](255) NULL,
[Post Town] [nvarchar](255) NULL,
[County] [nvarchar](255) NULL,
[Postcode] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Invoice No] [nvarchar](255) NULL,
[Invtot] [nvarchar](255) NULL,
[VehicleRegNo] [nvarchar](255) NULL,
[Plant] [nvarchar](255) NULL,
[Created on] [datetime] NULL,
[CarManufacturer] [nvarchar](255) NULL,
[Car model] [nvarchar](255) NULL,
[CarManufactYear] [nvarchar](255) NULL,
[Mileage] [nvarchar](255) NULL,
[Customer] [nvarchar](255) NULL,
[branch code] [nvarchar](255) NULL,
[urn no] [nvarchar](255) NULL,
[success] [nvarchar](255) NULL,
[branch type] [nvarchar](255) NULL,
[Month] [nvarchar](255) NULL,
[Marked] [nvarchar](255) NULL,
[DQFlag] [nvarchar](255) NULL,
[DQLinkID] [nvarchar](255) NULL
) ON [PRIMARY]
GO
above is for 1st table
/****** Object: Table [dbo].[Main mailing data] Script Date: 03/28/2012 09:23:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Main mailing data](
[DQID] [nvarchar](255) NULL,
[ID] [int] NOT NULL,
[Salutation] [nvarchar](255) NULL,
[First Name] [nvarchar](255) NULL,
[Middle Name] [nvarchar](255) NULL,
[Surname] [nvarchar](255) NULL,
[Full Name] [nvarchar](255) NULL,
[Title] [nvarchar](255) NULL,
[Phone number] [nvarchar](255) NULL,
[Email] [nvarchar](255) NULL,
[Web Address] [nvarchar](255) NULL,
[Organisation] [nvarchar](255) NULL,
[Address Line 1] [nvarchar](255) NULL,
[Address Line 2] [nvarchar](255) NULL,
[Address Line 3] [nvarchar](255) NULL,
[Address Line 4] [nvarchar](255) NULL,
[Post Town] [nvarchar](255) NULL,
[County] [nvarchar](255) NULL,
[Postcode] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Invoice No] [nvarchar](255) NULL,
[Invtot] [nvarchar](255) NULL,
[VehicleRegNo] [nvarchar](255) NULL,
[Plant] [nvarchar](255) NULL,
[Created on] [datetime] NULL,
[CarManufacturer] [nvarchar](255) NULL,
[Car model] [nvarchar](255) NULL,
[CarManufactYear] [nvarchar](255) NULL,
[Mileage] [nvarchar](255) NULL,
[Customer] [nvarchar](255) NULL,
[branch code] [nvarchar](255) NULL,
[urn no] [nvarchar](255) NULL,
[success] [nvarchar](255) NULL,
[branch type] [nvarchar](255) NULL,
[month] [nvarchar](255) NULL,
[Marked] [nvarchar](255) NULL,
[DQFlag] [nvarchar](255) NULL,
[DQLinkID] [nvarchar](255) NULL
) ON [PRIMARY]
GO
and that is for 2nd table
I dont quite know how to put the data into here???
March 28, 2012 at 2:29 am
take a look at the Create Some Data section of the article, or dump out the data into 2 excel files so we can import it
March 28, 2012 at 2:34 am
data is attached, 2 spreadsheets in one file
March 28, 2012 at 2:45 am
the issue is down to the DQID's and registrations being different for the same DQLinkID and your group by is working as it should as it cannot group 416 into 417 and cannot group AB11 DEF into AB12 DEF as they are different
what is the expected output from this, do you need all of this data or can some columns be removed? if so I would look at row_number using partition by on the DQLinkID and then ordering on the DQLinkID
March 28, 2012 at 2:52 am
basically i have de-duped and flagged all duplicated as a duplicate, then i have linked them to its master record using the master records dqid. therefore all masters have thier own dqid in the linkid and the dupes have the masters dqid in the link id.
I want all the data back (all columns) if poss. on a master where there is only 1 dupe, then master with 2 dupes, master with 3 dupes and a master with 4 or more dupes
Please help, i have been racking my brain for a few days now!!!
March 28, 2012 at 3:14 am
you just need to do a row_number based on the bd.DQLinkID then and it will say if its 1,2,3,4,5,6 rows for that particular DQLinkID which will give you a starting point to then start using the max function to get out where there are only 2 matches, 3 matches, 4 matches etc
March 28, 2012 at 3:17 am
so what would be the syntax for that?
March 28, 2012 at 3:19 am
ROW_NUMBER () OVER(...........) AS RowNum where you put in the over clause either a partitioning field and an ordering field or just an ordering field.
in your case you will need to partition and order
March 28, 2012 at 3:24 am
im sorry but im confused as to where to put what in the statement??
March 28, 2012 at 3:26 am
can put it anywhere in the query you want
SELECT
ROW_NUMBER() OVER (PARTITION BY bd.DQLinkID ORDER BY bd.DQLinkID) AS NumMatches,
bd.DQLinkID, ...............................
March 28, 2012 at 3:29 am
could you add it to the previous query so i can get the correct result please? i dont know what else to put in the query. thanks
March 28, 2012 at 3:33 am
This will give you a number in the first column based on how many times DQLinkID appears in the result set
SELECT
ROW_NUMBER() OVER (PARTITION BY bd.DQLinkID ORDER BY bd.DQLinkID) AS NumMatches,
bd.DQLinkID as bdDQLinkID,
bd.DQID as bdDQID,
bd.ID as bdID,
bd.Salutation as bdSalutation,
bd.[First Name] as bdFirstName,
bd.[Middle Name] as bdMiddleName,
bd.Surname as bdSurname,
bd.[Full Name] as bdFullName,
bd.Title as bdTitle,
bd.[Phone number] as bdPhoneNumber,
bd.Email as bdEmail,
bd.[Web Address] as bdWebAddress,
bd.Organisation as bdOrganisation,
bd.[Address Line 1] as bdAddress1,
bd.[Address Line 2] as bdAddress2,
bd.[Address Line 3] as bdAddress3,
bd.[Address Line 4] as bdAddress4,
bd.[Post Town] as bdPostTown,
bd.County as bdCounty,
bd.Postcode as bdPostcode,
bd.Country as bdCountry,
bd.[Invoice No] as bdInvoiceNo,
bd.Invtot as bdInvtot,
bd.VehicleRegNo as bdVehicleRegNo,
bd.Plant as bdPlant,
bd.[Created on] as bdCreatedOn,
bd.CarManufacturer as bdCarMan,
bd.[Car model] as bdCarMod,
bd.CarManufactYear as bdCarManYear,
bd.Mileage as bdMileage,
bd.Customer as bdCustomer,
bd.[branch code] as bdBranchCode,
bd.[urn no] as bdURNNo,
bd.success as bdSuccess,
bd.[branch type] as bdBranchType,
bd.[Month] as bdMonth,
bd.Marked as bdMarked,
bd.DQFlag as bdDQFlag,
md.DQID as mdDQID,
md.ID as mdID,
md.Salutation as mdSalutation,
md.[First Name] as mdFirstName,
md.[Middle Name] as mdMiddleName,
md.Surname as mdSurname,
md.[Full Name] as mdFullName,
md.Title as mdTitle,
md.[Phone number] as mdPhoneNumber,
md.Email as mdEmail,
md.[Web Address] as mdWebAddress,
md.Organisation as mdOrganisation,
md.[Address Line 1] as mdAddress1,
md.[Address Line 2] as mdAddress2,
md.[Address Line 3] as mdAddress3,
md.[Address Line 4] as mdAddress4,
md.[Post Town] as mdPostTown,
md.County as mdCounty,
md.Postcode as mdPostCode,
md.Country as mdCountry,
md.[Invoice No] as mdInvoiceNo,
md.Invtot as mdInvtot,
md.VehicleRegNo as mdVehicleRegNo,
md.Plant as mdPlant,
md.[Created on] as mdCreatedOn,
md.CarManufacturer as mdCarMan,
md.[Car model] as mdCarMod,
md.CarManufactYear as mdCarManYear,
md.Mileage as msMileage,
md.Customer as mdCustomer,
md.[branch code] as mdBranchCode,
md.[urn no] as mdURNNo,
md.success as mdSuccess,
md.[branch type] as mdBranchType,
md.[month] as mdMonth,
md.Marked as mdMarked,
md.DQFlag as mdDQFlag,
md.DQLinkID as mdDQLinkID
FROM
[Branch data 2009 - 2012] AS bd
INNER JOIN
[main mailing data] AS md
ON
bd.dqlinkid = md.dqlinkid
ORDER BY 2,1
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply