Getting results from two different tables using a join

Question

Let's say I have the following tables:

+-------------------------------------------+
| t_classroom                               |
+-------------------------------------------+
| PK | id                                   |
|    | admin_user_id                        |
|    | name                                 |
|    | students                             |
+-------------------------------------------+

+-------------------------------------------+
| t_shared                                  |
+-------------------------------------------+
|    | admin_user_id                        |
|    | classroom_id                         |
|    | expiry                               |
+-------------------------------------------+

I want to write a query that will pull all classrooms that an admin_user_id has access to. In essence, I want a union of classroom rows when I search by admin_user_id in the t_classroom table as well as classroom rows when I search by admin_user_id in the t_shared table. I made the following attempt:

  SELECT
    id,
    admin_user_id,
    name,
    students
  FROM
    t_classroom
  WHERE
    admin_user_id = 1
  UNION ALL
  SELECT
    c.id,
    c.admin_user_id,
    c.name,
    students
  FROM
    t_classroom c
    INNER JOIN t_shared s 
    ON c.id = s.classroom_id
  WHERE
    admin_user_id = 1

Does the above look correct? Is there anything more efficient/cleaner?

would you please provide some sample data and result??

Esty
– Esty

2016-10-04 04:37:43 +00:00
Commented Oct 4, 2016 at 4:37 — Esty
– Esty, Commented Oct 4, 2016 at 4:37

Phill · Accepted Answer · 2016-10-04 04:46:52Z

1

Depending on how much data you have you could probably get away with just using an IN clause to look at the other table.

  SELECT
    c.id,
    c.admin_user_id,
    c.name,
    c.students
  FROM
    t_classroom c
  WHERE
     c.admin_user_id = 1 
  OR c.id IN ( select s.classroom_id from t_shared s where s.admin_user_id = 1 )

Your union wont work because you're left-joining to the t_shared table and checking only the classroom admin user.

If you join the shared room you would also end up with duplicates and would need to distinct the result too.

Edit:

Because of the large number of rows it might be better to use an exists check on the 2nd table.

  SELECT
    c.id,
    c.admin_user_id,
    c.name,
    c.students
  FROM
    t_classroom c
  WHERE
     c.admin_user_id = 1 
  OR EXISTS ( select 1 from t_shared s where s.classroom_id = c.id AND s.admin_user_id = 1 )

edited Oct 4, 2016 at 4:46

answered Oct 4, 2016 at 4:01

Phill

19k7 gold badges66 silver badges104 bronze badges

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

1 Comment

John Baum Over a year ago

Just to give an indication of size, both tables would be in the 100s of millions of rows each

Laurenz Albe · Accepted Answer · 2016-10-04 08:44:27Z

0

Your solution is fundamentally fine, the only two problems I can detect when eyeballing your query are:

You need to write s.admin_user_id instead of admin_user_id in the last line to avoid an error message, because there is a column of that name in both tables. Best practice is to always qualify column names with the table names.
You might want to use UNION instead of UNION ALL if you want to avoid a duplicate result row in the case that both tables have admin_user_id = 1 for the same classroom.

answered Oct 4, 2016 at 8:44

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

Getting results from two different tables using a join

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related