0

I've two tables: T1 & T2. My column of interest are 'Item' from T1 & 'Store' from T2.

Here's my query & few options i've tried but could get desired result:

Query1: (very time consuming - Its oracle DB)

select distinct  T1.Item,T2.Store from
(select distinct * from T1) T1
full outer join 
(select distinct * from T2)T2
on T1.ITEM_ID=T2.ITEM_ID 

Query2: (This is so close to the result I need but i'm stuck with max of item value instead of the whole picture)

select max(T1.ITEM),T2.Store from T1 , T2 
where  T1.ITEM_ID=T2.ITEM_ID
group by T2.Store

I am trying to get unique row values for both column

Here's the scenario i'm facing: After doing distinct im getting result as below:

Item Store
1     8
2     8
3     8

But my desired result is (1 item per store):

Item Store
1     8
2     15
3     53

That is, It should be unique for all rows but i am unable to achieve it. Please suggest.

EDIT:

Table T1:

ID         Item  ITEM_ID    CREATE_DATE          UPDATE_DATE          
---------  ---------------  ---------  -------------------  -------------------  
123           8    1       2021-05-21 21:08:14  2021-05-21 21:08:14  
15           15    7      2021-05-22 14:15:49  2021-05-22 14:15:49

Table T2:

Store      ITEM_ID      Itemsale       Status   
8          1             (null)          C      
65         87            (null)          C

Solution for my case

**select T1.Item,T2.Store 
from
(SELECT  Item, DENSE_RANK() OVER (ORDER BY Item )  rnk from T1 group by Item   ) T1
inner join 
(SELECT Store ,DENSE_RANK() OVER (ORDER BY Store )  rnk  from T2 group by Store ) T2
on T1.rnk=T2.rnk**
Amin
  • 9
  • 1
  • 4
  • 1
    Use `UNION ALL` with two tables and from the result alias table Apply `DISTINCT` clause – Ajith May 23 '21 at 16:34
  • Edit your question and show sample data. Without knowing what the data looks like, it is really hard to address your question. – Gordon Linoff May 23 '21 at 17:09

1 Answers1

1

It seems like you should simply be doing:

select distinct  T1.Item, T2.Store 
from T1
  inner join T2 on T1.ITEM_ID = T2.ITEM_ID

Or "including items not found in any store":

select distinct  T1.Item, T2.Store 
from T1
  left outer join T2 on T1.ITEM_ID = T2.ITEM_ID

Or "including empty stores"

select distinct  T1.Item, T2.Store 
from T1
  right outer join T2 on T1.ITEM_ID = T2.ITEM_ID

The time taken to do this join will depend on which columns are indexed, how many rows there are and your connection to the server. For 3 rows it should reasonably be milliseconds

If there is something else that can be used to remove duplicates (other than distinct) you should leverage that (for example if items have a "discontinued" flag, and they are replaced by another item with the same name, you should look to include discontinued flag in the where/on rather than including them in the join and using DISTINCT to clobber them out afterwards)

If you meant you just want "every product cross combined with every store" that's;

select distinct  T1.Item, T2.Store 
from T1
  cross join T2

To join item and store on a fake id, meaning items get assigned to stores, one item per store, in some order, you can do like..

select T1.Item,T2.Store
from
  (select item, row_number() over(order by Item) r from T1 group by item) T1
  inner join 
  (select store, row_number() over(order by store) r from T2 group by store) T2
  on T1.R = T2.R
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • It's giving me the same scenario. Like store is 8 but item is changing. But I want both to be unique like row wise Different item different store. Not like different item same store value. Hope im making sense. – Amin May 23 '21 at 16:48
  • Not really making sense, no.. Store is 8 and item is x,y or z because store table really does have multiple rows for store 8 with different itemId, or those x,y,z items all have same item id (and that itemId is in the store table) – Caius Jard May 23 '21 at 16:53
  • Do you mean you have 1000 items and 1000 stores and you want 1 item per store (but you don't care which item, or which store? In that case you have to invent a fake column to join on that is just an incrementing number – Caius Jard May 23 '21 at 16:55
  • Yes exactly 1 item per store is what i'm trying to achieve! – Amin May 23 '21 at 17:01
  • I put an an example at the end of the answer – Caius Jard May 23 '21 at 17:35
  • Thanks a lot! it worked for me... Due to duplicate values I changed the query a lil bit. Added at the end of my question on edit. – Amin May 23 '21 at 17:58
  • There shouldn't be any duplicates by the time the row numbering is done (due to the GroupBy, which is performed first) and row number produces unique values so I'm mildly puzzled by the claim that there were duplicates – Caius Jard May 23 '21 at 18:40
  • 1
    Yes correct. Group by loses the duplicates no matter if I use row_number or dense_Rank. Thanks Caius for the help. You're awesome! – Amin May 24 '21 at 02:49