RE: join query
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "krishan.gupta@xxxxxxxx" <krishan.gupta@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jan 2009 05:31:09 -0500
Hmm....I think I see some pretty severe data modelling problems/questions here,
or, at the very least, an incompletely communicated data model and desired
output.
There are lots of ways to cobble together a query that will produce the
requested output, but, I don't think any of them will solve the problem.
One such query is:
1 select distinct a.pos_id, a.case_id,b.box_id from xx_case a, xx_box b
2 where a.pos_id = b.pos_id and substr(a.case_id,2,1) =
substr(b.box_id,2,1)
3 union all
4 select 2,null,'B3' from dual
5 union all
6 select 2,null,'B3' from dual
7* order by 2 nulls last
SQL> /
POS_ID CA BO
---------- -- --
2 C1 B1
2 C2 B2
2 B3
2 B3
Strictly speaking, it *is* an answer to the question you asked. Having seen
that, please purge it from your brain! Don't even think of implementing code
like this.
My point is, we need more information, more sample data. We need to better
understand your data model and what question you're trying to answer. For
example, B3? Where'd those values come from, if they're not in the table??
If you can more clearly communicate your data model, some sample data, and some
sample output that logically follows from that, I'm confident that someone here
will be able to help you.
Hope that helps,
-Mark
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Krishan Gupta [krishan.gupta@xxxxxxxx]
Sent: Tuesday, January 20, 2009 12:28 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: join query
Hi all..,
This may be very simple but I guess I need experts help for the following in
oracle 10g database :-
select * from xx_case;
pos_id case_id
2 C1
2 C2
select * from xx_box;
pos_id box_id
2 B1
2 B2
The only common column between these two tables is pos_id.
How do I write query from these two tables so that the output result would be
pos_id case_id box_id
2 C1 B1
2 C2 B2
2 B3
2 B3
Thanks and Regards
Krishan
--
//www.freelists.org/webpage/oracle-l
Other related posts:
