Home » SQL & PL/SQL » SQL & PL/SQL » Comparing columns from different tables (merged by LF)
Comparing columns from different tables (merged by LF) [message #263052] Tue, 28 August 2007 23:16 Go to next message
ilangoraj
Messages: 5
Registered: August 2007
Junior Member
I need check a column value from two different Table
I had the Query Like this

-----------------------------------------------------
SELECT CASE WHEN
a.col_1 = c.col_1 AND
a.col_2 = c.col_2 AND
a.col_3 = c.col_3 AND
a.col_4 = c.col_4 AND
a.col_5 = c.col_5 AND
a.col_6 = c.col_6 THEN 'equal'
WHEN
a.col_1 > c.col_1 AND
a.col_2 > c.col_2 AND
a.col_3 > c.col_3 AND
a.col_4 > c.col_4 AND
a.col_5 > c.col_5 AND
a.col_6 > c.col_6 THEN 'high'
WHEN
a.col_1 < c.col_1 AND
a.col_2 < c.col_2 AND
a.col_3 < c.col_3 AND
a.col_4 < c.col_4 AND
a.col_5 < c.col_5 AND
a.col_6 < c.col_6 THEN 'low'
ELSE
'unequal'
END as status
FROM
Table1 a,
Table2 b
Table1 c,
Table2 d

WHERE a.Prim_Key = b.Prim_Key
AND b.retrieve_key = Value1
and c.Prim_Key = d.Prim_Key
AND d.retrieve_key = Value2

-----------------------------
retrieve_key key values Will be different
Re: Checking Two column from two Diffrerent table [message #263054 is a reply to message #263052] Tue, 28 August 2007 23:19 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
retrieve_key key values Will be different



What you want ? Not clear. For different checking use <>
Re: Checking Two column from two Diffrerent table [message #263058 is a reply to message #263052] Tue, 28 August 2007 23:42 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

WHERE a.Prim_Key = b.Prim_Key
AND b.retrieve_key = Value1
and c.Prim_Key = d.Prim_Key
AND d.retrieve_key = Value2



You have no relation between table alias a and table alias c

[Updated on: Tue, 28 August 2007 23:42]

Report message to a moderator

Re: Checking Two column from two Diffrerent table [message #263072 is a reply to message #263052] Wed, 29 August 2007 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the question?

By the way, think about NULL.

Regards
Michel
Re: Checking Two column from two Diffrerent table [message #263091 is a reply to message #263054] Wed, 29 August 2007 01:06 Go to previous messageGo to next message
ilangoraj
Messages: 5
Registered: August 2007
Junior Member
yes, Different Values
Re: Checking Two column from two Diffrerent table [message #263092 is a reply to message #263072] Wed, 29 August 2007 01:11 Go to previous messageGo to next message
ilangoraj
Messages: 5
Registered: August 2007
Junior Member
Michel My Question i need to compare betwwen column,
but the column value may vary because i use two different selection Key(b.retrieve_key = Value1 AND d.retrieve_key = Value2).
Re: Checking Two column from two Diffrerent table [message #263095 is a reply to message #263052] Wed, 29 August 2007 01:15 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

It usually make no sense.

You are comparing because of varying. If it has no variation then why you are comparing?
i don't follow rules: i need to compare column of two tables [message #263126 is a reply to message #263052] Wed, 29 August 2007 02:41 Go to previous messageGo to next message
ilangoraj
Messages: 5
Registered: August 2007
Junior Member
i need to check column value of one query1 to query2

query1 :
--------
SELECT col1,
col2,col3,col4,col5
FROM Table1 a,
Table2 b
WHERE Table2 .Fieldname = 'InputValue1';
query 2:
--------
SELECT col1,
col2,col3,col4,col5
FROM Table1 a,
Table2 b
WHERE Table2 .Fieldname = 'InputValue2';


since Input value(InputValue1 and InputValue2) are different so the resultant output changes. so when i try to join this query.it returns Cartesian value. Need to compare the col1 from first query1 with query2

Let Me Know as soon as possible

[Updated on: Thu, 30 August 2007 07:52] by Moderator

Report message to a moderator

Re: urgent:i need to compare column of two tables [message #263132 is a reply to message #263126] Wed, 29 August 2007 02:56 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:24371552251735
and
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2151582681236

may be these links might help you.


regards,

Re: urgent:i need to compare column of two tables [message #263207 is a reply to message #263126] Wed, 29 August 2007 07:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd use UNION ALL personaly. (Untested Code)
SELECT 'query 1' source, col1
FROM Table1 a,
Table2 b
WHERE Table2 .Fieldname = 'InputValue1'
UNION ALL
SELECT 'query 2' source,col1,
FROM Table1 a,
Table2 b
WHERE Table2 .Fieldname = 'InputValue2'
order by 2,1
Previous Topic: no of weeks
Next Topic: Not getting the result
Goto Forum:
  


Current Time: Tue Dec 06 08:47:32 CST 2016

Total time taken to generate the page: 0.11627 seconds