September 12, 2015 at 5:46 pm
Hello,
I have an insert statement similar to:
insert into dbo.CHILD (ChildMotherID, ChildNo)
values
(
(select MotherID from dbo.Mother where MotherName = 'Suzie Q'),
(select max(ChildrenAmt)+1 from dbo.Children where ChildrenMotherID =
(select MotherID from dbo.Mother where MotherName = 'Suzie Q')
)
This statement will work if Suzie Q has an entry in dbo.Children but if she does not then I get error similar to "column does not allow nulls. INSERT fails."
How do I enhance this query to deal with no lines in dbo.Children?
Thanks in advance!
September 13, 2015 at 1:00 am
chetta (9/12/2015)
Hello,I have an insert statement similar to:
insert into dbo.CHILD (ChildMotherID, ChildNo)
values
(
(select MotherID from dbo.Mother where MotherName = 'Suzie Q'),
(select max(ChildrenAmt)+1 from dbo.Children where ChildrenMotherID =
(select MotherID from dbo.Mother where MotherName = 'Suzie Q')
)
This statement will work if Suzie Q has an entry in dbo.Children but if she does not then I get error similar to "column does not allow nulls. INSERT fails."
How do I enhance this query to deal with no lines in dbo.Children?
Thanks in advance!
This example will probably get you passed this hurdle
😎
/* Safe place */
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.CHILD') IS NOT NULL DROP TABLE dbo.CHILD;
IF OBJECT_ID(N'dbo.MOTHER') IS NOT NULL DROP TABLE dbo.MOTHER;
CREATE TABLE dbo.MOTHER
(
MOTHER_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_MOTHER_MOTHER_ID PRIMARY KEY CLUSTERED
,MOTHER_NAME VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.CHILD
(
CHILD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_CHILD_CHILD_ID PRIMARY KEY CLUSTERED
,MOTHER_ID INT NOT NULL CONSTRAINT FK_DBO_CHILD_DBO_MOTHER_MOTHER_ID FOREIGN KEY REFERENCES dbo.MOTHER(MOTHER_ID)
,CHILD_NAME VARCHAR(50) NOT NULL
);
INSERT INTO dbo.MOTHER(MOTHER_NAME)
VALUES ( 'Anna')
,( 'Bella')
,( 'Charol')
,( 'Diana')
,( 'Elsa')
;
INSERT INTO dbo.CHILD(MOTHER_ID,CHILD_NAME)
VALUES ( 1, 'Alice')
,( 1, 'Tommy')
,( 3, 'Benny')
,( 3, 'Mary')
,( 5, 'Joe')
;
/* Some indexes */
CREATE NONCLUSTERED INDEX NCLIDX_DBO_MOTHER_NAME_INCL_MOTHER_ID ON dbo.MOTHER (MOTHER_NAME ASC) INCLUDE (MOTHER_ID);
CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_DBO_CHILD_MOTHER_ID_CHILD_ID_INCL_CHILD_NAME ON dbo.CHILD (MOTHER_ID ASC,CHILD_ID ASC) INCLUDE (CHILD_NAME);
/* All children have mothers but not all mothers have children.
If the mother does not have a child then the CHILDREN_ID of 0
and CHILDREN_NAME of 'N/C' is displayed
*/
SELECT
MUM.MOTHER_ID
,MUM.MOTHER_NAME
,ISNULL(BRAT.CHILD_ID ,0) AS CHILD_ID
,ISNULL(BRAT.CHILD_NAME,'N/C') AS CHILD_NAME
FROM dbo.MOTHER MUM
LEFT OUTER JOIN dbo.CHILD BRAT
ON MUM.MOTHER_ID = BRAT.MOTHER_ID;
/* Charol has children */
DECLARE @MUM VARCHAR(50) = 'Charol';
SELECT
MUM.MOTHER_ID
,MUM.MOTHER_NAME
,ISNULL(BRAT.CHILD_ID ,0) AS CHILD_ID
,ISNULL(BRAT.CHILD_NAME,'N/C') AS CHILD_NAME
FROM dbo.MOTHER MUM
LEFT OUTER JOIN dbo.CHILD BRAT
ON MUM.MOTHER_ID = BRAT.MOTHER_ID
WHERE MUM.MOTHER_NAME = @MUM;
/* Bella has no children */
SET @MUM = 'Bella';
SELECT
MUM.MOTHER_ID
,MUM.MOTHER_NAME
,ISNULL(BRAT.CHILD_ID ,0) AS CHILD_ID
,ISNULL(BRAT.CHILD_NAME,'N/C') AS CHILD_NAME
FROM dbo.MOTHER MUM
LEFT OUTER JOIN dbo.CHILD BRAT
ON MUM.MOTHER_ID = BRAT.MOTHER_ID
WHERE MUM.MOTHER_NAME = @MUM;
Query results (respectfully)
MOTHER_ID MOTHER_NAME CHILD_ID CHILD_NAME
----------- ------------- ----------- -------------
1 Anna 1 Alice
1 Anna 2 Tommy
2 Bella 0 N/C
3 Charol 3 Benny
3 Charol 4 Mary
4 Diana 0 N/C
5 Elsa 5 Joe
MOTHER_ID MOTHER_NAME CHILD_ID CHILD_NAME
----------- ------------- ----------- -------------
3 Charol 3 Benny
3 Charol 4 Mary
MOTHER_ID MOTHER_NAME CHILD_ID CHILD_NAME
----------- ------------- ----------- -------------
2 Bella 0 N/C
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply