Duplicate Values when Joining Queries

[FirstStruck]

Hello everyone,

My final query is creating multiple rows on my join and I only want it to create 1. Here is an Example:

Query 1: Items
Product: A    Item: 1
Product: A    Item: 2

Query 2: Units
Product: A    Unit: 9
Product: A    Unit: 8

Query 3: Join on Product 1 to 1
I want it to show:
Prouduct: A    Item: 1    Unit: 9
Prouduct: A    Item: 2    Unit: 8

But instead it's showing:
Prouduct: A    Item: 1    Unit: 9
Prouduct: A    Item: 1    Unit: 8
Prouduct: A    Item: 2    Unit: 8
Prouduct: A    Item: 2    Unit: 9

I only want items to be listed once and units to be listed once. I don't need to see a seperate row for each combination possibility.

Thanks for any help!

[Lynn]

Doesn't your Framework model define the joins? Either way, how would anyone understand that Unit 9 goes with Item 1? Or that Unit 8 goes with item 2? You don't have the necessary detail in query 1 and 2 to achieve what you are describing so unless you can change those I think you are stuck with every item joining to every unit for the product.

[jeff_rob]

Hi FirstStruck,
You can get what you're after IF there is a relationship between item and unit that can be used in a CASE statement for the unit.

CASE WHEN item = 1 THEN unit = 9
         WHEN item = 2 THEN unit = 8
         etc ...
END AS unit

JR

[FirstStruck]

Thanks for the help JR and Lynn.

The problem is theres no relationship between the item and units. From JR's example, Item 1 will not always be Unit 9 and Item 2 will not always be Unit 8. Ideally I just want all the Items listed once, and all the Units listed once.

If there were 200 Items and 30 Units, I wouldn't care which row a particular Unit landed on with a particular Item, I would just want 200 rows, because there were 200 items, 1 item listed on each row, and then on 30 of those 200 rows, to list the 30 Units...Doesn't matter which rows, just only want them to list once.

Thanks again for your all's time and help!

[jeff_rob]

In that case you can simply use a MAX or MIN for unit in query 3, the joined query.  So you would have something like:

SELECT product
           ,item
           ,MAX(unit) as unit
  FROM Query1 q1
           ,Query2 q2
WHERE q1.product = q2.product
GROUP BY product, item

JR