Select x rows on join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select x rows on join

Author

Topic

dturbert
Starting Member

8 Posts

Posted - 2012-04-05 : 09:22:17

Hi There! Hoping someone can help me with this one.

I've got about 6000 account prospect records, with varying number of contacts tied to them. I am trying to update the contact entity where only one contact exists for a given account. In other words, we create mailing lists to contacts that we identify as "Key" contacts. I need to set the contact.key flag = '1' where only one contact is joined to the given account entity. Any ideas? I'm stumped!

- Dan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-05 : 10:10:54

That's not a heavy problem.
Can you give the table structure as far as needed to understand the relation and so on?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 10:23:15

so in case duplicate contacts exists for an account on what basis you'll select key contact?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dturbert
Starting Member

8 Posts

Posted - 2012-04-05 : 10:35:07

[quote]Originally posted by webfred

That's not a heavy problem.
Can you give the table structure as far as needed to understand the relation and so on?

Table structure is as follows. Account.account_ID = Contact.account_ID

So we may have an account with 30 contacts tied to it, but in our scenario, we only want to find contacts where only one contact is tied to the specific account, or only one row on the contact table where contact.account_ID would be unique

dturbert
Starting Member

8 Posts

Posted - 2012-04-05 : 10:36:46

quote:
Originally posted by visakh16

so in case duplicate contacts exists for an account on what basis you'll select key contact?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

We've got a dupe checker in place for that, but yes it did cross my mind.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 12:29:32

so will duplicates be mrked already? is there a field for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-05 : 13:06:10

I hate Flags

Please Post the DDL of your 2 tables and some sample data (preferably in DML form) and what your expected results should be

You will get the answer you want in 10 minutes after you do that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-05 : 13:09:35

quote:
Originally posted by dturbert
Table structure is as follows. Account.account_ID = Contact.account_ID

That is not the table structure (DDL)

Fred Means:

Sie zeigen uns den Code für Ihre Tabellen und Sie werden es uns jetzt zeigen,

Like

CREATE TABLE tablename (
Col datatype nullability
, etc
, PRIMARY KEY...
, FOREIGN KEY...

you get can this out of SSMS

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

kcrinklaw
Starting Member

2 Posts

Posted - 2012-04-05 : 13:50:29

declare @t1_account table
( account_id int,
account_name varchar(255)
)

declare @t2_contact table
( account_id int,
account_contact varchar(255)
)

INSERT @t1_account
( account_id, account_name)
VALUES
(1,'account 1'),(2, 'account 2'),(3, 'account 3'),(4, 'account 4')

insert @t2_contact
( account_id, account_contact)
VALUES
(1,'contact 1-1'),(1,'contact 1-2'),(1,'contact 1-3'),(2,'contact 2-1'),(3,'contact 3-1'),(3,'contact 3-2'),(4,'contact 4-1'),(1,'contact 1-4')
;
WITH CTE (account_id)
AS
(
SELECT cte2.account_id
FROM @t1_account cte1
join @t2_contact cte2 on cte1.account_id = cte2.account_id
GROUP BY cte2.account_id
having COUNT(cte2.account_id) = 1
)
SELECT *
FROM @t1_account t1
JOIN @t2_contact t2 on t1.account_id = t2.account_id
JOIN CTE on t1.account_id = CTE.account_id

I added some extra stuff based on my understanding of your question. A CTE will allow you to create a table (CTE) based on a sub-query, which is re-joined to the parent tables to then return the info about the account and contact.

Website Expert

dturbert
Starting Member

8 Posts

Posted - 2012-04-05 : 14:15:43

quote:
Originally posted by X002548

quote:
Originally posted by dturbert
Table structure is as follows. Account.account_ID = Contact.account_ID

That is not the table structure (DDL)

Fred Means:

Sie zeigen uns den Code für Ihre Tabellen und Sie werden es uns jetzt zeigen,

Like

CREATE TABLE tablename (
Col datatype nullability
, etc
, PRIMARY KEY...
, FOREIGN KEY...

you get can this out of SSMS

Hope this is what you mean:

CREATE TABLE [dbo].[AccountBase](
[AccountId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[AccountCategoryCode] [int] NULL,
[TerritoryId] [uniqueidentifier] NULL,
[DefaultPriceLevelId] [uniqueidentifier] NULL,
[CustomerSizeCode] [int] NULL,
[PreferredContactMethodCode] [int] NULL,
[CustomerTypeCode] [int] NULL,
[AccountRatingCode] [int] NULL,
[IndustryCode] [int] NULL,
[TerritoryCode] [int] NULL,
[AccountClassificationCode] [int] NULL,
[DeletionStateCode] [int] NOT NULL,
[BusinessTypeCode] [int] NULL,
[OwningBusinessUnit] [uniqueidentifier] NULL,
[OwningTeam] [uniqueidentifier] NULL,
[OwningUser] [uniqueidentifier] NULL,
[OriginatingLeadId] [uniqueidentifier] NULL,
[PaymentTermsCode] [int] NULL,
[ShippingMethodCode] [int] NULL,
[PrimaryContactId] [uniqueidentifier] NULL,
[ParticipatesInWorkflow] [bit] NULL,
[Name] [nvarchar](160) NULL,
[AccountNumber] [nvarchar](20) NULL,
[Revenue] [money] NULL,
[NumberOfEmployees] [int] NULL,
[Description] [nvarchar](max) NULL,
[SIC] [nvarchar](20) NULL,
[OwnershipCode] [int] NULL,
[MarketCap] [money] NULL,
[SharesOutstanding] [int] NULL,
[TickerSymbol] [nvarchar](10) NULL,
[StockExchange] [nvarchar](20) NULL,
[WebSiteURL] [nvarchar](200) NULL,
[FtpSiteURL] [nvarchar](200) NULL,
[EMailAddress1] [nvarchar](100) NULL,
[EMailAddress2] [nvarchar](100) NULL,
[EMailAddress3] [nvarchar](100) NULL,
[DoNotPhone] [bit] NULL,
[DoNotFax] [bit] NULL,
[Telephone1] [nvarchar](50) NULL,
[DoNotEMail] [bit] NULL,
[Telephone2] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Telephone3] [nvarchar](50) NULL,
[DoNotPostalMail] [bit] NULL,
[DoNotBulkEMail] [bit] NULL,
[DoNotBulkPostalMail] [bit] NULL,
[CreditLimit] [money] NULL,
[CreditOnHold] [bit] NULL,
[IsPrivate] [bit] NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedBy] [uniqueidentifier] NULL,
[VersionNumber] [timestamp] NULL,
[ParentAccountId] [uniqueidentifier] NULL,
[Aging30] [money] NULL,
[StateCode] [int] NOT NULL,
[Aging60] [money] NULL,
[StatusCode] [int] NULL,
[Aging90] [money] NULL,
[PreferredAppointmentDayCode] [int] NULL,
[PreferredSystemUserId] [uniqueidentifier] NULL,
[PreferredAppointmentTimeCode] [int] NULL,
[Merged] [bit] NULL,
[DoNotSendMM] [bit] NULL,
[MasterId] [uniqueidentifier] NULL,
[LastUsedInCampaign] [datetime] NULL,
[PreferredServiceId] [uniqueidentifier] NULL,
[PreferredEquipmentId] [uniqueidentifier] NULL,
[ExchangeRate] [decimal](23, 10) NULL,
[UTCConversionTimeZoneCode] [int] NULL,
[OverriddenCreatedOn] [datetime] NULL,
[TimeZoneRuleVersionNumber] [int] NULL,
[ImportSequenceNumber] [int] NULL,
[TransactionCurrencyId] [uniqueidentifier] NULL,
[CreditLimit_Base] [money] NULL,
[Aging30_Base] [money] NULL,
[Revenue_Base] [money] NULL,
[Aging90_Base] [money] NULL,
[MarketCap_Base] [money] NULL,
[Aging60_Base] [money] NULL,
[YomiName] [nvarchar](160) NULL,
CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED
(
[AccountId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ContactBase] Script Date: 04/05/2012 13:11:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContactBase](
[ContactId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[DefaultPriceLevelId] [uniqueidentifier] NULL,
[CustomerSizeCode] [int] NULL,
[CustomerTypeCode] [int] NULL,
[PreferredContactMethodCode] [int] NULL,
[LeadSourceCode] [int] NULL,
[DeletionStateCode] [int] NOT NULL,
[OriginatingLeadId] [uniqueidentifier] NULL,
[OwningBusinessUnit] [uniqueidentifier] NULL,
[OwningUser] [uniqueidentifier] NULL,
[PaymentTermsCode] [int] NULL,
[ShippingMethodCode] [int] NULL,
[OwningTeam] [uniqueidentifier] NULL,
[AccountId] [uniqueidentifier] NULL,
[ParticipatesInWorkflow] [bit] NULL,
[IsBackofficeCustomer] [bit] NULL,
[Salutation] [nvarchar](100) NULL,
[JobTitle] [nvarchar](100) NULL,
[FirstName] [nvarchar](50) NULL,
[Department] [nvarchar](100) NULL,
[NickName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Suffix] [nvarchar](10) NULL,
[YomiFirstName] [nvarchar](150) NULL,
[FullName] [nvarchar](160) NULL,
[YomiMiddleName] [nvarchar](150) NULL,
[YomiLastName] [nvarchar](150) NULL,
[Anniversary] [datetime] NULL,
[BirthDate] [datetime] NULL,
[GovernmentId] [nvarchar](50) NULL,
[YomiFullName] [nvarchar](450) NULL,
[Description] [nvarchar](max) NULL,
[EmployeeId] [nvarchar](50) NULL,
[GenderCode] [int] NULL,
[AnnualIncome] [money] NULL,
[HasChildrenCode] [int] NULL,
[EducationCode] [int] NULL,
[WebSiteUrl] [nvarchar](200) NULL,
[FamilyStatusCode] [int] NULL,
[FtpSiteUrl] [nvarchar](200) NULL,
[EMailAddress1] [nvarchar](100) NULL,
[SpousesName] [nvarchar](100) NULL,
[AssistantName] [nvarchar](100) NULL,
[EMailAddress2] [nvarchar](100) NULL,
[AssistantPhone] [nvarchar](50) NULL,
[EMailAddress3] [nvarchar](100) NULL,
[DoNotPhone] [bit] NULL,
[ManagerName] [nvarchar](100) NULL,
[ManagerPhone] [nvarchar](50) NULL,
[DoNotFax] [bit] NULL,
[DoNotEMail] [bit] NULL,
[DoNotPostalMail] [bit] NULL,
[DoNotBulkEMail] [bit] NULL,
[DoNotBulkPostalMail] [bit] NULL,
[AccountRoleCode] [int] NULL,
[TerritoryCode] [int] NULL,
[IsPrivate] [bit] NULL,
[CreditLimit] [money] NULL,
[CreatedOn] [datetime] NULL,
[CreditOnHold] [bit] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedBy] [uniqueidentifier] NULL,
[NumberOfChildren] [int] NULL,
[ChildrensNames] [nvarchar](255) NULL,
[VersionNumber] [timestamp] NULL,
[MobilePhone] [nvarchar](50) NULL,
[Pager] [nvarchar](50) NULL,
[Telephone1] [nvarchar](50) NULL,
[Telephone2] [nvarchar](50) NULL,
[Telephone3] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Aging30] [money] NULL,
[StateCode] [int] NOT NULL,
[Aging60] [money] NULL,
[StatusCode] [int] NULL,
[Aging90] [money] NULL,
[ParentContactId] [uniqueidentifier] NULL,
[PreferredSystemUserId] [uniqueidentifier] NULL,
[PreferredServiceId] [uniqueidentifier] NULL,
[MasterId] [uniqueidentifier] NULL,
[PreferredAppointmentDayCode] [int] NULL,
[PreferredAppointmentTimeCode] [int] NULL,
[DoNotSendMM] [bit] NULL,
[Merged] [bit] NULL,
[ExternalUserIdentifier] [nvarchar](50) NULL,
[SubscriptionId] [uniqueidentifier] NULL,
[PreferredEquipmentId] [uniqueidentifier] NULL,
[LastUsedInCampaign] [datetime] NULL,
[TransactionCurrencyId] [uniqueidentifier] NULL,
[OverriddenCreatedOn] [datetime] NULL,
[ExchangeRate] [decimal](23, 10) NULL,
[ImportSequenceNumber] [int] NULL,
[TimeZoneRuleVersionNumber] [int] NULL,
[UTCConversionTimeZoneCode] [int] NULL,
[AnnualIncome_Base] [money] NULL,
[CreditLimit_Base] [money] NULL,
[Aging60_Base] [money] NULL,
[Aging90_Base] [money] NULL,
[Aging30_Base] [money] NULL,
CONSTRAINT [cndx_PrimaryKey_Contact] PRIMARY KEY CLUSTERED
(
[ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-05 : 17:02:25

Ever hear of Data Normalization?

Let me take a look see

b right back..don't go anywhere

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dturbert
Starting Member

8 Posts

Posted - 2012-04-10 : 14:21:38

quote:
Originally posted by dturbert

Hi There! Hoping someone can help me with this one.

I've got about 6000 account prospect records, with varying number of contacts tied to them. I am trying to update the contact entity where only one contact exists for a given account. In other words, we create mailing lists to contacts that we identify as "Key" contacts. I need to set the contact.key flag = '1' where only one contact is joined to the given account entity. Any ideas? I'm stumped!

- Dan

Anyone have any ideas on how to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:16:44

you still didnt answer my last question. will be mark down duplicate contacts already? if not, whats the rule to determine which is key contact in case multiple contacts exists?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dturbert
Starting Member

8 Posts

Posted - 2012-04-11 : 11:07:20

quote:
Originally posted by visakh16

you still didnt answer my last question. will be mark down duplicate contacts already? if not, whats the rule to determine which is key contact in case multiple contacts exists?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

We run a dupe workflow or a merge to eliminate them before-hand, so in my case I only care about finding a single contact (not greater, not less than) record tied to a given account record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:31:53

then wont it be a straightforward update?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dturbert
Starting Member

8 Posts

Posted - 2012-04-12 : 10:16:39

quote:
Originally posted by visakh16

then wont it be a straightforward update?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thats right. I need to update a segment of these contacts based on other criterias, but all i need to know how to do is how to select these contacts based on the aforementioned.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 17:42:28

nope..if there's only one contact isnt it a simple join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dturbert
Starting Member

8 Posts

Posted - 2012-04-13 : 13:32:37

quote:
Originally posted by visakh16

nope..if there's only one contact isnt it a simple join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Some companies have 40 contacts, some have 6 etc, some have just 1 etc. For companies who have multiple contacts, we have already scrubbed them clear of duplicates. We need to identify contacts where only one contact is associated to a given company. How can this be done? Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 15:17:36

[code]
SELECT required columns here...
FROM
(
SELECT required columns here..., COUNT(1) OVER (PARTITION BY ContactId) AS Occ
FROM ContactBase c
INNER JOIN AccountBase a
ON a.AccountId = c.AccountId
WHERE [Name] = @YourCompanyValue
)t
WHERE Occ=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources