get rows from 3 tables in postgresql

Question

I have a client,clientcontacts and contactphones tables.

each client may have many contacts, each contact may have many phones.

Some of the clients defined as sellers, meaning that sellerid is FK to clientid

I want to write a query where I give the clientid and it returns the seller contacts with phone (if any exists)

example:

clients

clientid  name  sellerid
1          jack
2          jeff
3          robin   1

clientcontacts

contactid  clientid  name
1               1     Robert
2               1     Magen
3               3     Sara
4               3     Rebeca

contactphones

contactphoneid  contactid  phone
1                    1       00522
2                    1       15541
3                    1       555841
4                    3       120
5                    3       121
6                    3       127

if I give clientid=3 The out put should be the contacts & phones of clientid=1 beacuse 1 is the seller of 3 as follows:

Sara    120
Sara    121
Sara    127
Rebeca

i tried the following:

With seller (select sellerid from clients where clientid=INPUT)
select name,phone
from clientcontacts
using seller
left join contactphones on(clientcontacts.contactid=contactphones.contactid)
where clientcontacts.clientid=seller.sellerid

this gives:

ERROR: syntax error at or near "using"

How can I do this query? If possible without WITH it is preffered.

If someone can also explain what excaly is the problem with my query that could be great... I don't understand why the USING is not working here.

yes... couldn't make it work... I wasn't able to get the desired result because you can't do: clientid=Input and clientid=resellerid — Un Peu
– Un Peu, Commented Mar 2, 2016 at 8:29

jarlh · Accepted Answer · 2016-03-02 13:52:36Z

1

First do an inner join to get all clientcontacts that have a contact (with that clientid). Then do a left outer join to pick those clientcontacts' phone number (if available):

select cc.name, cp.phone
from clientcontacts cc
join clients c on cc.clientid = c.clientid
left join contactphones cp on cc.contactid = cp.contactid

where c.clientid = 3

Executes as:

SQL>create table clients (clientid  int, name varchar(10), sellerid int);
SQL>insert into clients values (1,'jack',null);
SQL>insert into clients values (2,'jeff',null);
SQL>insert into clients values (3,'robin',1);
SQL>create table clientcontacts (contactid int, clientid int, name varchar(10));
SQL>insert into clientcontacts values (1,1,'Robert');
SQL>insert into clientcontacts values (2,1,'Magen');
SQL>insert into clientcontacts values (3,3,'Sara');
SQL>insert into clientcontacts values (4,3,'Rebeca');
SQL>create table contactphones (contactphoneid int, contactid int, phone 
SQL&varchar(10));
SQL>insert into contactphones values (1,1,'00522');
SQL>insert into contactphones values (2,1,'15541');
SQL>insert into contactphones values (3,1,'555841'); 
SQL>insert into contactphones values (4,3,'120');
SQL>insert into contactphones values (5,3,'121');
SQL>insert into contactphones values (6,3,'127');
SQL>select cc.name, cp.phone
SQL&from clientcontacts cc
SQL&join clients c on cc.clientid = c.clientid
SQL&left join contactphones cp on cc.contactid = cp.contactid
SQL&
SQL&where c.clientid = 3;
name       phone
========== ==========
Sara       120
Sara       121
Sara       127
Rebeca     -

                  4 rows found

Isn't that what you asked for?

edited Mar 2, 2016 at 13:52

answered Mar 2, 2016 at 8:54

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

1 Comment

Un Peu Over a year ago

This won't work. This simply gives the contacts of clientid=3. This is not what is needed..... I need to see the contacts of clientid=1 when the input is clientid=3

Collectives™ on Stack Overflow

get rows from 3 tables in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related