Return data where set is unique

[laxster]

I have two columns within a table that I am trying to write a query for.

I am trying to compare "Coupon #" with "Campaign #" ... so I can pull out only instances where the Coupon # is being used across multiple campaigns.

So for example, in the sample dataset below, 2 entries for 21645 should be the results from the query because 21645 is appearing in BOTH campaigns.

How to build this?

COUPON_NBR CAMPAIGN_NBR
21599 3200012
21621 3200008
21626 3200006
21627 3200008
21632 3200006
21633 3200008
21638 3200006
21639 3200008
21644 3200006
21645 3200010
21645 3200008
21649 3200009
21650 3200006
21651 3200008
21656 3200006
21657 3200008

 

[mdlueck]

So you want only the recurds without including duplicate coupon numbers, or only DISTINCT coupon numbers were there are more than one occurrence of said coupon number? Not really sure what you actually want... ???

I needed to build a query that would insure DISTINCT records from one table and include one of the values from another table that said record happens to be associated with. In my case I did not want duplicate coupon numbers, and just one of the campain numbers the coupon number happens to be associated with.

http://www.access-programmers.co.uk/forums/showthread.php?t=224921#post1147855

 

[laxster]

So you want only the recurds without including duplicate coupon numbers, or only DISTINCT coupon numbers were there are more than one occurrence of said coupon number? Not really sure what you actually want... ???

I needed to build a query that would insure DISTINCT records from one table and include one of the values from another table that said record happens to be associated with. In my case I did not want duplicate coupon numbers, and just one of the campain numbers the coupon number happens to be associated with.

http://www.access-programmers.co.uk/forums/showthread.php?t=224921#post1147855

Well, coupon #s can be assigned to multiple items (IE: milk and cookies could get the same coupon #; this is an "item family"). I don't care about the duplicates within a campaign.... simply the coupon #s that are being used across campaigns.

Below is the SQL I have so far that gets me close to what I want. The issue I have with this code is that it doesn't reflect the cases where there is only 1 coupon # appearing in a campaign but still is used across multiple campaigns. But if I take out the counts, then I only get part of the information I need (item families using the same coupon # across campaigns)

select * from (
       select distinct
              COUPON_NBR,
              CAMPAIGN_NBR,
              count(CAMPAIGN_NBR) over (
                     partition by COUPON_NBR
              ) as coupon_count
       from
       (
              select distinct
                     COUPON_NBR,
                    CAMPAIGN_NBR
              from ADMIN.IEP_OFFER_INVSTMNT i
              group by
                     COUPON_NBR,
                     CAMPAIGN_NBR
              having count(COUPON_NBR) > 1
              order by 1
       ) V1
) V2
where coupon_count > 1

 

[SQL_Hell]

Does this do what you want?

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]*[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][ADMIN.IEP_OFFER_INVSTMNT] A[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2]COUPON_NBR[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]COUNT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]CAMPAIGN_NBR[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][ADMIN.IEP_OFFER_INVSTMNT][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]GROUP [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]COUPON_NBR[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]HAVING[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]COUNT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]CAMPAIGN_NBR[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] B [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] B[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]COUPON_NBR [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] A[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]COUPON_NBR[/SIZE]