Question

Showing all records

SELECT statement, JOIN

DATA SET:

Table1
XID YID WRKDATE
--- --- -------
1 NOR 2007-01
2 SOU 2007-03

Table2
XID ZID QTY
--- --- ---
1 PEN 10
1 PAP 20
2 PAP 5
1 PEN 10
2 PAP 5

Result
XID YID WRKDATE ZID QTY
--- --- ------- --- ---
1 NOR 2007-01 PAP 20
1 NOR 2007-01 PEN 10
1 NOR 2007-01 PEN 10
2 SOU 2007-03 PAP 5
2 SOU 2007-03 PAP 5

Not This Result
XID YID WRKDATE ZID QTY
--- --- ------- --- ---
1 NOR 2007-01 PAP 20
1 NOR 2007-01 PEN 10
2 SOU 2007-03 PAP 5

I have tried the following query but kept getting the un-desired result.
Select * from table1, table2 where table1.XID=table2.XID

I have tried joins and still get the un-desired result.

Any hints will be appreciated.

Thanks.

Hi,

The query you posted:

Select * from table1, table2 where table1.XID=table2.XID

should produce the five-row result set (with duplicates) that you want. It does when I run it in Oracle 10. To get the three-line results, I have to say "SELECT DISTINCT ...".

The opposite of "SELECT DISTINCT" is "SELECT ALL". I've never used it before, because it's always been the default, but it could solve your problem. Try:

Select ALL * from table1, table2 where table1.XID=table2.XID