Home » SQL & PL/SQL » SQL & PL/SQL » Query output - Help (9.2.0.7)
Query output - Help [message #358368] Mon, 10 November 2008 23:30 Go to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

I have following information in tables

Table : Branch
Zone	Branch
==============
East	101
East	102
West	103
West	104
South	105
South	106
North	107
North	108

Table : Commodity
ID	Commodity	
=================
1	A
2	B

Table : Txns
Zone	Branch	Amt	ID
==========================
East	101	100	1
East	101	50	2
East	102	200	2
South	105	300	1
South	106	250	2
North	108	75	1


I'm looking for following o/p

Required Output
===============

Zone	Branch Commodity	Amt
===================================
East	101	A		100
East	101	B		50
East	102	A		0
East	102	B		200
West	103	A		0
West	103	B		0
West	104	A		0
West	104	B		0
South	105	A		300
South	105	B		0
South	106	A		0
South	106	B		250
North	107	A		0
North	107	B		0
North	108	A		75
North	108	B		0


Can anybody help me get the above output.

Brian.
Re: Query output - Help [message #358370 is a reply to message #358368] Tue, 11 November 2008 00:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


What did you try so far ?

Option 1 :

Try Cartesian join Commodity and Branch and Select the Correspond amount from Txns (using correlated subquery).

Option 2:

Try Cartesian join Commodity and Branch and outer join with Txns for getting the appropriate amount .

Smile
Rajuvan.

Re: Query output - Help [message #358377 is a reply to message #358368] Tue, 11 November 2008 01:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For this kind of question, always post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Previous Topic: table with subtypes, inserting problem
Next Topic: default value and not null constraint
Goto Forum:
  


Current Time: Sun Dec 04 08:42:39 CST 2016

Total time taken to generate the page: 0.25021 seconds