SELECT Question
-
Hi, I'm trying to use the query below in MSSQL.
SELECT [mxDelete],[mxContactID] FROM mxContact.dbo.Contacts WHERE mxContactID IN **(SELECT [mxContactID], [mxzCopies] FROM** (SELECT [mxContactID], [mxzCopies] FROM mxContact.dbo.Associations_Groups_to_Contacts WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B') **AS COL GROUP BY mxContactID HAVING (COUNT(mxContactID) > 1))**My question is, how can I make it so that the code in bold will give me the mxzCopies column as well. If I select just the contactID, then it works fine, but with the addition of mxzCopies, it gives me: Msg 8120, Level 16, State 1, Line 1 Column 'COL.mxzCopies' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Cheers, Mark.Mark Brock "We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen Click here to view my blog
-
Hi, I'm trying to use the query below in MSSQL.
SELECT [mxDelete],[mxContactID] FROM mxContact.dbo.Contacts WHERE mxContactID IN **(SELECT [mxContactID], [mxzCopies] FROM** (SELECT [mxContactID], [mxzCopies] FROM mxContact.dbo.Associations_Groups_to_Contacts WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B') **AS COL GROUP BY mxContactID HAVING (COUNT(mxContactID) > 1))**My question is, how can I make it so that the code in bold will give me the mxzCopies column as well. If I select just the contactID, then it works fine, but with the addition of mxzCopies, it gives me: Msg 8120, Level 16, State 1, Line 1 Column 'COL.mxzCopies' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Cheers, Mark.Mark Brock "We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen Click here to view my blog
Nevermind figured it out.
SELECT mxContactID, mxzCopies FROM ( SELECT [mxContactID], [mxzCopies] FROM mxContact.dbo.Associations_Groups_to_Contacts WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B' ) AS COL GROUP BY mxContactID, mxzCopies HAVING (COUNT(mxContactID) > 1)Mark Brock "We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen Click here to view my blog