CODEWITHSUNDEEP
sqlpostgresqljoindistinctexists
Ashley I.
Ashley I.

Reputation: 137

How to find distinct users in multiple tables

I have a table called users that holds users ids, as well as a few tables like cloud_storage_a, cloud_storage_b and cloud_storage_c. If a user exists in cloud_storage_a, that means they are a connected to cloud storage a. A user can exist in many cloud storages too. Here's an example:

users table:

user_id | address      | name
-------------------------------
123     | 23 Oak Ave   | Melissa
333     | 18 Robson Rd | Steve
421     | 95 Ottawa St | Helen
555     | 12 Highland  | Amit
192     | 39 Anchor Rd | Oliver

cloud_storage_a:

user_id
-------
 421
 333

cloud_storage_b:

user_id
-------
 555

cloud_storage_c:

user_id
-------
 192
 555

Etc.

I want to create a query that grabs all users connected on any cloud storage. So for this example, users 421, 333, 555, 192 should be returned. I'm guessing this is some sort of join but I'm not sure which one.

Upvotes: 1

Views: 68

Answers (4)

JNevill
JNevill

Reputation: 50064

You are close. Instead of a JOIN that merges tables next to each other based on a key, you want to use a UNION which stacks recordsets/tables on top of eachother.

SELECT user_id FROM cloud_storage_a
UNION
SELECT user_id FROM cloud_storage_b
UNION
SELECT user_id FROM cloud_storage_c

Using keyword UNION here will give you distinct user_id's across all three tables. If you switched that to UNION ALL you would no longer get Distinct, which has it's advantages in other situations (not here, obviously).

Edited to add:

If you wanted to bring in user address you could use this thing as a subquery and join into your user table:

SELECT
    subunion.user_id
    user.address
FROM
    user
    INNER JOIN
        (
            SELECT user_id FROM cloud_storage_a
            UNION
            SELECT user_id FROM cloud_storage_b
            UNION
            SELECT user_id FROM cloud_storage_c
        ) subunion ON
            user.user_id = subunion.user_id

That union will need to grow as you add more cloud_storage_N tables. All in all, it's not a great database design. You would be much better off creating a single cloud_storage table and having a field that delineates which one it is a, b, c, ... ,N

Then your UNION query would just be SELECT DISTINCT user_id FROM cloud_storage; and you would never need to edit it again.

Upvotes: 4

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657052

To get columns from your users table for all (distinct) qualifying users:

SELECT * -- or whatever you need
FROM   users u
WHERE  EXISTS (SELECT 1 FROM cloud_storage_a WHERE user_id = u.user_id) OR
       EXISTS (SELECT 1 FROM cloud_storage_b WHERE user_id = u.user_id) OR
       EXISTS (SELECT 1 FROM cloud_storage_c WHERE user_id = u.user_id);

To just get all user_id and nothing else, @JNevill's UNION query looks good. You could join the result of this to users to the same effect:

SELECT u.* -- or whatever you need
FROM   users u
JOIN  (
   SELECT user_id FROM cloud_storage_a
   UNION
   SELECT user_id FROM cloud_storage_b
   UNION
   SELECT user_id FROM cloud_storage_c
   ) c USING user_id);

But that's probably slower.

Upvotes: 0

Adnan Yaqoob
Adnan Yaqoob

Reputation: 66

select u.* from users u,
 cloud_storage_a csa,
 cloud_storage_b csb,
 cloud_storage_c csc 
where u.user_id = csa.user_id or u.user_id = csb.user_id or u.user_id = csc.user_id

You should simplify your schema to handle this type of queries.

Upvotes: 1

Egor Rogov
Egor Rogov

Reputation: 5398

You need to join unknown(?) number of tables cloud_storage_X this way.

You'd better change your schema to the following:

storage:

user_id cloud
------- -----
 421     a
 333     a
 555     b
 192     c
 555     c

Then the query is as simple as this:

select distinct user_id
from   storage;

Upvotes: 1

Related Questions