question

sqlLearner 1 avatar image
sqlLearner 1 asked

Insert a dummy record in query

I am trying to add a dummy record to the results of a query. Below is an example that will make more sense: My query returns this: ID NAME ContactMother ContactFather ConatctOther 123 Jim FALSE TRUE FALSE 123 Mary False False TRUE So now that the contactMother is False I want to add a dummy record whenever ContactMother is False for a record but at the same time keeping the information from the current two rows. Any ideas?
tsqlssmsinsert
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you show what this third record would look like
0 Likes 0 ·
it would just be dummy info in the name column: ID NAME 123 MISSING_Contact
0 Likes 0 ·
I tried using a CASE STATEMENT when contactMOther is False THEN 'Missing_Contact' but that overwrites my current records
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
I'm still not clear on the requirement, but hopefully this might get us going in the right direction...... declare @YourTable table ( ID int, NAME varchar(50), ContactMother varchar(50), ContactFather varchar(50), ContactOther varchar(50) ) insert into @YourTable select 123,'Jim','FALSE','TRUE','FALSE' insert into @YourTable select 123,'Mary','FALSE','FALSE','TRUE' select ID , NAME , ContactMother , ContactFather , ContactOther from @YourTable union select ID , 'MISSING_Contact' , '' , '' , '' from @YourTable where ContactMother = 'FALSE'
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

change the where clause along the lines that @ThomasRushton suggested above... select ID , NAME , ContactMother , ContactFather , ContactOther from @YourTable union select ID , 'MISSING_Contact' , '' , '' , '' from @YourTable where ID not in (select ID from @YourTable where ContactMother = 'TRUE') This will only add the row when the ID is NOT in a list of IDs that do have a ContactMother
1 Like 1 ·
And can you see why your design is flawed... an ID can have multiple contacts, and a contact has a type (father/mother/other). If this was a normalised design, the query would have been easier to write, using outer joins and nulls
1 Like 1 ·
This works except look at this example: look at this example: It will still produce the dummy record when it shouldn't because this contact does have a MotherContact. ID NAME ContactMother ContactFather 1234 Mary TRUE FALSE 1234 JOE FALSE TRUE I believe something has to be done with count True = 1 False = 0 So When a contact has Count(ContactMOther) < 1 then add dummy record
0 Likes 0 ·
So add something to the `WHERE` clause at the end of @Kev's statement along the lines of AND ID NOT IN (SELECT ID FROM @YourTable WHERE ContactMother = TRUE)
0 Likes 0 ·
Id is not unique? I thought that was a typo. How can ID 1234 have a name 'Mary' and a name 'Joe'?
0 Likes 0 ·
Show more comments
happycat59 avatar image
happycat59 answered
Try something like INSERT INTO yourtable (id, name) SELECT DISTINCT ID, 'Missing_Contact') FROM yourtable WHERE ContactMother IS FALSE
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That won't work because contactmother will be false for at least 2 out of every 3 situations for each ID.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.