November 24, 2015 at 4:36 am
Hi there
I have the following scenario that I'm trying to conquer, and I'd appreciate some help please.
My database has contacts that can have 1 or 2 addresses. Each contact has a Contact_ID and each address has an Address_ID. One of the Address_ID records will show an Organisation_Name and the other will not (or may not).
I need to produce a SQL statement to output only the record where the Organisation_Name is present. Also, if both Address_ID records have an Organisation_Name then only output one of them.
So, if I had the following, I would only want Address_ID=11 to be output
Contact_ID = 1, Address_ID=10 (no Organisation_Name on the record), Address_ID=11 (Organisation_Name shown on the record)
In the following scenario I would only want Address_ID=12 to be output
Contact_ID = 2, Address_ID=12 (Organisation_Name shown on the record), Address_ID=13 (Organisation_Name shown on the record)
I hope this makes sense.
Many thanks for your help.
Jon
November 24, 2015 at 4:43 am
Jon
This sounds fairly easy, but you haven't provided us enough information. Please post a CREATE TABLE script for each table involved, sample data in the form of INSERT statements and expected results based on the sample data.
John
November 24, 2015 at 4:59 am
Hi John
Thanks for your reply. The database already exists, so I'm unable to post what would be CREATE TABLE scripts (I think this is what you mean?). However, I've shown the relevant tables and columns below which I hope will be of help. The SQL that I would normally use the pull out such a list (ignoring the fact that I only want those people with ** is also shown).
Addresses table
Columns are: Contact_ID, Address_ID, Organisation_Name
Contacts table
Columns are: Contact_ID, PostalName, EmailAddress
SELECT Contacts.ContactID, Contacts.PostalName, Contacts.EmailAddress, Addresses.Address_ID, Addresses.Organisation_Name
FROM Contacts
INNER JOIN Addresses ON Contacts.ContactID=Addresses.ContactID
I would then manually de-dupe (yawn!) the output using Excel.
Please let me know if you require any further information.
Many thanks
Jon
November 24, 2015 at 5:09 am
Hi Jon
Just because a database has been created does not mean that CREATE TABLE scripts cannot. On the contrary, if the database did not already exist, you would find it most difficult to provide them.
Please take a look at the first link in my signature for details of how to post questions in order to get fast, coded answers.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 24, 2015 at 5:20 am
Hi both
Sorry, I didn't realise that (newbie!), but thanks for explaining it. I've now produced the CREATE TABLE scripts and have posted them below. As you'll see the scripts contain the correct names (e.g. ContactID, AddressID, CompanyName).
CONTACTS TABLE
USE [EnterpriseMRM]
GO
/****** Object: Table [dbo].[Contacts] Script Date: 11/24/2015 12:17:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contacts](
[ContactID] [nvarchar](50) NOT NULL,
[SecondKey] [nvarchar](50) NULL,
[ThirdKey] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL,
[Forenames] [nvarchar](50) NULL,
[Initials] [nvarchar](50) NULL,
[Title] [nvarchar](50) NULL,
[Suffixes] [nvarchar](50) NULL,
[Salutation] [nvarchar](50) NULL,
[PostalName] [nvarchar](50) NULL,
[Position] [nvarchar](50) NULL,
[CompanyName] [nvarchar](250) NULL,
[DateOfBirth] [datetime] NULL,
[RecordType] [nvarchar](50) NULL,
[CategoryCode] [nvarchar](50) NULL,
[CatEffectiveDate] [datetime] NULL,
[AreaCode] [nvarchar](50) NULL,
[RegionCode] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[MobileTelephone] [nvarchar](50) NULL,
[EMail] [nvarchar](255) NULL,
[WebSite] [nvarchar](50) NULL,
[Notes1] [ntext] NULL,
[Notes2] [ntext] NULL,
[Notes3] [ntext] NULL,
[Notes4] [ntext] NULL,
[Notes5] [ntext] NULL,
[Notes6] [ntext] NULL,
[Image] [image] NULL,
[Balance] [money] NULL,
[DateCreated] [datetime] NULL,
[UserCreated] [nvarchar](50) NULL,
[DateAmended] [datetime] NULL,
[UserAmended] [nvarchar](50) NULL,
[Inactive] [bit] NULL,
[Deleted] [bit] NULL,
[VATCode] [nvarchar](5) NULL,
[IgnoreDeclarations] [bit] NULL,
[Currency] [nvarchar](10) NULL,
[DeletedDate] [datetime] NULL,
[upsize_ts] [timestamp] NULL,
[CreditLimit] [int] NULL,
[Terms] [int] NULL,
[OnStop] [bit] NULL,
[DiscountPercentage] [float] NULL,
[OldCategory] [nvarchar](50) NULL,
[WebUserID] [nvarchar](50) NULL,
[PreviousCategory] [nvarchar](50) NULL,
[ImageFileName] [nvarchar](255) NULL,
[SubscriptionDiscount] [float] NULL,
[DDSubsOnly] [bit] NOT NULL,
CONSTRAINT [aaaaaContacts1_PK] PRIMARY KEY NONCLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contacts] WITH NOCHECK ADD CONSTRAINT [Contacts_FK01] FOREIGN KEY([CategoryCode])
REFERENCES [dbo].[Categories] ([CategoryCode])
GO
ALTER TABLE [dbo].[Contacts] NOCHECK CONSTRAINT [Contacts_FK01]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Balanc__117F9D94] DEFAULT (0) FOR [Balance]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Inacti__1273C1CD] DEFAULT (0) FOR [Inactive]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Delete__1367E606] DEFAULT (0) FOR [Deleted]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Ignore__145C0A3F] DEFAULT (0) FOR [IgnoreDeclarations]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__CONTACTS__Credit__10E07F16] DEFAULT (0) FOR [CreditLimit]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__CONTACTS__Terms__11D4A34F] DEFAULT (0) FOR [Terms]
GO
ALTER TABLE [dbo].[Contacts] ADD DEFAULT (0) FOR [OnStop]
GO
ALTER TABLE [dbo].[Contacts] ADD DEFAULT (0) FOR [DiscountPercentage]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF_Contacts_DDSubsOnly] DEFAULT (0) FOR [DDSubsOnly]
GO
ADDRESSES TABLE
USE [EnterpriseMRM]
GO
/****** Object: Table [dbo].[Addresses] Script Date: 11/24/2015 12:17:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Addresses](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [nvarchar](50) NULL,
[AddressType] [nvarchar](50) NULL,
[DefaultAddress] [bit] NULL,
[LinkedAddress] [bit] NULL,
[LinkedAddressID] [int] NULL,
[LinkTelephone] [bit] NULL,
[LinkFax] [bit] NULL,
[Position] [nvarchar](50) NULL,
[CompanyName] [nvarchar](250) NULL,
[UseDefaultCompany] [bit] NULL,
[Address1] [nvarchar](250) NULL,
[Address2] [nvarchar](50) NULL,
[Address3] [nvarchar](50) NULL,
[Town] [nvarchar](50) NULL,
[County] [nvarchar](50) NULL,
[PostCode] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[MailsortCode] [nvarchar](50) NULL,
[Telephone1] [nvarchar](50) NULL,
[Telephone2] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[DateCreated] [datetime] NULL,
[UserCreated] [nvarchar](50) NULL,
[DateAmended] [datetime] NULL,
[UserAmended] [nvarchar](50) NULL,
[upsize_ts] [timestamp] NULL,
[Notes] [ntext] NULL,
[LinkTelephone2] [bit] NULL,
[Department] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[AddressEmail] [nvarchar](255) NULL,
[LinkAddressEmail] [bit] NOT NULL,
[DefaultDeliveryAddress] [bit] NULL,
CONSTRAINT [aaaaaAddresses1_PK] PRIMARY KEY NONCLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Addresses] WITH NOCHECK ADD CONSTRAINT [Addresses_FK00] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contacts] ([ContactID])
GO
ALTER TABLE [dbo].[Addresses] NOCHECK CONSTRAINT [Addresses_FK00]
GO
ALTER TABLE [dbo].[Addresses] WITH NOCHECK ADD CONSTRAINT [Addresses_FK01] FOREIGN KEY([AddressType])
REFERENCES [dbo].[AddressTypes] ([AddressType])
GO
ALTER TABLE [dbo].[Addresses] NOCHECK CONSTRAINT [Addresses_FK01]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__Defau__78B3EFCA] DEFAULT (0) FOR [DefaultAddress]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__Linke__79A81403] DEFAULT (0) FOR [LinkedAddress]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__Linke__7A9C383C] DEFAULT (0) FOR [LinkedAddressID]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__LinkT__7B905C75] DEFAULT (0) FOR [LinkTelephone]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__LinkF__7C8480AE] DEFAULT (0) FOR [LinkFax]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__UseDe__7D78A4E7] DEFAULT (0) FOR [UseDefaultCompany]
GO
ALTER TABLE [dbo].[Addresses] ADD DEFAULT ('') FOR [LinkAddressEmail]
GO
ALTER TABLE [dbo].[Addresses] ADD DEFAULT ((0)) FOR [DefaultDeliveryAddress]
GO
Best wishes
Jon
November 24, 2015 at 5:22 am
Hi both
Sorry, I didn't realise that (newbie!), but thanks for explaining it. I've now produced the CREATE TABLE scripts and have posted them below. As you'll see the scripts contain the correct names (e.g. ContactID, AddressID, CompanyName).
CONTACTS TABLE
USE [EnterpriseMRM]
GO
/****** Object: Table [dbo].[Contacts] Script Date: 11/24/2015 12:17:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contacts](
[ContactID] [nvarchar](50) NOT NULL,
[SecondKey] [nvarchar](50) NULL,
[ThirdKey] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL,
[Forenames] [nvarchar](50) NULL,
[Initials] [nvarchar](50) NULL,
[Title] [nvarchar](50) NULL,
[Suffixes] [nvarchar](50) NULL,
[Salutation] [nvarchar](50) NULL,
[PostalName] [nvarchar](50) NULL,
[Position] [nvarchar](50) NULL,
[CompanyName] [nvarchar](250) NULL,
[DateOfBirth] [datetime] NULL,
[RecordType] [nvarchar](50) NULL,
[CategoryCode] [nvarchar](50) NULL,
[CatEffectiveDate] [datetime] NULL,
[AreaCode] [nvarchar](50) NULL,
[RegionCode] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[MobileTelephone] [nvarchar](50) NULL,
[EMail] [nvarchar](255) NULL,
[WebSite] [nvarchar](50) NULL,
[Notes1] [ntext] NULL,
[Notes2] [ntext] NULL,
[Notes3] [ntext] NULL,
[Notes4] [ntext] NULL,
[Notes5] [ntext] NULL,
[Notes6] [ntext] NULL,
[Image] [image] NULL,
[Balance] [money] NULL,
[DateCreated] [datetime] NULL,
[UserCreated] [nvarchar](50) NULL,
[DateAmended] [datetime] NULL,
[UserAmended] [nvarchar](50) NULL,
[Inactive] [bit] NULL,
[Deleted] [bit] NULL,
[VATCode] [nvarchar](5) NULL,
[IgnoreDeclarations] [bit] NULL,
[Currency] [nvarchar](10) NULL,
[DeletedDate] [datetime] NULL,
[upsize_ts] [timestamp] NULL,
[CreditLimit] [int] NULL,
[Terms] [int] NULL,
[OnStop] [bit] NULL,
[DiscountPercentage] [float] NULL,
[OldCategory] [nvarchar](50) NULL,
[WebUserID] [nvarchar](50) NULL,
[PreviousCategory] [nvarchar](50) NULL,
[ImageFileName] [nvarchar](255) NULL,
[SubscriptionDiscount] [float] NULL,
[DDSubsOnly] [bit] NOT NULL,
CONSTRAINT [aaaaaContacts1_PK] PRIMARY KEY NONCLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contacts] WITH NOCHECK ADD CONSTRAINT [Contacts_FK01] FOREIGN KEY([CategoryCode])
REFERENCES [dbo].[Categories] ([CategoryCode])
GO
ALTER TABLE [dbo].[Contacts] NOCHECK CONSTRAINT [Contacts_FK01]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Balanc__117F9D94] DEFAULT (0) FOR [Balance]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Inacti__1273C1CD] DEFAULT (0) FOR [Inactive]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Delete__1367E606] DEFAULT (0) FOR [Deleted]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__Contacts__Ignore__145C0A3F] DEFAULT (0) FOR [IgnoreDeclarations]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__CONTACTS__Credit__10E07F16] DEFAULT (0) FOR [CreditLimit]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF__CONTACTS__Terms__11D4A34F] DEFAULT (0) FOR [Terms]
GO
ALTER TABLE [dbo].[Contacts] ADD DEFAULT (0) FOR [OnStop]
GO
ALTER TABLE [dbo].[Contacts] ADD DEFAULT (0) FOR [DiscountPercentage]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF_Contacts_DDSubsOnly] DEFAULT (0) FOR [DDSubsOnly]
GO
ADDRESSES TABLE
USE [EnterpriseMRM]
GO
/****** Object: Table [dbo].[Addresses] Script Date: 11/24/2015 12:17:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Addresses](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [nvarchar](50) NULL,
[AddressType] [nvarchar](50) NULL,
[DefaultAddress] [bit] NULL,
[LinkedAddress] [bit] NULL,
[LinkedAddressID] [int] NULL,
[LinkTelephone] [bit] NULL,
[LinkFax] [bit] NULL,
[Position] [nvarchar](50) NULL,
[CompanyName] [nvarchar](250) NULL,
[UseDefaultCompany] [bit] NULL,
[Address1] [nvarchar](250) NULL,
[Address2] [nvarchar](50) NULL,
[Address3] [nvarchar](50) NULL,
[Town] [nvarchar](50) NULL,
[County] [nvarchar](50) NULL,
[PostCode] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[MailsortCode] [nvarchar](50) NULL,
[Telephone1] [nvarchar](50) NULL,
[Telephone2] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[DateCreated] [datetime] NULL,
[UserCreated] [nvarchar](50) NULL,
[DateAmended] [datetime] NULL,
[UserAmended] [nvarchar](50) NULL,
[upsize_ts] [timestamp] NULL,
[Notes] [ntext] NULL,
[LinkTelephone2] [bit] NULL,
[Department] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[AddressEmail] [nvarchar](255) NULL,
[LinkAddressEmail] [bit] NOT NULL,
[DefaultDeliveryAddress] [bit] NULL,
CONSTRAINT [aaaaaAddresses1_PK] PRIMARY KEY NONCLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Addresses] WITH NOCHECK ADD CONSTRAINT [Addresses_FK00] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contacts] ([ContactID])
GO
ALTER TABLE [dbo].[Addresses] NOCHECK CONSTRAINT [Addresses_FK00]
GO
ALTER TABLE [dbo].[Addresses] WITH NOCHECK ADD CONSTRAINT [Addresses_FK01] FOREIGN KEY([AddressType])
REFERENCES [dbo].[AddressTypes] ([AddressType])
GO
ALTER TABLE [dbo].[Addresses] NOCHECK CONSTRAINT [Addresses_FK01]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__Defau__78B3EFCA] DEFAULT (0) FOR [DefaultAddress]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__Linke__79A81403] DEFAULT (0) FOR [LinkedAddress]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__Linke__7A9C383C] DEFAULT (0) FOR [LinkedAddressID]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__LinkT__7B905C75] DEFAULT (0) FOR [LinkTelephone]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__LinkF__7C8480AE] DEFAULT (0) FOR [LinkFax]
GO
ALTER TABLE [dbo].[Addresses] ADD CONSTRAINT [DF__Addresses__UseDe__7D78A4E7] DEFAULT (0) FOR [UseDefaultCompany]
GO
ALTER TABLE [dbo].[Addresses] ADD DEFAULT ('') FOR [LinkAddressEmail]
GO
ALTER TABLE [dbo].[Addresses] ADD DEFAULT ((0)) FOR [DefaultDeliveryAddress]
GO
Best wishes
Jon
November 24, 2015 at 5:26 am
Unless I'm missing something, you won't need any IF statements. Instead, you want to run your query and filter on the contacts.organization_name column.
To generate the DDL for a table, you can right-click the table name...Script Table As...Create To...New Query window.
You can then write insert statements for it to populate some sample data. We don't want your real data, just sample data.
November 24, 2015 at 5:47 am
Hi all
Thanks for your continued help (I know that it's probably like pulling teeth!).
Attached are two text files showing the relevant table scripts as requested (I'm unable to post them just by pasting the scripts unfortunately).
As for the sample data, would an e.g. Excel spreadsheet of the sample output be sufficient?
Many thanks
Jon
November 24, 2015 at 5:54 am
jon.clay 91459 (11/24/2015)
Hi allThanks for your continued help (I know that it's probably like pulling teeth!).
Attached are two text files showing the relevant table scripts as requested (I'm unable to post them just by pasting the scripts unfortunately).
As for the sample data, would an e.g. Excel spreadsheet of the sample output be sufficient?
Many thanks
Jon
Found no Organization... Do you mean
[CompanyName] [nvarchar](250) NULL,
?
November 24, 2015 at 6:02 am
Hi there
Yes, CompanyName.
I tried to make it easier to explain when I was doing it before, but I seem to have made things more confusing, my apologies.
Best wishes
Jon
November 24, 2015 at 6:17 am
Ok, you need to decide, which of possible relevant Addresses is first
SELECT C.ContactID, C.PostalName, Addresses.AddressID, Addresses.CompanyName
FROM Contacts C
CROSS APPLY(
SELECT TOP(1) *
FROM Addresses A
WHERE C.ContactID=A.ContactID AND A.CompanyName IS NOT NULL
ORDER BY A.CompanyName -- change as needed
) Addresses
November 24, 2015 at 6:19 am
And, depending on requirements if you need contacts with no relevant addresses too, change CROSS APPLY to OUTER APPLY.
November 24, 2015 at 6:37 am
Fantastic - thank you very much.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply