Join Multiple tables in oracle sql

Question

im new to SQL and want to know how to replace the below code with SQL joins.

I want to list all information based on p_id ='123'.

select p.p_name,c.c_name,s.s_name,s.s_contact,b.b_name,b.b_contact 
from product p, category c, seller s, buyer b 
where p.p_id="123" and c.p_id="123" and s.p_id="123" and b.p_id="123";

Tables used

Product Table

p_id
p_name

Category Table

p_id
c_id
c_name

Seller Table

p_id
s_id
s_name
s_contact

Buyer Table

p_id
b_id
b_name
b_contact

Thanks

You are already doing joins, but using an older version of the syntax. Jens' answer has the newer version of the syntax. — jarlh
– jarlh, Commented Jan 19, 2015 at 8:35
Not related, but : 123 is a NUMBER and not a string. Using single-quotation marks would introduce unnecessary implicit conversion. — Lalit Kumar B
– Lalit Kumar B, Commented Jan 19, 2015 at 8:44
And in ANSI SQL "123" is not a string, it's a delimited identifier. (I.e. the column named 123 in this case...) — jarlh
– jarlh, Commented Jan 19, 2015 at 8:49
@jarlh, If you reread my comment, I said single-quotation and not double-quotation marks. Oracle would throw invalid-identifier error. Also read OP's question, he said "I want to list all information based on p_id ='123'." — Lalit Kumar B
– Lalit Kumar B, Commented Jan 19, 2015 at 8:59
@LalitKumarB, yes, and I agree. But after reading your comment I also noticed the "123" in the code example, that's why I wrote my comment. — jarlh
– jarlh, Commented Jan 19, 2015 at 9:15

Jens · Accepted Answer · 2015-01-19 10:16:04Z

1

This is the query using join:

select p.p_name,c.c_name,s.s_name,s.s_contact,b.b_name,b.b_contact 
from product p 
join buyer b  on p.p_id = b.p_id and <second condition>
join category c on  p.p_id = c.p_id
join seller s on c.p_id  = s.p_id
where p.p_id="123" ;

edited Jan 19, 2015 at 10:16

answered Jan 19, 2015 at 8:33

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SMA · Accepted Answer · 2015-01-19 08:37:02Z

0

Try joining all the table with your criteria as below:

SELECT p.p_name,c.c_name,s.s_name,s.s_contact,b.b_name,b.b_contact 
FROM product p INNER JOIN category c
ON   p.p_id = c.p_id
INNER JOIN seller s
ON p.p_id = s.p_id
INNER JOIN buyer b 
ON p.p_id = b.p_id
WHERE p.p_id='123';

answered Jan 19, 2015 at 8:37

SMA

37.2k8 gold badges53 silver badges76 bronze badges

2 Comments

Nisha Over a year ago

if there is AND condition from product and buyer? how to combine in joins

SMA Over a year ago

it should be like ON p.p_id = s.p_id AND p.Nextid = s.nextId

Collectives™ on Stack Overflow

Join Multiple tables in oracle sql

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related