asked on

How to Query Distinct Values - Only Different

Hi Experts,

I have two tables. One called 'Label_Languages' and the other called 'Label_Descriptions'

Right now I am viewing the results with this query:

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
            where LD.Label_ID = LL.Label_ID
            order by LL.Label_ID

Open in new window

Here we have the results of the above query from rows 27 thru 38:

My question is this... I want to run a query that shows where I don't have results that are of both Language_ID 1 and 5, so that I can correct the situation by adding in the values for a missing row of either 1 (English) or 5 (Chinese).

If you look at rows 27 through 32... we're good. Meaning that I have values in both English and Chinese for the same Label_ID. However, beginning on row 33, I have English only for Label_ID '40000025', so I'm missing the Chinese values for this Label_ID.

Then beginning on row 34 through 36, I have Chinese only (Language_ID of 5) for Label_ID's '40000026', '40000027' and '40000028'.

What I need is a query to show me where only one Language_ID exists for any given Label_ID, then I can quickly identify the ones that need values inserted so that I have both languages for any given Label_ID.

Thanks much in advance!

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Just a slight correction to Patrick's solution (no points please):

SELECT  LD.Label_ID,
        LD.Label_Description
FROM    LABEL_DESCRIPTIONS LD
        INNER JOIN LABEL_LANGUAGES LL ON LL.Label_ID = LD.Label_ID AND LL.Language_ID IN (1, 5)
WHERE	LL.LabelID IS NULL
GROUP BY LD.Label_ID,
        LD.Label_Description
ORDER BY LD.Label_ID

Open in new window

zberg007

ASKER

Thanks for the quick responses:

When I run matthewspatrick's possible solution... I get this error:
Msg 8127, Level 16, State 1, Line 1
Column "LABEL_LANGUAGES.LABEL_ID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

However, when I run acperkins slight correction comment (thanks by the way)... I get no rows returned.

Anthony Perkins

You are right, I am not sure what I was thinking:

SELECT  LD.Label_ID,
        LD.Label_Description
FROM    LABEL_DESCRIPTIONS LD
        LEFT JOIN LABEL_LANGUAGES LL ON LL.Label_ID = LD.Label_ID AND LL.Language_ID IN (1, 5)
WHERE	LL.LabelID IS NULL
GROUP BY LD.Label_ID,
        LD.Label_Description
ORDER BY LD.Label_ID

Open in new window

zberg007

ASKER

We are getting there. I made a slight adjustment using this query:

SELECT  LD.Label_ID,
        LD.Label_Description
FROM    LABEL_DESCRIPTIONS LD
        INNER JOIN LABEL_LANGUAGES LL ON LL.Label_ID = LD.Label_ID AND LL.Language_ID IN (1, 5)
WHERE	LL.Label_ID = LD.Label_ID
GROUP BY LD.Label_ID, LD.Label_Description
HAVING Count(1) = 1
ORDER BY LD.Label_ID

Open in new window

And it outputs like this:

However, how can I add the Language_ID column back in to show the Language that I have, so that I can insert the one that I still need. Hope this makes sense.

Thanks again!

ASKER CERTIFIED SOLUTION

Matt Bowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

The following is redundant:
WHERE LL.Label_ID = LD.Label_ID

zberg007

ASKER

acperkins is right... the where statement is redundant. Works fine without it. Thanks!