January 11, 2016 at 8:22 am
Here is my requirement
Need to get a list of all members with dates of birth from 11/15/13-10/15/14.
In addition to the members name, need to get parents names, phone number and e-mail.
All details are stored in dbo.name table. below query gets me the results for first part of requirement.
I am lost how to include the parent field to this query. The field that differentiate between parent n child is, member_type...either NM-CH or M-CH is child, NM-F or MF will give the family(Parent) details.
select distinct
Name.ID,
Name.FULL_NAME,
Name.FIRST_NAME,
Name.LAST_FIRST,
Name.CO_ID,
Name.MEMBER_TYPE,
Name.STATUS,
Name.FULL_ADDRESS,
Name.EMAIL,
Name.BIRTH_DATE
From dbo.Name
Where BIRTH_DATE >= '20131114'
and BIRTH_DATE < '20141016'
order by BIRTH_DATE;
Regards,
SQLisAwe5oMe.
January 11, 2016 at 8:31 am
Table definitions and sample data please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2016 at 8:34 am
GilaMonster (1/11/2016)
Table definitions and sample data please.
Hi Gail,
I have attached the create script for dbo.Name
Regards,
SQLisAwe5oMe.
January 11, 2016 at 8:40 am
For anybody else not wanting to download the text file here is the contents.
CREATE TABLE [dbo].[Name](
[ID] [varchar](10) NOT NULL,
[ORG_CODE] [varchar](5) NOT NULL,
[MEMBER_TYPE] [varchar](5) NOT NULL,
[CATEGORY] [varchar](5) NOT NULL,
[STATUS] [varchar](5) NOT NULL,
[MAJOR_KEY] [varchar](15) NOT NULL,
[CO_ID] [varchar](10) NOT NULL,
[LAST_FIRST] [varchar](70) NOT NULL,
[COMPANY_SORT] [varchar](30) NOT NULL,
[BT_ID] [varchar](10) NOT NULL,
[DUP_MATCH_KEY] [varchar](20) NOT NULL,
[FULL_NAME] [varchar](70) NOT NULL,
[TITLE] [varchar](80) NOT NULL,
[COMPANY] [varchar](80) NOT NULL,
[FULL_ADDRESS] [varchar](255) NOT NULL,
[PREFIX] [varchar](25) NOT NULL,
[FIRST_NAME] [varchar](20) NOT NULL,
[MIDDLE_NAME] [varchar](20) NOT NULL,
[LAST_NAME] [varchar](30) NOT NULL,
[SUFFIX] [varchar](10) NOT NULL,
[DESIGNATION] [varchar](20) NOT NULL,
[INFORMAL] [varchar](20) NOT NULL,
[WORK_PHONE] [varchar](25) NOT NULL,
[HOME_PHONE] [varchar](25) NOT NULL,
[FAX] [varchar](25) NOT NULL,
[TOLL_FREE] [varchar](25) NOT NULL,
[CITY] [varchar](40) NOT NULL,
[STATE_PROVINCE] [varchar](15) NOT NULL,
[ZIP] [varchar](10) NOT NULL,
[COUNTRY] [varchar](25) NOT NULL,
[MAIL_CODE] [varchar](5) NOT NULL,
[CRRT] [varchar](40) NOT NULL,
[BAR_CODE] [varchar](14) NOT NULL,
[COUNTY] [varchar](30) NOT NULL,
[MAIL_ADDRESS_NUM] [int] NOT NULL,
[BILL_ADDRESS_NUM] [int] NOT NULL,
[GENDER] [varchar](1) NOT NULL,
[BIRTH_DATE] [datetime] NULL,
[US_CONGRESS] [varchar](20) NOT NULL,
[STATE_SENATE] [varchar](20) NOT NULL,
[STATE_HOUSE] [varchar](20) NOT NULL,
[SIC_CODE] [varchar](10) NOT NULL,
[CHAPTER] [varchar](15) NOT NULL,
[FUNCTIONAL_TITLE] [varchar](50) NOT NULL,
[CONTACT_RANK] [int] NOT NULL,
[MEMBER_RECORD] [bit] NOT NULL,
[COMPANY_RECORD] [bit] NOT NULL,
[JOIN_DATE] [datetime] NULL,
[SOURCE_CODE] [varchar](40) NOT NULL,
[PAID_THRU] [datetime] NULL,
[MEMBER_STATUS] [varchar](5) NOT NULL,
[MEMBER_STATUS_DATE] [datetime] NULL,
[PREVIOUS_MT] [varchar](5) NOT NULL,
[MT_CHANGE_DATE] [datetime] NULL,
[CO_MEMBER_TYPE] [varchar](5) NOT NULL,
[EXCLUDE_MAIL] [bit] NOT NULL,
[EXCLUDE_DIRECTORY] [bit] NOT NULL,
[DATE_ADDED] [datetime] NULL,
[LAST_UPDATED] [datetime] NULL,
[UPDATED_BY] [varchar](60) NOT NULL,
[INTENT_TO_EDIT] [varchar](80) NOT NULL,
[ADDRESS_NUM_1] [int] NOT NULL,
[ADDRESS_NUM_2] [int] NOT NULL,
[ADDRESS_NUM_3] [int] NOT NULL,
[varchar](100) NOT NULL,
[WEBSITE] [varchar](255) NOT NULL,
[TIME_STAMP] [timestamp] NULL,
[SHIP_ADDRESS_NUM] [int] NOT NULL,
[DISPLAY_CURRENCY] [varchar](3) NOT NULL,
CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_ID] DEFAULT ('') FOR [ID]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_ORG_CODE] DEFAULT ('') FOR [ORG_CODE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_MEMBER_TYPE] DEFAULT ('') FOR [MEMBER_TYPE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_CATEGORY] DEFAULT ('') FOR [CATEGORY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_STATUS] DEFAULT ('') FOR [STATUS]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_MAJOR_KEY] DEFAULT ('') FOR [MAJOR_KEY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_CO_ID] DEFAULT ('') FOR [CO_ID]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_LAST_FIRST] DEFAULT ('') FOR [LAST_FIRST]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_COMPANY_SORT] DEFAULT ('') FOR [COMPANY_SORT]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_BT_ID] DEFAULT ('') FOR [BT_ID]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_DUP_MATCH_KEY] DEFAULT ('') FOR [DUP_MATCH_KEY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_FULL_NAME] DEFAULT ('') FOR [FULL_NAME]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_TITLE] DEFAULT ('') FOR [TITLE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_COMPANY] DEFAULT ('') FOR [COMPANY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_FULL_ADDRESS] DEFAULT ('') FOR [FULL_ADDRESS]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_PREFIX] DEFAULT ('') FOR [PREFIX]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_FIRST_NAME] DEFAULT ('') FOR [FIRST_NAME]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_MIDDLE_NAME] DEFAULT ('') FOR [MIDDLE_NAME]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_LAST_NAME] DEFAULT ('') FOR [LAST_NAME]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_SUFFIX] DEFAULT ('') FOR [SUFFIX]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_DESIGNATION] DEFAULT ('') FOR [DESIGNATION]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_INFORMAL] DEFAULT ('') FOR [INFORMAL]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_WORK_PHONE] DEFAULT ('') FOR [WORK_PHONE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_HOME_PHONE] DEFAULT ('') FOR [HOME_PHONE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_FAX] DEFAULT ('') FOR [FAX]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_TOLL_FREE] DEFAULT ('') FOR [TOLL_FREE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_CITY] DEFAULT ('') FOR [CITY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_STATE_PROVINCE] DEFAULT ('') FOR [STATE_PROVINCE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_ZIP] DEFAULT ('') FOR [ZIP]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_COUNTRY] DEFAULT ('') FOR [COUNTRY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_MAIL_CODE] DEFAULT ('') FOR [MAIL_CODE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_CRRT] DEFAULT ('') FOR [CRRT]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_BAR_CODE] DEFAULT ('') FOR [BAR_CODE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_COUNTY] DEFAULT ('') FOR [COUNTY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_MAIL_ADDRESS_NUM] DEFAULT ((0)) FOR [MAIL_ADDRESS_NUM]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_BILL_ADDRESS_NUM] DEFAULT ((0)) FOR [BILL_ADDRESS_NUM]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_GENDER] DEFAULT ('') FOR [GENDER]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_US_CONGRESS] DEFAULT ('') FOR [US_CONGRESS]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_STATE_SENATE] DEFAULT ('') FOR [STATE_SENATE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_STATE_HOUSE] DEFAULT ('') FOR [STATE_HOUSE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_SIC_CODE] DEFAULT ('') FOR [SIC_CODE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_CHAPTER] DEFAULT ('') FOR [CHAPTER]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_FUNCTIONAL_TITLE] DEFAULT ('') FOR [FUNCTIONAL_TITLE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_CONTACT_RANK] DEFAULT ((0)) FOR [CONTACT_RANK]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_MEMBER_RECORD] DEFAULT ((0)) FOR [MEMBER_RECORD]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_COMPANY_RECORD] DEFAULT ((0)) FOR [COMPANY_RECORD]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_SOURCE_CODE] DEFAULT ('') FOR [SOURCE_CODE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_MEMBER_STATUS] DEFAULT ('') FOR [MEMBER_STATUS]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_PREVIOUS_MT] DEFAULT ('') FOR [PREVIOUS_MT]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_CO_MEMBER_TYPE] DEFAULT ('') FOR [CO_MEMBER_TYPE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_EXCLUDE_MAIL] DEFAULT ((0)) FOR [EXCLUDE_MAIL]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_EXCLUDE_DIRECTORY] DEFAULT ((0)) FOR [EXCLUDE_DIRECTORY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_UPDATED_BY] DEFAULT ('') FOR [UPDATED_BY]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_INTENT_TO_EDIT] DEFAULT ('') FOR [INTENT_TO_EDIT]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_ADDRESS_NUM_1] DEFAULT ((0)) FOR [ADDRESS_NUM_1]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_ADDRESS_NUM_2] DEFAULT ((0)) FOR [ADDRESS_NUM_2]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_ADDRESS_NUM_3] DEFAULT ((0)) FOR [ADDRESS_NUM_3]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_EMAIL] DEFAULT ('') FOR
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_WEBSITE] DEFAULT ('') FOR [WEBSITE]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_SHIP_ADDRESS_NUM] DEFAULT ((0)) FOR [SHIP_ADDRESS_NUM]
GO
ALTER TABLE [dbo].[Name] ADD CONSTRAINT [DF_Name_DISPLAY_CURRENCY] DEFAULT ('') FOR [DISPLAY_CURRENCY]
GO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2016 at 8:44 am
You desperately need to do some reading about database design and normalization. This table is a mess and appear to be lacking any attempt at normalization.
As to the question at hand...you still haven't provided sample data. Even more challenging is you talk about the parent but there are no columns in your table that indicate a parent. Help us help you by providing all the details required to answer the question at hand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2016 at 9:23 am
Sean Lange (1/11/2016)
You desperately need to do some reading about database design and normalization. This table is a mess and appear to be lacking any attempt at normalization.As to the question at hand...you still haven't provided sample data. Even more challenging is you talk about the parent but there are no columns in your table that indicate a parent. Help us help you by providing all the details required to answer the question at hand.
I am not really sure what exactly I can do at this point in regards to database design and normalization. This is a vendor app and already in Production.
I found a view which I provide to you earlier, which has parents details. I am attaching the create script for the view.
I believe, I can join this view and name table to get the details.....still struggling.
Regards,
SQLisAwe5oMe.
January 11, 2016 at 9:42 am
SQLisAwE5OmE (1/11/2016)
Sean Lange (1/11/2016)
You desperately need to do some reading about database design and normalization. This table is a mess and appear to be lacking any attempt at normalization.As to the question at hand...you still haven't provided sample data. Even more challenging is you talk about the parent but there are no columns in your table that indicate a parent. Help us help you by providing all the details required to answer the question at hand.
I am not really sure what exactly I can do at this point in regards to database design and normalization. This is a vendor app and already in Production.
I found a view which I provide to you earlier, which has parents details. I am attaching the create script for the view.
I believe, I can join this view and name table to get the details.....still struggling.
I feel your pain when it comes to vendor apps and horrible design. There is usually nothing that can be done there.
Here is the contents of the view text file for somebody else who might be coming along.
CREATE VIEW [dbo].[vw_csys_NurserySchool]
AS
SELECT n.ID, n.FIRST_NAME, n.MIDDLE_NAME, n.LAST_NAME, n.FULL_NAME, n.LAST_FIRST, n.CO_ID, n.COMPANY, n.EMAIL, n.HOME_PHONE, n.JOIN_DATE,
n.TOLL_FREE AS Cell, na.ADDRESS_1, na.ADDRESS_2, na.CITY, na.STATE_PROVINCE, na.ZIP, na.FULL_ADDRESS, i.GENDER, i.AGE, i.BIRTHDATE,
nmp.ID AS Parent1_id, nmp.status as Parent1_STATUS, nmp.FIRST_NAME AS Parent1_FirstName, nmp.MIDDLE_NAME AS Parent1_MiddleName,
nmp.LAST_NAME AS Parent1_LastName, nmp.FULL_NAME AS Parent1_FullName, nmp.LAST_FIRST AS Parent1_LastFirst,
nmp.EMAIL AS Parent1_Email, nmp.HOME_PHONE AS Parent1_HomePhome, nmp.WORK_PHONE AS Parent1_WorkPhone,
nmp.TOLL_FREE AS Parent1_Cell, imp.GENDER AS Parent1_Gender, nsp.ID AS Parent2_ID, nsp.status as Parent2_STATUS, nsp.FIRST_NAME AS Parent2_FirstName,
nsp.MIDDLE_NAME AS Parent2_MiddleName, nsp.LAST_NAME AS Parent2_LastName, nsp.FULL_NAME AS Parent2_FullName,
nsp.LAST_FIRST AS Parent2_LastFirst, nsp.EMAIL AS Parent2_Email, nsp.HOME_PHONE AS Parent2_HomePhome,
nsp.WORK_PHONE AS Parent2_WorkPhone, nsp.TOLL_FREE AS Parent2_Cell, isp.GENDER AS Parent2_Gender, ns.YEAR, ns.ROOM_ASSIGNMENT,
ns.MEDICAL_FORMS, ns.NS_PROGRAM, ns.NS_NOTES, ns.NS_EXPULSION, ns.NS_DYFS, ns.NS_EMERGENCY, ns.NS_HISTORY,
ns.NS_MORNINGCARE, ns.NS_LUNCH,ns.NS_LUNCH_BILL_CODE, ns.NS_AFTERCARE, ns.NS_BUSAM, ns.NS_BUSPM, ns.NS_BUSAM_DRIVER, ns.NS_BUSPM_DRIVER,
ns.NS_HOTLUNCH, ns.APPLICATION_STATUS, ns.NS_TEACHER, ns.EMAIL_FORM, s.PAID_THRU, ns.DATE_RECEIVED,ns.NS_OPTIONS,ns.NS_SUMMER_CHKLST,ns.IMMUNIZATION,ns.NEW_FAM,ns.PLACEMENT_NOTES,ns.UNIV_CHILD_RECORD,ns.NS_MORNING_BILL_CODE,ns.NS_AFTER_BILL_CODE,ns.NEW_STD,ns.ACCOUNTING_NOTES,ns.NS_PA_GIFT
FROM dbo.NURSERY_SCHOOL AS ns INNER JOIN
dbo.Name AS n ON ns.ID = n.ID INNER JOIN
dbo.Individual_JCCOTP AS i ON i.ID = n.ID INNER JOIN
dbo.Gen_Tables AS gt ON gt.CODE = ns.NS_PROGRAM AND gt.TABLE_NAME = 'NS_PROGRAM' INNER JOIN
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM LEFT OUTER JOIN
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN
dbo.Individual_JCCOTP AS imp ON imp.ID = nmp.ID LEFT OUTER JOIN
dbo.Individual_JCCOTP AS isp ON isp.ID = nsp.ID left outer join
dbo.Subscriptions as s on
s.id=ns.ID and s.product_code=ns.ns_program
WHERE (ns.YEAR <> '') AND (ns.NS_PROGRAM <> '')
You have to remember that each question you ask needs to stand on its own. I am not going to go dig through your other questions looking for clues to help answer this question. Aside from this view also referencing the Name table I don't see how this answers the question of a parent. We also STILL don't have any sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2016 at 10:14 am
I would think it would be something like below.
Note: I assumed from the context that you need to match only the *child's* birthday, even though you said "all members". If not, naturally remove the "n.MEMBER_TYPE LIKE '%CH'" condition:
SELECT --columns,...
FROM dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 11, 2016 at 1:46 pm
ScottPletcher (1/11/2016)
I would think it would be something like below.Note: I assumed from the context that you need to match only the *child's* birthday, even though you said "all members". If not, naturally remove the "n.MEMBER_TYPE LIKE '%CH'" condition:
SELECT --columns,...
FROM dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
Hi Scott, Thanks for your input. I tried your statement and getting the following errors.
SELECT
Name.ID,
Name.FULL_NAME,
Name.FIRST_NAME,
Name.LAST_FIRST,
Name.MEMBER_TYPE,
Name.STATUS,
Name.FULL_ADDRESS,
Name.EMAIL
FROM
dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Name.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Name.FULL_NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "Name.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Name.LAST_FIRST" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "Name.MEMBER_TYPE" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "Name.STATUS" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Name.FULL_ADDRESS" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "Name.EMAIL" could not be bound.
Regards,
SQLisAwe5oMe.
January 11, 2016 at 1:48 pm
is there any reason why you cannot / will not provide sample data for your table?
am sure it will make providing you a tested solution so much easier for everyone.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 11, 2016 at 2:11 pm
J Livingston SQL (1/11/2016)
is there any reason why you cannot / will not provide sample data for your table?am sure it will make providing you a tested solution so much easier for everyone.
Attached sample data for dbo.Name table.
Regards,
SQLisAwe5oMe.
January 11, 2016 at 2:57 pm
SQLisAwE5OmE (1/11/2016)
J Livingston SQL (1/11/2016)
is there any reason why you cannot / will not provide sample data for your table?am sure it will make providing you a tested solution so much easier for everyone.
Attached sample data for dbo.Name table.
care to convert your spreadsheet into some SQL scripts that populate your table?
before you do......and based on your original posting, the parameters you mentioned dont seem to be included in your sample data...ie Birthdate
Where BIRTH_DATE >= '20131114'
and BIRTH_DATE < '20141016'
+-------------------------------------------------------------------------------------------------------------------+
¦ ID ¦ MEMBER_TYPE ¦ STATUS ¦ CO_ID ¦ LAST_FIRST ¦ FULL_NAME ¦ FIRST_NAME ¦ BIRTH_DATE ¦ CO_MEMBER_TYPE ¦ EMAIL ¦
¦--------+-------------+--------+-------+------------+-----------+------------+------------+----------------+-------¦
¦ 65 ¦ NM-F ¦ A ¦ ¦ ¦ ¦ Anna ¦ NULL ¦ ¦ ¦
¦ 70 ¦ NM-F ¦ I ¦ ¦ ¦ ¦ Issa ¦ NULL ¦ ¦ ¦
¦ 103 ¦ NM-F ¦ I ¦ ¦ ¦ ¦ Chrita ¦ NULL ¦ ¦ ¦
¦ 10000 ¦ NM-F ¦ A ¦ ¦ ¦ ¦ Nova ¦ NULL ¦ ¦ ¦
¦ 100000 ¦ NM-MP ¦ I ¦ 65 ¦ ¦ ¦ duva ¦ 9/9/1999 ¦ MF ¦ ¦
¦ 100001 ¦ NM-MP ¦ A ¦ 70 ¦ ¦ ¦ howa ¦ 1/1/1987 ¦ MF ¦ ¦
¦ 100002 ¦ NM-MP ¦ A ¦ 103 ¦ ¦ ¦ Uri ¦ 7/10/1941 ¦ MF ¦ ¦
¦ 100003 ¦ NM-SP ¦ A ¦ 103 ¦ ¦ ¦ Ruth ¦ 9/14/1944 ¦ MF ¦ ¦
¦ 100004 ¦ NM-CH ¦ A ¦ 103 ¦ ¦ ¦ Noam ¦ 11/24/1974 ¦ MF ¦ ¦
+-------------------------------------------------------------------------------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 11, 2016 at 3:14 pm
SQLisAwE5OmE (1/11/2016)
ScottPletcher (1/11/2016)
I would think it would be something like below.Note: I assumed from the context that you need to match only the *child's* birthday, even though you said "all members". If not, naturally remove the "n.MEMBER_TYPE LIKE '%CH'" condition:
SELECT --columns,...
FROM dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
Hi Scott, Thanks for your input. I tried your statement and getting the following errors.
SELECT
Name.ID,
Name.FULL_NAME,
Name.FIRST_NAME,
Name.LAST_FIRST,
Name.MEMBER_TYPE,
Name.STATUS,
Name.FULL_ADDRESS,
Name.EMAIL
FROM
dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Name.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Name.FULL_NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "Name.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Name.LAST_FIRST" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "Name.MEMBER_TYPE" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "Name.STATUS" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Name.FULL_ADDRESS" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "Name.EMAIL" could not be bound.
That's because I used the alias "n" for the Name table. The name table must be aliased in this query, since you need to use it three different times in the same query.
SELECT
n.ID,
n.FULL_NAME,
n.FIRST_NAME,
n.LAST_FIRST,
n.MEMBER_TYPE,
n.STATUS,
n.FULL_ADDRESS,
n.EMAIL,
nmp.ID AS Parent1_ID,
nmp.FULL_NAME AS Parent1_Full_Name,
nmp.FIRST_NAME AS Parent1_First_Name,
...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 11, 2016 at 3:24 pm
ScottPletcher (1/11/2016)
SQLisAwE5OmE (1/11/2016)
ScottPletcher (1/11/2016)
I would think it would be something like below.Note: I assumed from the context that you need to match only the *child's* birthday, even though you said "all members". If not, naturally remove the "n.MEMBER_TYPE LIKE '%CH'" condition:
SELECT --columns,...
FROM dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
Hi Scott, Thanks for your input. I tried your statement and getting the following errors.
SELECT
Name.ID,
Name.FULL_NAME,
Name.FIRST_NAME,
Name.LAST_FIRST,
Name.MEMBER_TYPE,
Name.STATUS,
Name.FULL_ADDRESS,
Name.EMAIL
FROM
dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Name.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Name.FULL_NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "Name.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Name.LAST_FIRST" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "Name.MEMBER_TYPE" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "Name.STATUS" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Name.FULL_ADDRESS" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "Name.EMAIL" could not be bound.
That's because I used the alias "n" for the Name table. The name table must be aliased in this query, since you need to use it three different times in the same query.
SELECT
n.ID,
n.FULL_NAME,
n.FIRST_NAME,
n.LAST_FIRST,
n.MEMBER_TYPE,
n.STATUS,
n.FULL_ADDRESS,
n.EMAIL,
nmp.ID AS Parent1_ID,
nmp.FULL_NAME AS Parent1_Full_Name,
nmp.FIRST_NAME AS Parent1_First_Name,
...
Thanks Again Scott. This seems to be working. I will validate.
SELECT
n.ID,
n.FULL_NAME,
n.FIRST_NAME,
n.LAST_FIRST,
n.MEMBER_TYPE,
n.STATUS,
n.FULL_ADDRESS,
n.BIRTH_DATE,
nmp.ID AS Parent1_ID,
nmp.FULL_NAME AS Parent1_Full_Name,
nmp.FIRST_NAME AS Parent1_First_Name,
nmp.EMAIL
FROM
dbo.Name AS n LEFT OUTER JOIN --child
dbo.Name AS nmp ON nmp.CO_ID = n.CO_ID AND nmp.MEMBER_TYPE LIKE '%MP' LEFT OUTER JOIN --parent1
dbo.Name AS nsp ON nsp.CO_ID = n.CO_ID AND nsp.MEMBER_TYPE LIKE '%SP' LEFT OUTER JOIN --parent2
dbo.Name_Address AS na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM --??
WHERE
n.MEMBER_TYPE LIKE '%CH' AND
n.BIRTH_DATE >= '20131114' AND
n.BIRTH_DATE < '20141016'
Order by BIRTH_DATE;
Regards,
SQLisAwe5oMe.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply