select statement

[raji96]

I have two tables :

Table 1

Code name
306 group1
310 group2
390 group3
520 group4

Table 2
Code details minidetails
310 y n
305 y n
312 y n

My result should be

310 y n group2
305 y n
312 y n

But when I join the tables i am getting

310 y n group2
305 y n group2
312 y n group2

Even though 305,312 does not have records in table1 its appending the value of 310 to 305 and 312. Why is this happening?

 

[carp]

It would help if you would include your query!

 

[bobby13]

I created the table 1 and called it CODE

CODE NAME
---------- ---------------
306 group1
310 group2
390 group3
520 group4
305 group5
312 group6

And Table 2 I called it CODE2

SQL> select * from code2;

CODE DETAILS MINIDETAILS
---------- --------------- ---------------
312 y n
305 y n
310 y n

Is this what you were after:

SQL> select code.code, code.name, code2.code, code2.details
2 from code, code2
3 where code.code = code2.code;

CODE NAME CODE DETAILS
---------- --------------- ---------- ---------------
312 group6 312 y
305 group5 305 y
310 group2 310 y

NOTE: I made the column code in table CODE the primary key and made code in table CODE2 the foreign key


Regards
Bobby

 

[carp]

Your query AND results look fine. However, I notice that your data has changed from your original post.
Are you still having a problem or do you want to get different results?

 

[dbtoo2001]

(carp, the posters where different people, so that's probably why there was different data.)

I'd venture to say that it's a cartesian join.
He probably had something like:

select code, details, minidetails, name
from table1,table2
where name='group2';

which should yield the result set based on the original post.

he probably wanted:

select table2.code, details, minidetails, name
from table1,table2
where name='group2'
and table1.code(+) = table2.code;

This should give back all the codes from table 2 and just the matching group from table1.

 

[Netherbeast]

I think an INNER JOIN is what you need in your code.

 

[ddrillich]

raji96,

Please try:

create table table1 (
code number ,
name varchar2(30)
)


insert into table1 values (301, 'group1')
insert into table1 values (310, 'group2')
insert into table1 values (390, 'group3')
insert into table1 values (520, 'group4')

create table table2 (
code number ,
details char ,
minidetails char
)

insert into table2 values (310, 'y', 'n')
insert into table2 values (305, 'y', 'n')
insert into table2 values (312, 'y', 'n')

select
table2.code, details, minidetails, name
from
table1,table2
where
table1.code(+) = table2.code

And with the new ANSI JOIN syntax:

Either:

select
table2.code, details, minidetails, name
from
table1 left outer join table2
on
table1.code = table2.code

Or

select
code, details, minidetails, name
from
table1 natural right outer join table2

CODE DETAILS MINIDETAILS NAME
310 y n group2
312 y n (nil)
305 y n (nil)

Regards,
Dan

 

[ddrillich]

Sorry, the one before last should be:

select
table2.code, details, minidetails, name
from
table1 right outer join table2
on
table1.code = table2.code

Dan