Home » SQL & PL/SQL » SQL & PL/SQL » Question on duplicates
Question on duplicates [message #254625] Fri, 27 July 2007 05:21 Go to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi,

I have 2 tables, Table Account_A has 500,00 records and Table Account_B has 8000 records when i run the following query
to bring up the records from b table, i get duplicate rows ,

SELECT b.aaccid
     , b.custid
     , b.empname
     , b.bal_start
     , b.bal_end
     , b.account_num
     , b.no_acc
     , b.pdt_cde
     , b.srt_cde
FROM   account_a a
     , account_b b
WHERE  a.accid  <> b.accid 
AND    b.custid <> a.custid


where as when i run the same query to bring up the records if table A it brings up proper records without duplicates.

SELECT a.aaccid
     , a.custid
     , a.empname
     , a.bal_start
     , a.bal_end
     , a.account_num
     , a.no_acc
     , a.pdt_cde
     , a.srt_cde
FROM   account_a a
     , account_b b
WHERE  a.accid <> b.accid 
AND    b.custid <> a.custid


What is the reason for this???, Also Please explain me how to get proper record from the table b using the same condition.

thanks,
gilbert

[Mod-edit]Applied code tags and changed title.

[Updated on: Fri, 27 July 2007 05:36] by Moderator

Report message to a moderator

Re: Question on duplicates [message #254630 is a reply to message #254625] Fri, 27 July 2007 05:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And what makes you think that you are avoiding duplicate records?

To get unique rows you have the keywords DISTINCT and UNIQUE at your disposal. Or you could group the records.

MHE

[Updated on: Fri, 27 July 2007 05:38]

Report message to a moderator

Re: Question on duplicates [message #254638 is a reply to message #254630] Fri, 27 July 2007 05:58 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi Maaher,

Actually i m trying to insert these records of b into another table, where the records of table A and B are not equal.I tried out both the queries wherein i got duplicate records retrieved for the first query where i try to get the records from table B , but when i tried the same query with columns of table A then there were no duplicates records retrieved .



Thanks,
Gilbert
Re: Question on duplicates [message #254668 is a reply to message #254625] Fri, 27 July 2007 07:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First, I do not believe you do not get duplicates in the second case, unless you have very special data. Just display your FULL resultset. Are there duplicates on (accid, custid) in both tables?

You are joining two tables on non-equality of columns. The result will contain ALL combination of (table1, table2) rows with different (accid, custid). Nearly cartesian product.

Just look at a simple example:
SQL> create table t1 ( c1 integer, c2 varchar2(10) );

Table created.

SQL> create table t2 ( c1 integer, c3 varchar2(10) );

Table created.

SQL> insert into t1 ( c1, c2 )
  2  select level, 'A'||to_char(level) from dual connect by level <= 3;

3 rows created.

SQL> insert into t2 ( c1, c3 )
  2  select level, 'B'||to_char(level) from dual connect by level <= 2;

2 rows created.

SQL> select * from t1, t2 where t1.c1 <> t2.c1;

        C1 C2                 C1 C3
---------- ---------- ---------- ----------
         2 A2                  1 B1
         3 A3                  1 B1
         1 A1                  2 B2
         3 A3                  2 B2

SQL> 
Maybe you want to use NOT EXISTS Condition (records from one table not present in the second one)?
Re: Question on duplicates [message #254679 is a reply to message #254668] Fri, 27 July 2007 08:18 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi,


Thank you for your explanations, can you please explain me how can i achieve my task of selecting records from the tables A and B where the a.acc_id <>b.acc_id and a.cus_id<>b.cus.id.


Thanks,
Gilbert
Re: Question on duplicates [message #254682 is a reply to message #254625] Fri, 27 July 2007 08:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Did you understand the example?
Table t1 contains 3 rows (1, 'A1'), (2, 'A2'), (3, 'A3') and table t1 contains 2 rows (1, 'B1'), (2, 'B2'). I join them on the first column (non-equality).
So it selects rows A2 and A3 from t1 for B1 from t2 (as they differ in join column) and A1 and A3 from t1 for B2 from t2 (the same reason).

> can you please explain me how can i achieve my task of selecting records from the tables A and B where the a.acc_id <>b.acc_id and a.cus_id<>b.cus.id

This condition does not make sense to me. Do you really want this?
Do you not rather want records from one table that not present AT ALL in the second one (NOT EXISTS Condition)?
Re: Question on duplicates [message #254683 is a reply to message #254682] Fri, 27 July 2007 08:40 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Thank you, i understood the cartesian product from your example , i also understood what mistake i made, i will use NOT EXIST condition in my query , thanks once again for your help.

thanks,

gilbert
Plz Help!!! [message #255359 is a reply to message #254630] Tue, 31 July 2007 09:15 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi Maaher,

Please explain me what is wrong in the following query

where in i m trying to update values in table XYZ with values from ABDC

UPDATE XYZ m SET
(name,age,sex,id) =
(SELECT x.age,x.sex
FROM ABCD x
WHERE x.name=Y.name AND x.id=Y.id)WHERE Y.id>12 AND
(Y.name<>'EMY' OR Y.name<>'KATE')

the error i get is missing right paranthesis??

thanks,
Gil
Re: Plz Help!!! [message #255377 is a reply to message #255359] Tue, 31 July 2007 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.

Regards
Michel
Re: Plz Help!!! [message #255401 is a reply to message #255359] Tue, 31 July 2007 12:33 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Continue in your other thread.
Previous Topic: Swap 2 variables without using a temperory variable
Next Topic: rows as columns
Goto Forum:
  


Current Time: Fri Dec 09 09:47:31 CST 2016

Total time taken to generate the page: 0.08941 seconds