How to Add in Another Column With the Data Filtered

zberg007
zberg007 used Ask the Experts™
on
Please review my prior question, which as been answered and closed:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27722365.html

Now, I just want to add back in one more column... the original LL.Label_Text, so that I can see this value in the returned results. I tried adding it in the select statement, but I get this error: Msg 8120, Level 16, State 1, Line 1
Column 'LABEL_LANGUAGES.LABEL_TEXT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I add it to the Group By clause, I get all the results returned from my table (306) rather than my filtered data.

Thanks Experts!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this

;with cte as (
      SELECT       LD.Label_ID,
            LL.Language_ID,
            LD.Label_Description,
            LL.Label_Text
      FROM   LABEL_DESCRIPTIONS LD
      INNER JOIN LABEL_LANGUAGES  LL  ON LL.Label_ID = LD.Label_ID
)
select * from cte a
inner join (select Label_ID from cte group by Label_ID having count(Language_ID) = 1) b on a.Label_ID = b.Label_ID
order by a.label_ID
Add the column in both the GROUP BY clause and in the SELECT list:

SELECT LD.Label_ID, LD.Label_Description,LL.Label_Text
            FROM   LABEL_DESCRIPTIONS LD
                            INNER JOIN LABEL_LANGUAGES  LL  ON LL.Label_ID = LD.Label_ID
            where LL.Language_ID = 1 OR LL.Language_ID = 5
group by LD.Label_ID, LD.Label_Description,LL.Label_Text
having Count(1) = 1
            order by LL.Label_ID

Author

Commented:
This worked great for my purposes. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial