Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Frosty615
Starting Member
13 Posts |
Posted - 2013-03-25 : 10:42:00
|
Hello all,I've got two tables and I want to insert into another table one column from eachI.e.SignaturesRequiredSIgnatureID INTPersonalDetailspdStaffid INTEach table holds multiple values....I have this:INSERT INTO PersonalDetails_Signatures(SignatureID, Staffid, SigYear)(SELECT SignatureID, (SELECT PDStaffID FROM PersonalDetails WHERE Deactivated = 0 AND Deleted = 0), DATEPART(YEAR, GETDATE()) FROM SignaturesRequired WHERE Deactivated = 0) But the statement fails 'Subquery returned more than 1 value'How do I resolve this?CheersFrosty |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-25 : 11:18:47
|
The error message is saying that the inner select (SELECT PDStaffID FROM ...) is returning more than one row.How is PersonalDetails table related to SignaturesRequired table? There has to be a column in the SignaturesRequired that can be used to join to PersonalDetails table. If there is then you would join on that column - for example like this:INSERT INTO PersonalDetails_Signatures(SignatureID, Staffid, SigYear)SELECT s.SignatureID, p.PDStaffID,DATEPART(YEAR, GETDATE()) FROM SignaturesRequired s inner join PersonalDetails pon p.JoinColumn = s.JoinColumnWHERE s.Deactivated = 0 and p.Deactivated = 0 AND p.Deleted = 0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-25 : 11:24:09
|
the error is obvious. The below query returns multiple records which cant be used inside a subquery.SELECT PDStaffID FROM PersonalDetails WHERE Deactivated = 0 AND Deleted = 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Frosty615
Starting Member
13 Posts |
Posted - 2013-03-25 : 11:37:49
|
Sorry I should have elaborated a bit more in my original postMembers of staff have to sign publications every quarterso, I have 3 tablesOne stores PersonalDetails (PDStaffid INT is the primary key)One stores the publications they need to sign SignaturesRequired (SignatureID INT is the primary key)and the other stores if the individual has signed the publicationStaffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BITOnce a year, I need to add a record for each individual for each publication (via a SP)So if I had four individuals setup and four publications the data that should be inserted is:Staffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BIT 0 0 0 0 0 0 0 1 0 0 0 0 0 2 0 0 0 0 0 3 0 0 0 0 1 0 0 0 0 0 1 1 0 0 0 0 1 2 0 0 0 0 1 3 0 0 0 0 2 0 0 0 0 0etc etcI hope this helpsFrosty*Edit, sorry, the forum unformatted the table above |
|
|
Frosty615
Starting Member
13 Posts |
Posted - 2013-03-25 : 11:40:54
|
quote: Originally posted by visakh16 the error is obvious. The below query returns multiple records which cant be used inside a subquery.SELECT PDStaffID FROM PersonalDetails WHERE Deactivated = 0 AND Deleted = 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Yes, apologies, like I said, I should have elaborated more in my OP. I understand the error and why it is happening, what I need help with is making the statement do what I want it toFrosty |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-25 : 12:44:17
|
quote: Originally posted by Frosty615 Sorry I should have elaborated a bit more in my original postMembers of staff have to sign publications every quarterso, I have 3 tablesOne stores PersonalDetails (PDStaffid INT is the primary key)One stores the publications they need to sign SignaturesRequired (SignatureID INT is the primary key)and the other stores if the individual has signed the publicationStaffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BITOnce a year, I need to add a record for each individual for each publication (via a SP)So if I had four individuals setup and four publications the data that should be inserted is:Staffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BIT 0 0 0 0 0 0 0 1 0 0 0 0 0 2 0 0 0 0 0 3 0 0 0 0 1 0 0 0 0 0 1 1 0 0 0 0 1 2 0 0 0 0 1 3 0 0 0 0 2 0 0 0 0 0etc etcI hope this helpsFrosty*Edit, sorry, the forum unformatted the table above
I think what you need is a cross joinsomething likeINSERT ThirdTableNameHereSELECT p.StaffID,s.SignatureID,0,0,0,0FROM Signatureequired sCROSS JOIN PersonDetails p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Frosty615
Starting Member
13 Posts |
Posted - 2013-03-29 : 04:29:31
|
Sorry for the late response - thank you, the cross join was the ticket! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-29 : 14:05:11
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|