Confusing SQL

[alison]

Hi there,

This is probably a simple question but it's go me flummoxed

I have 2 tables, one with 1,000 record one with 10,000 records

I want to join the two tables and still only see 1,000 records

e.g.

select count(*) from t1
= 1000 records

select count(*) from t1, t2
where t1.c1 = t2.c1
= 10000 records

The problem is that t1.c1 can (and does) have lots of identical record in t2.c1

There is nothing else to join it on, all of the appropriate where conditions are in.

Obviously this is just an example, I can post the actual SQL if required.

Oracle 7.3, Windows NT

Many thanks in advance

Alison

[smdakram]

!hello

According to my idea

Do " OUTER JOIN " Using (+)

ok
try it and get the solution for u r Query
good luck

[ales]

Hi,
if you need just count then select count(distinct t1.c1) from t1,t2 where t1.c1= t2.c1;

if you need records then select distinct * from t1,t2 where t1.c1= t2.c1;

Hope this is you need.
Ales

[alison]

Thanks - but still not working

This is the real code
--original query
SELECT COUNT(*)
FROM PS_TRANSACTION_INV A
WHERE A.TRANSACTION_GROUP = '021'
AND A.INV_ITEM_ID = '0709205'
AND A.INV_LOT_ID = 'P3L0831'
= 14 records

--new query
SELECT COUNT(*)
FROM PS_TRANSACTION_INV A, PS_CB_WMS_SHIP B WHERE A.BUSINESS_UNIT(+) = B.BUSINESS_UNIT
AND A.INV_ITEM_ID(+) = B.INV_ITEM_ID
AND A.INV_LOT_ID(+) = B.INV_LOT_ID
AND A.TRANSACTION_GROUP = '021'
AND A.INV_ITEM_ID = '0709205'
AND A.INV_LOT_ID = 'P3L0831'
= 182 records

As you can see my results are totally different

Thanks v much

[nealh]

What exactly are you trying to achieve ? Are you trying to count the records in PS_TRANSACTION which are validly registered in PS_CB_WMS_SHIP ? If so, you could do:

SELECT COUNT(*)
FROM PS_TRANSACTION_INV A
WHERE A.TRANSACTION_GROUP = '021'
AND A.INV_ITEM_ID = '0709205'
AND A.INV_LOT_ID = 'P3L0831'
AND EXISTS
(SELECT 'X' FROM
PS_CB_WMS_SHIP B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.INV_ITEM_ID = B.INV_ITEM_ID
AND A.INV_LOT_ID = B.INV_LOT_ID)

[alison]

No sorry, I've probably confused the matter by putting in the count(*) reference.

What I am trying to do is retrieve the information from the PS_TRANSACTION_INV table with an extra field from the PS_CB_WMS_SHIP (that I will later use to identify different shipping lines)

I want the amount of records (and the data displayed) to be exactly the same as if I hadn't added the second table (except the additional field)

i.e
SELECT A.PRODUCTION_ID, A.INV_ITEM_ID, A.INV_LOT_ID, B.CB_SS_CODE, SUM(A.QTY_BASE)
FROM PS_TRANSACTION_INV A, PS_CB_WMS_SHIP B WHERE A.BUSINESS_UNIT(+) = B.BUSINESS_UNIT AND A.INV_ITEM_ID(+) = B.INV_ITEM_ID
AND A.INV_LOT_ID(+) = B.INV_LOT_ID
AND A.TRANSACTION_GROUP = '021'
AND A.INV_ITEM_ID = '0709205'
AND A.INV_LOT_ID = 'P3L0831'
GROUP BY A.PRODUCTION_ID, A.INV_ITEM_ID, A.INV_LOT_ID, B.CB_SS_CODE;

to give the same results as

SELECT A.PRODUCTION_ID, A.INV_ITEM_ID, A.INV_LOT_ID, SUM(A.QTY_BASE)
FROM PS_TRANSACTION_INV A,
WHERE A.TRANSACTION_GROUP = '021'
AND A.INV_ITEM_ID = '0709205'
AND A.INV_LOT_ID = 'P3L0831'
GROUP BY A.PRODUCTION_ID, A.INV_ITEM_ID, A.INV_LOT_ID;

Hope I haven't confused things more.

Cheers

Alison

[nealh]

The problem is that you are not joining PS_TRANSACTION_INV to PS_CB_WMS_SHIP on a primary key. That's why you are getting duplicates. This implies that the design of the tables might need a bit of rethinking.

In the short-term, the only solution that I can think of would be to create a cut-down version of PS_CB_WMS_SHIP using an inline view e.g.

SELECT A.PRODUCTION_ID, A.INV_ITEM_ID, A.INV_LOT_ID, B.CB_SS_CODE, SUM(A.QTY_BASE)
FROM PS_TRANSACTION_INV A,
(select distinct b.business_unit,
b.inv_item_id,
b.inv_lot_id,
b.cb_ss_code
from PS_CB_WMS_SHIP) B
WHERE A.BUSINESS_UNIT(+) = B.BUSINESS_UNIT AND A.INV_ITEM_ID(+) = B.INV_ITEM_ID
AND A.INV_LOT_ID(+) = B.INV_LOT_ID
AND A.TRANSACTION_GROUP = '021'
AND A.INV_ITEM_ID = '0709205'
AND A.INV_LOT_ID = 'P3L0831'
GROUP BY A.PRODUCTION_ID, A.INV_ITEM_ID, A.INV_LOT_ID, B.CB_SS_CODE

A problem would arise if CB_SS_CODE is not defined uniquely for a particular combination of inv_item_id, inv_lot_id and business_unit. In that case, you would still get duplicates. If so, you really will need to revisit the design and look more closely at what you're trying to do.

[alison]

Thanks very much nealh

You have confirmed what I suspected.

Back to the drawing board

Allie