Get data from WHERE Postgresql

Question

I have this tables: users contacts and user_providers, this is the structure of each one:

I want to get all the contacts from a specific user id in contacts table and user_provider provider_name in user_providers , i have this query

SELECT c.id, c.contact_name,c.description,c.discharge,c.latitude,c.longitude,c.town,c.country,c.province,
  (SELECT array_agg(DISTINCT cn.number) FROM contact_numbers cn WHERE cn.contact_id = c.id) AS numbers,
  (SELECT array_agg(DISTINCT ce.email)  FROM contact_emails  ce WHERE ce.contact_id = c.id) AS emails
  FROM
   contacts c,
   user_providers po
  WHERE
   c.user_id = 1 and po.provider_name = 'google'
  ORDER BY
   c.id;

But when im trying to get the data it comes like this:

The query doesnt respect the provider_name, it returns all contacts related only with a specific user id.

Image from stored data: contactsTable user_provider Table

I appreciate any help!

Your question is not clear. The first result set is presumably created because all those rows match your filter conditions. — Gordon Linoff
– Gordon Linoff, Commented Jul 31, 2017 at 1:28
can you show the provider_name data for the 3 rows that you have in the picture? — tima
– tima, Commented Jul 31, 2017 at 2:17
Nope, the three rows doesnt have provider_name, they are from a specific user, and i want to get all the contacts from a specific user and provider_name,i will add some photos about the info in the database — Johan Sánchez
– Johan Sánchez, Commented Jul 31, 2017 at 2:41

tima · Accepted Answer · 2017-07-31 15:54:33Z

As per my comment, you are getting the correct results for your query because the tables contacts and user_providers do not have a relationship other than the user_id column.

And since the user_id is the same for all three rows in the contacts table you will always get at least 3 rows. The number of rows you will get will be: number of contacts for specific user id * number of user providers for a specific user id

For example, doing a select without provider_name in the where clause, gets you: 3 (contacts) * 2 (providers)

id  contact_name  provider_name
-------------------------------
1   Vacio         google
2   Vacio2        google
3   Vaciogiogle   google
1   Vacio         facebook
2   Vacio2        facebook
3   Vaciogiogle   facebook

What you can do is create another relationship between contacts and user_providers tables. For example, add a provider_id column to the contacts table where you will store the ID of the provider from the user_providers table. Then you can use the below SQL join the tables and actually filter on the provider_name.

SELECT c.id, c.contact_name, up.provider_name 
FROM contacts c 
LEFT JOIN user_providers up ON c.provider_id = up.provider_id 
WHERE c.user_id = 1 AND up.provider_name = 'google';

Gordon Linoff · Accepted Answer · 2017-07-31 01:30:04Z

0

You need a JOIN between the tables. Simple rule: Never use commas in the FROM clause. Always use proper, explicit JOIN syntax. I think this is you are looking for something like this:

SELECT up.user_id, c.contact_name
FROM contacts c JOIN
     user_providers up
     ON c.id = up.user_id
WHERE up.user_id = 1 AND up.provider_name = 'facebook'       
ORDER BY up.user_id;

However, the JOIN conditions are not obvious from your query.

answered Jul 31, 2017 at 1:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Get data from WHERE Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related