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?
it would just be dummy info in the name column: ID NAME 123 MISSING_Contact
I tried using a CASE STATEMENT when contactMOther is False THEN 'Missing_Contact' but that overwrites my current records
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'
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
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
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
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)
Id is not unique? I thought that was a typo. How can ID 1234 have a name 'Mary' and a name 'Joe'?
Try something like INSERT INTO yourtable (id, name) SELECT DISTINCT ID, 'Missing_Contact') FROM yourtable WHERE ContactMother IS FALSE
That won't work because contactmother will be false for at least 2 out of every 3 situations for each ID.
11 People are following this question.
