CODEWITHSUNDEEP
sqloracle-database
OracleLearner
OracleLearner

Reputation: 45

Sql Query to join two table with tricky logic

I have 2 tables mst_item and time_factor , mst_item has 3 columns item,location,card and time_factor has 2 columns location and card

when i am trying to join these two table using location and card columns.

The requirement is below,

mst_item

item     location    card
xyz       R10        CRU
ABC       R10        LAT
CCC       R14        NAC

time_factor

location     card 
R10          CRU
R10          ALL
R14          ALL
R15          FX
R15          ALL

The output should come as, if from mast_item table location and card both match with location and card in time_factor table then it should return matching records,

ex- for R10 & CRU

item     location    card
xyz       R10        CRU

if only location matches then it should return location and 'ALL' as card from time_factor table.

In any case it should not return both matching card value and 'ALL'.

ex- for R14 and NAC

item     location    card
CCC       R14        ALL

Please help me with the query logic.

Upvotes: 0

Views: 5844

Answers (7)

Gangadhar gudditi
Gangadhar gudditi

Reputation: 1

This can be resolved by using left outer join

Upvotes: -1

user958802
user958802

Reputation: 63

SELECT item, MI.location, MI.card FROM mst_item MI INNER JOIN time_factor TF ON MI.location = TF.location AND MI.card = TF.card

Upvotes: 0

pragna
pragna

Reputation: 1

I think below query will help.

SELECT DISTINCT
mi.item,
mi.location,
CASE
WHEN Tf_Query.location=mi.location AND Tf_Query.card=mi.card
THEN Tf_Query.card
WHEN Tf_Query.location=mi.location AND Tf_Query.card!=mi.card
THEN mi.card
END card
FROM
mst_item mi,
(SELECT
tf.location LOCATION,max(tf.card) card
FROM
time_factor tf
GROUP BY (LOCATION)) Tf_Query
WHERE
Tf_Query.LOCATION=mi.LOCATION

Upvotes: 0

Utsav
Utsav

Reputation: 1

As per my understanding of the requirement the following query will serve the purpose :

select item,mst.location,decode(mst.card,tym.card,tym.card,'ALL') from mst_item mst, time_factor tym where mst.location=tym.location;

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

I think this should do the trick...

select m.item, m.location, m.card from mst_card m, time_factor t
  where m.location = t.location and m.card = t.card
union all
select m.item, m.location, t.card from mst_card m, time_factor t
  where m.location = t.location and t.card = 'ALL'
    and not exists ( select 1 from time_factor t2 where t2.location=m.location and t2.card=m.card);

Upvotes: 2

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

For returning ALL when only location matches, try following query

SQL QUERY

select m.item, m.location,
(CASE WHEN
m.card like t.card THEN m.card
ELSE
'All'
END) as card 
from 
mst_item as m,
time_factor as t 
WHERE
m.location like t.location

ORACLE QUERY

select mst_item.item, mst_item.location,
(CASE 
 WHEN
 mst_item.card = time_factor.card THEN mst_item.card
 ELSE
 'All'
 END) as card 
 from 
 mst_item,
 time_factor  
 WHERE
 mst_item.location like time_factor.location;

OUTPUT

enter image description here

A better query can be

SQL QUERY

select m.item, m.location,t.card
from 
mst_item as m,
time_factor as t 
WHERE
m.location like t.location

ORACLE QUERY

select mst_item.item, mst_item.location, time_factor.card
from 
mst_item,
time_factor  
WHERE
mst_item.location like time_factor.location;

OUTPUT

enter image description here

One other variation can be 

select m.item, m.location,
(CASE WHEN
m.card like t.card THEN m.card
WHEN
t.card like 'ALL' THEN t.card
END) as mcard 
from 
mst_item as m,
time_factor as t 
WHERE
m.location like t.location 
AND (CASE WHEN
m.card like t.card THEN m.card
WHEN
t.card like 'ALL' THEN t.card
END) != '';

ONE more variant can be

SQL QUERY

select m.item, m.location,
(CASE WHEN
m.card like t.card THEN m.card
WHEN
t.card like 'ALL' THEN t.card
END) as mcard 
from 
mst_item as m,
time_factor as t 
WHERE
m.location like t.location 
AND (CASE WHEN
m.card like t.card THEN m.card
WHEN
t.card like 'ALL' THEN t.card
END) != '';

ORACLE QUERY 

select mst_item.item, mst_item.location,
(CASE 
WHEN
mst_item.card = time_factor.card THEN mst_item.card
WHEN
time_factor.card like 'ALL' THEN time_factor.card 
END) as card 
from 
mst_item,
time_factor  
WHERE
mst_item.location like time_factor.location
AND
(CASE 
WHEN
mst_item.card = time_factor.card THEN mst_item.card
WHEN
time_factor.card like 'ALL' THEN time_factor.card 
END) IS NOT NULL
;

OUTPUT

enter image description here

Please check outputs that you want so we can modify accordingly.

SQL fiddle demo at http://sqlfiddle.com/#!2/53e53/21

Oracle fiddle demo at http://sqlfiddle.com/#!4/d28b1/26

Upvotes: 0

ReubenTellis
ReubenTellis

Reputation: 68

Is this something that you are looking for?

select test1.location,"card"= case 
          When test1.location = test2.location AND test1.card = test2.card then test2.card 
          When test1.location = test2.location AND test1.card != test2.card then 'ALL'
          End
from test1 inner join test2 on test1.location = test2.location

Here test1 is mst_item and test2 is time_factor

Here is the SQL Fiddle link

http://sqlfiddle.com/#!3/9c6eb/8

Upvotes: 0

Related Questions