Showing all records

15 pts.
Tags:
JOIN statement
SELECT statement
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TeamJR3
    Thanks Frank. Actually, by you confirming that this does work, I was able to see the problem. Unfortunately, I am only an end-user and do not have the tools to create test tables. On my question, I have used ficticious column names. But, what the problem was when I did the query .. "select table.*, table2.ZID, table2.QTY from table1, table2 where table1.XID=table2.XID", it yielded the un-desired result. However, there is also a unique identifier in table2 with the column name of LID. When I added that to the query, due to the distinct value in LID, it gave me the result that I needed. Thanks for your help.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following