RE: join query

From: Krishan Gupta <>
Date: Wed, 21 Jan 2009 09:13:17 -0600
Message-ID: <C0D6C26683355D43B6F85842E5DE9E9152DAD7_at_etccex03.etcc.local>

My apologies, I misstated it. xx_box have 4 records as follows :-.

select * from xx_box;

pos_id       box_id
2                B1
2                B2
2                B3
2                B4

I also figured out how to get the output result I wanted. Here is the query :-

select e.pos_id, d.case_id, e.box_id
(select rownum xc , c.* from xx_case c) d
full outer join
(select rownum xb , b.* from xx_box b) e on d.xc = e.xb

Thanks to you all for your precious time on this.


-----Original Message-----
From: Bobak, Mark [] Sent: Wednesday, January 21, 2009 4:31 AM To: Krishan Gupta; Subject: RE: join query

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
---------- -- --

	 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,


From: [] On Behalf Of Krishan Gupta [] Sent: Tuesday, January 20, 2009 12:28 PM To:
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

Received on Wed Jan 21 2009 - 09:13:17 CST

Original text of this message