CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloper
Shashi
Shashi

Reputation: 199

Oracle sql one-to-many Join

I'm new to this sql world. After I'm a developer with limited knowledge in SQL & simple joins. I have an issue in writing sql join for one-to-many relationship. Here's my problem say -

For instance if I have a VendorsList table with:

id   Name         address
1    sales        Japan
2    marketing    US

And a VendorContacts table with:

id   vendorid        vendorempname
1    1               Tom
2    1               Bill
3    2               Jessy
4    1               Rachel
5    2               Rob

Now what I want after join is :

vendor_id    Name     address   vendorempname
1            Sales    Japan     Tom
1            Sales    Japan     Bill
1            Sales    Japan     Rachel
2            Marketing US       Jessy
2            Marketing US       Rob

Can any one help in writing join for this please?

Upvotes: 1

Views: 7471

Answers (4)

ngrashia
ngrashia

Reputation: 9904

Try below query. It gives the exact results you are looking for except the order of the reps name..

            WITH VENDORLIST AS 
            (SELECT '1' ID,    'sales' Name,      'Japan'  address FROM DUAL
            UNION
            SELECT '2' ID,    'marketing' Name,      'US'  address FROM DUAL),
            VENDORCONTACTS AS 
            (
            SELECT '1' ID,    '1' vendorid,      'Tom'  vendorempname FROM DUAL
            UNION
            SELECT '2' ID,    '1' vendorid,      'Bill'  vendorempname FROM DUAL
            UNION
            SELECT '3' ID,    '2' vendorid,      'Jessy'  vendorempname FROM DUAL
            UNION
            SELECT '4' ID,    '1' vendorid,      'Rachel'  vendorempname FROM DUAL
            UNION
            SELECT '5' ID,    '2' vendorid,      'Rob'  vendorempname FROM DUAL

            )
            SELECT VENDORID, NAME, ADDRESS, vendorempname FROM VENDORLIST, VENDORCONTACTS
            WHERE VENDORCONTACTS.vendorid = VENDORLIST.ID
            ORDER BY VENDORID, NAME, ADDRESS, vendorempname
            ;

Upvotes: 0

Hardik Patel
Hardik Patel

Reputation: 716

Please have look this:

Select vl.vendorId,vl.name,vl.address,vc.vendorempname
from vendorlist as vl
right join VendorContacts as vc
on vl.vendorId = vc.vendorId
order by vc.vendorId asc

please try this and will get data that you want.

Upvotes: 0

chetan
chetan

Reputation: 2896

  SELECT b.vendor_id, a.Name, a.address, b.vendorempname
    FROM VendorList a, VendorContact b
   WHERE a.id = b.vendor_id
ORDER BY b.vendor_id;

Upvotes: 0

Cristian Meneses
Cristian Meneses

Reputation: 4041

This should do the work

Select v.vendor_id, v.name, v.address, vc.vendorempname
  from VendorContacts vc
  join VendorList v
    on v.id = vc.vendor_id
 order by vc.vendor_id

Upvotes: 3

Related Questions