join not working - i think!

[wvdba]

Hi.
i have two tables a and b.
table a:
id name
1 john
2 jane
3 jack

table a:
id name
1 john
2 jane
3 jack
5 jim
6 mike
7 tom

i want to see only 1, 2 and 3 to show up on the resut. i'm using join. but it lists all 7 rows no matter what i use.
thanks.

 

[SantaMufasa]

wvdba,

Since you didn't post your query, we are flying blind as to what the problem could be. Here is my posting of what I believe you want. First, the data...Second, the query:

SELECT * FROM A;

        ID NAME
---------- -----
         1 john
         2 jane
         3 jack

3 rows selected.

SELECT * FROM B;

        ID NAME
---------- -----
         1 john
         2 jane
         3 jack
         5 jim
         6 mike
         7 tom

6 rows selected.

SELECT A.ID,A.NAME
  FROM A,B
 WHERE A.ID = B.ID
/

        ID NAME
---------- ------
         1 john
         2 jane
         3 jack

3 rows selected.

Let us know if you still have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[wvdba]

Thanks for the response.
table b may have duplicate ID's - which does.
and table b has dept_nbr also:
1 john shipping
1 john orders
1 john service
2 jane logistics
2 jane communication
3 ....
4 ....
5 ....
6 ....
7 ....
in that case i want to select only 3 rows (1, 2, 3) not 7 rows.
the base table should be 'a'. if 'a' has 3 rows, it should show only 3 rows.
i will post the code:

   select a.id, a.name 
     from a a 
     inner join b on a.id=b.id

 

[SantaMufasa]

Okay...so when I run your code, this is what happens:

select a.id, a.name
     from a a
     inner join b on a.id=b.id
/

        ID NAME
---------- -----
         1 john
         2 jane
         3 jack

3 rows selected.

So how does this differ from what you want?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SkipVought]

how about

select DISTINCT a.id, a.name
...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]