HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.newusers » Need help with subqueries
Topic: Need help with subqueries
I have (the following) two tables for which I need a result consisting of
[Contact Category] and [Checked]
Here's what I'm trying to do:
There are several default Contact categories. A user is able to create
custom categories. A user sees only the default categories and his own
custom categories. A user assigns one or more categories to a contact (also
owned exclusively by the user).
On the contacts' edit page, I want to present a checkbox list of all
categories available to the user, with the checkboxes checked for those that
have already been assigned to the contact.
(I am not sure if I really need the 'Checked' column in the tables)
Input parameters are:
UserId
ContactId
The result should include:
- all rows where [Contacts_Categories.CategoryId] = ContactId
- all rows where [ContactCategories.UserId] = UserId - but not rows that are
already returned above
- all remaining rows from [ContactCategories] where
[ContactCategories.Default] = True - but not rows that are already returned
above
I have tried my hand at SQL queries/subqueries/correlated subqueries for
three days now and have yet to write the right query.
Any help will be greatly appreciated
Thank you!
HF Schreiner
***** Tables /w test data *****
CREATE TABLE [dbo].[copy_of_rbContactCategories](
[ContactCategoryId] [uniqueidentifier] NOT NULL,
[ContactCategory] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[IsDefault] [bit] NULL,
[ListOrder] [smallint] NULL,
[UserId] [uniqueidentifier] NULL,
[Checked] [bit] NOT NULL,
CONSTRAINT [PK_copy_of_rbContactCategories] PRIMARY KEY CLUSTERED
(
[ContactCategoryId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO copy_of_rbContactCategories (ContactCategoryId, ContactCategory,
IsDefault, ListOrder, UserId, Checked)
VALUES ('dc9738b4-6cd5-49fa-9fe2-0d138df93983', 'Christmas Cards', 'False',
'1', '6ebbcb1c-c5ac-4428-a7bf-07db953089ae', 'False')
INSERT INTO copy_of_rbContactCategories (ContactCategoryId, ContactCategory,
IsDefault, ListOrder, UserId, Checked)
VALUES ('8cc69560-adac-4493-94b2-6ddce66affe1', 'Active', 'True', '1',
'NULL', 'False')
INSERT INTO copy_of_rbContactCategories (ContactCategoryId, ContactCategory,
IsDefault, ListOrder, UserId, Checked)
VALUES ('41d11ff0-74d9-4383-9b37-acb386fea5f8', 'Address incomplete or
missing', 'True', '2', 'NULL', 'False')
INSERT INTO copy_of_rbContactCategories (ContactCategoryId, ContactCategory,
IsDefault, ListOrder, UserId, Checked)
VALUES ('3069b58e-a095-4556-9595-ba9fba65638f', 'Letter Opt-Out', 'True',
'4', 'NULL', 'False')
INSERT INTO copy_of_rbContactCategories (ContactCategoryId, ContactCategory,
IsDefault, ListOrder, UserId, Checked)
VALUES ('04f2f4ea-5de7-4cfc-b662-d70c614342c8', 'Inactive', 'True', '3',
'NULL', 'False')
INSERT INTO copy_of_rbContactCategories (ContactCategoryId, ContactCategory,
IsDefault, ListOrder, UserId, Checked)
VALUES ('6306b738-bd41-45d3-98c8-eacbf0eba085', 'Birthday Cards', 'False',
'2', '6ebbcb1c-c5ac-4428-a7bf-07db953089ae', 'False')
INSERT INTO copy_of_rbContactCategories (ContactCategoryId, ContactCategory,
IsDefault, ListOrder, UserId, Checked)
VALUES ('6ee0959e-a122-43fc-9253-f225df24e467', 'Needs fixing (see note)',
'True', '5', NULL, 'False')
CREATE TABLE [dbo].[copy_of_rbContacts_Categories](
[ContactCategoryId] [uniqueidentifier] NOT NULL,
[ContactId] [uniqueidentifier] NOT NULL,
[Checked] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[copy_of_rbContacts_Categories] WITH CHECK ADD CONSTRAINT
[FK_copy_of_rbContacts_Categories_copy_of_rbContactCategories] FOREIGN
KEY([ContactCategoryId])
REFERENCES [dbo].[copy_of_rbContactCategories] ([ContactCategoryId])
GO
ALTER TABLE [dbo].[copy_of_rbContacts_Categories] CHECK CONSTRAINT
[FK_copy_of_rbContacts_Categories_copy_of_rbContactCategories]
INSERT INTO copy_of_rbContacts_Categories(ContactCategoryId, ContactId,
Checked)
VALUES ('8cc69560-adac-4493-94b2-6ddce66affe1',
'8ca165a2-d759-4cb5-acf5-3c2c2cae3320', 'True')
INSERT INTO copy_of_rbContacts_Categories(ContactCategoryId, ContactId,
Checked)
VALUES ('8cc69560-adac-4493-94b2-6ddce66affe1',
'b3afb1a1-dc82-419f-935b-9349de2f53ae', 'True')
INSERT INTO copy_of_rbContacts_Categories(ContactCategoryId, ContactId,
Checked)
VALUES ('8cc69560-adac-4493-94b2-6ddce66affe1',
'2b5c9131-2cd7-41d4-b351-e74bf5a0bb6a', 'True')
INSERT INTO copy_of_rbContacts_Categories(ContactCategoryId, ContactId,
Checked)
VALUES ('dc9738b4-6cd5-49fa-9fe2-0d138df93983',
'8ca165a2-d759-4cb5-acf5-3c2c2cae3320', 'True')
INSERT INTO copy_of_rbContacts_Categories(ContactCategoryId, ContactId,
Checked)
VALUES ('8cc69560-adac-4493-94b2-6ddce66affe1',
'882aa0e3-fb70-421b-8e61-b351c6e4966f', 'True')
Replies below ↓
Replies
Re: Need help with subqueries
First, thanks for providing the DDL and INSERT statements with sample data.
That makes everything much easier. It would also be helpful if in addition
to the English description of what you wanted, you also showed the result
you wanted with that sample data. That often makes it more clear what you
are looking for.
Also, you said,
"The result should include: - all rows where
[Contacts_Categories.CATEGORYID] = ContactId"
I assume you meant
"The result should include: - all rows where [Contacts_Categories.CONTACTID]
= ContactId"
I think the following will give as start to what you want. It returns the
CategoryId, CategoryName, and the reason this category was included.
-- Specify the UserID and ContactId we are looking for
Declare @UserID uniqueidentifier
Declare @ContactId uniqueidentifier
Set @UserID = '6EBBCB1C-C5AC-4428-A7BF-07DB953089AE'
Set @ContactId = '8CA165A2-D759-4CB5-ACF5-3C2C2CAE3320'
Select c.ContactCategoryId, c.ContactCategory,
Case When cc.ContactID Is Not Null Then 'In Contacts_Categories For
Contact '
+ Cast(@ContactID As char(36))
When c.UserID = @UserID Then 'Belongs to User '
+ Cast(@UserID As char(36))
Else 'Is default' End As ReasonIncluded
From [dbo].[copy_of_rbContactCategories] c
Left Outer Join [dbo].[copy_of_rbContacts_Categories] cc On
c.ContactCategoryId = cc.ContactCategoryId
And cc.ContactId = @ContactID
Where c.UserID = @UserID
Or c.IsDefault = 1
Tom
"HF Schreiner" <NoOne@Home.Today> wrote in message
news:ubnUI4cyIHA.1236@TK2MSFTNGP02.phx.gbl...
>I have (the following) two tables for which I need a result consisting of
> [Contact Category] and [Checked]
>
> Here's what I'm trying to do:
> There are several default Contact categories. A user is able to create
> custom categories. A user sees only the default categories and his own
> custom categories. A user assigns one or more categories to a contact
> (also
> owned exclusively by the user).
> On the contacts' edit page, I want to present a checkbox list of all
> categories available to the user, with the checkboxes checked for those
> that
> have already been assigned to the contact.
> (I am not sure if I really need the 'Checked' column in the tables)
>
> Input parameters are:
> UserId
> ContactId
>
> The result should include:
> - all rows where [Contacts_Categories.CategoryId] = ContactId
> - all rows where [ContactCategories.UserId] = UserId - but not rows that
> are
> already returned above
> - all remaining rows from [ContactCategories] where
> [ContactCategories.Default] = True - but not rows that are already
> returned
> above
>
> I have tried my hand at SQL queries/subqueries/correlated subqueries for
> three days now and have yet to write the right query.
> Any help will be greatly appreciated
>
> Thank you!
> HF Schreiner
>
<snip>
Re: Need help with subqueries
Thank you so very much, Tom!
You have done with a few instructions what I could not do with dozens... I'm
learning, though!
Your assumption about: "[Contacts_Categories.CATEGORYID] = ContactId" was
correct
Here is the result I wanted - using the test data:
Category Checked
Christmas Cards true
Active true
Address incomplete or missing false
Letter Opt-Out false
Inactive false
Birthday Cards false
Needs fixing (see note) false
and the modified query:
Declare @UserID uniqueidentifier
Declare @ContactId uniqueidentifier
Set @UserID = '6EBBCB1C-C5AC-4428-A7BF-07DB953089AE'
Set @ContactId = '8CA165A2-D759-4CB5-ACF5-3C2C2CAE3320'
Select c.ContactCategory,
Case When cc.ContactID Is Not Null Then 'true '
When c.UserID = @UserID Then 'false'
Else 'false' End As Checked
From [dbo].[copy_of_rbContactCategories] c
Left Outer Join [dbo].[copy_of_rbContacts_Categories] cc On
c.ContactCategoryId = cc.ContactCategoryId
And cc.ContactId = @ContactID
Where c.UserID = @UserID
Or c.IsDefault = 1
Thanks again,
Fred Schreiner
"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:Of0BsddyIHA.420@TK2MSFTNGP02.phx.gbl...
> First, thanks for providing the DDL and INSERT statements with sample
> data. That makes everything much easier. It would also be helpful if in
> addition to the English description of what you wanted, you also showed
> the result you wanted with that sample data. That often makes it more
> clear what you are looking for.
>
> Also, you said,
> "The result should include: - all rows where
> [Contacts_Categories.CATEGORYID] = ContactId"
> I assume you meant
> "The result should include: - all rows where
> [Contacts_Categories.CONTACTID] = ContactId"
>
> I think the following will give as start to what you want. It returns the
> CategoryId, CategoryName, and the reason this category was included.
>
> -- Specify the UserID and ContactId we are looking for
>
> Declare @UserID uniqueidentifier
> Declare @ContactId uniqueidentifier
> Set @UserID = '6EBBCB1C-C5AC-4428-A7BF-07DB953089AE'
> Set @ContactId = '8CA165A2-D759-4CB5-ACF5-3C2C2CAE3320'
>
> Select c.ContactCategoryId, c.ContactCategory,
> Case When cc.ContactID Is Not Null Then 'In Contacts_Categories For
> Contact '
> + Cast(@ContactID As char(36))
> When c.UserID = @UserID Then 'Belongs to User '
> + Cast(@UserID As char(36))
> Else 'Is default' End As ReasonIncluded
> From [dbo].[copy_of_rbContactCategories] c
> Left Outer Join [dbo].[copy_of_rbContacts_Categories] cc On
> c.ContactCategoryId = cc.ContactCategoryId
> And cc.ContactId = @ContactID
> Where c.UserID = @UserID
> Or c.IsDefault = 1
>
> Tom
>
> "HF Schreiner" <NoOne@Home.Today> wrote in message
> news:ubnUI4cyIHA.1236@TK2MSFTNGP02.phx.gbl...
>>I have (the following) two tables for which I need a result consisting of
>> [Contact Category] and [Checked]
>>
>> Here's what I'm trying to do:
>> There are several default Contact categories. A user is able to create
>> custom categories. A user sees only the default categories and his own
>> custom categories. A user assigns one or more categories to a contact
>> (also
>> owned exclusively by the user).
>> On the contacts' edit page, I want to present a checkbox list of all
>> categories available to the user, with the checkboxes checked for those
>> that
>> have already been assigned to the contact.
>> (I am not sure if I really need the 'Checked' column in the tables)
>>
>> Input parameters are:
>> UserId
>> ContactId
>>
>> The result should include:
>> - all rows where [Contacts_Categories.CategoryId] = ContactId
>> - all rows where [ContactCategories.UserId] = UserId - but not rows that
>> are
>> already returned above
>> - all remaining rows from [ContactCategories] where
>> [ContactCategories.Default] = True - but not rows that are already
>> returned
>> above
>>
>> I have tried my hand at SQL queries/subqueries/correlated subqueries for
>> three days now and have yet to write the right query.
>> Any help will be greatly appreciated
>>
>> Thank you!
>> HF Schreiner
>>
> <snip>
>