How to return rows from three tables at once?

Question

How do merge these three queries into one?

1.

SELECT "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid", MIN("TrainerScores"."score") AS "score"
FROM "TrainerScores"
INNER JOIN "Skills" ON "TrainerScores"."skill_id" = "Skills"."id"
WHERE "TrainerScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("TrainerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid"

2.

Select "Skills"."name", "Skills"."id", MIN("PeerScores"."score") AS "score"
FROM "PeerScores"
LEFT OUTER JOIN "Skills" ON "PeerScores"."skill_id" = "Skills"."id"
WHERE "PeerScores"."evaluatee_uid" = 'google:105697533513134511631'
AND DATE("PeerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

3.

Select "Skills"."name", "Skills"."id", MIN("SelfScores"."score") AS "score"
FROM "SelfScores"
LEFT OUTER JOIN "Skills" ON "SelfScores"."skill_id" = "Skills"."id"
WHERE "SelfScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("SelfScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

I want to use this as a report and I do not want to call each one of the queries any time I want to get the data.

@AyoolaSolmon I'm not sure if it is possible to join these three queries, but with sequelize is possible to obtain results in all three in the same function, using callback. — BrTkCa, Oct 29 '15 at 11:47
@LucasCosta how do you think i can go about this with sequelize — Solomon Ayoola, Oct 29 '15 at 11:48
Why don't you want to just use the three queries? What problem do you expect to solve by combining them? — Craig Ringer, Oct 29 '15 at 11:50
@CraigRinger I want to represent the minimum score of each table as an horizontal bar chart and i want to use get the data once and not like three times — Solomon Ayoola, Oct 29 '15 at 11:56
Simply UNION ALL? Either between the three scores tables before LEFT JOIN. Or one big (double) UNION ALL. — jarlh, Oct 29 '15 at 11:58

score 3 · Answer 1 · edited Oct 29 '15 at 13:47

Alternative 1, simply a huge UNION ALL:

SELECT "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid", MIN("TrainerScores"."score") AS "score"
FROM "TrainerScores"
INNER JOIN "Skills" ON "TrainerScores"."skill_id" = "Skills"."id"
WHERE "TrainerScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("TrainerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid"

UNION ALL

Select "Skills"."name", "Skills"."id", NULL, MIN("PeerScores"."score") AS "score"
FROM "PeerScores"
LEFT OUTER JOIN "Skills" ON "PeerScores"."skill_id" = "Skills"."id"
WHERE "PeerScores"."evaluatee_uid" = 'google:105697533513134511631'
AND DATE("PeerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

UNION ALL

Select "Skills"."name", "Skills"."id", NULL, MIN("SelfScores"."score") AS "score"
FROM "SelfScores"
LEFT OUTER JOIN "Skills" ON "SelfScores"."skill_id" = "Skills"."id"
WHERE "SelfScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("SelfScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

@jarih The Union will defeat the purpose. I want to be able to seperate each of the data from the respective tables. — Solomon Ayoola, Oct 29 '15 at 15:11
@AyoolaSolomon, what about adding different literal values to each select? select 'q1', ... UNION ALL select 'q2', ... UNION ALL select 'q3'? Then you'll know from which select every row origins. — jarlh, Oct 29 '15 at 15:14

Erwin Brandstetter · Accepted Answer · 2017-12-15T14:51:24.470

Basically, use UNION ALL like @jarlh already provided.
Details in the manual in the chapter "Combining Queries".

But there is a lot more. My educated guess, you really want this:

WITH vals AS (SELECT timestamp '2015-10-01 00:00' AS ts_low  -- incl. lower bound
                   , timestamp '2015-10-31 00:00' AS ts_hi   -- excl. upper bound
                   , text 'google:105697533513134511631' AS uid)
SELECT s.name, sub.*
FROM  (
   SELECT skill_id AS id, min(score) AS score, 'T' AS source
   FROM   "TrainerScores", vals v
   WHERE  fellow_uid =  v.uid
   AND    created_at >= v.ts_low
   AND    created_at <  v.ts_hi
   GROUP  BY 1

   UNION ALL
   SELECT skill_id, min(score), 'P'
   FROM   "PeerScores", vals v
   WHERE  evaluatee_uid = v.uid
   AND    created_at >= v.ts_low
   AND    created_at <  v.ts_hi
   GROUP  BY 1

   UNION ALL
   SELECT skill_id, min(score), 'S'
   FROM   "SelfScores", vals v
   WHERE  fellow_uid =  v.uid
   AND    created_at >= v.ts_low
   AND    created_at <  v.ts_hi
   GROUP  BY 1
   ) sub
JOIN   "Skills" s USING (id);

Major points

First I trimmed the noise from your syntax (probably produced by your ORM) to make it human-readable: remove redundant double-quotes, add table aliases, trim noise words ...
Your use of LEFT [OUTER] JOIN was broken, since you filter on columns of the left table, which counteracts the LEFT JOIN. Replace with [INNER] JOIN.
Use sargable expressions in the WHERE clause or your query can't use plain indexes and will be very slow for big tables. Related:
- Get difference of another field between first and last timestamps of grouping
Provide your parameters once in a CTE (WITH clause) - which is not needed in a prepared statement where you pass uid, ts_low and ts_hi as parameters instead.
I removed "TrainerScores"."fellow_uid" from the output in your first query to simplify the query. That's just your input parameter anyway.
You can aggregate your respective main tables before you join to "Skills" once.
I added a column source to signify the source of each row.

Aside: It seems like you want to match the whole of October 2015, but then you exclude Oct. 31. Is that on purpose?

One last thing @Erwin Brandstetter, i want the timestamp to be inclusive of the ts_hi — Solomon Ayoola, Oct 29 '15 at 21:54
@AyoolaSolomon: Are you sure of this? `created_at < timestamp '2015-10-31 00:00'` does the same (smarter) as `date(created_at) <= date '2015-10-30'`. (I had missed Oct. 31 in my initial answer and fixed that now!) When dealing with timestamps you typically want to *include* the lower and *exclude* the upper bound. Dates are different because there are no fractional digits. — Erwin Brandstetter, Oct 29 '15 at 22:27
I am sure about that because i want to be able to include the date i am creating the data when am fetching it from the database... Thanks — Solomon Ayoola, Oct 29 '15 at 22:31
In response to your answer ... I am just using that as an example.. Hope you understand — Solomon Ayoola, Oct 29 '15 at 22:35
@AyoolaSolomon: To *include* a certain date, use `created_at < (date '2015-10-30' + 1)` - which is effectively the same as `created_at < timestamp '2015-10-31 00:00'`. — Erwin Brandstetter, Oct 29 '15 at 22:57

score 1 · Answer 3 · answered Oct 29 '15 at 12:03

I propose a solution that does exactly what you want, but it works. Using raw query you can run and get the results of multiple queries, something like this:

var sequelize = require('./libs/pg_db_connect');

var query = "SELECT Skills.name, Skills.id, TrainerScores.fellow_uid, MIN(TrainerScores.score) AS score
FROM TrainerScores
INNER JOIN Skills ON TrainerScores.skill_id = Skills.id
WHERE TrainerScores.fellow_uid = 'google:105697533513134511631' AND DATE(TrainerScores.created_at) BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY Skills.name, Skills.id, TrainerScores.fellow_uid";


sequelize.query(query, {
   type: sequelize.QueryTypes.SELECT
}).success(function (query1) {
   done = _.after(query1.length, function () {
      callback(query1)
   })

   query = "Select Skills.name, Skills.id, MIN(PeerScores.score) AS score
        FROM PeerScores
        LEFT OUTER JOIN Skills ON PeerScores.skill_id = Skills.id
        WHERE PeerScores.evaluatee_uid = 'google:105697533513134511631' AND DATE(PeerScores.created_at) BETWEEN '2015-10-01' AND '2015-10-30'
        GROUP BY Skills.name, Skills.id";

   sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT
   }).success(function (query2) {

      query = "Select Skills.name, Skills.id, MIN(SelfScores.score) AS score
        FROM SelfScores
        LEFT OUTER JOIN Skills ON SelfScores.skill_id = Skills.id
        WHERE SelfScores.fellow_uid = 'google:105697533513134511631' AND DATE(SelfScores.created_at) BETWEEN '2015-10-01' AND '2015-10-30'
        GROUP BY Skills.name, Skills.id";

      sequelize.query(query, {
         type: sequelize.QueryTypes.SELECT
      }).success(function (query3) {
         console.log(query1); // show the returns of query 1
         console.log(query2); // show the returns of query 2
         console.log(query3); // show the returns of query 3
      });

The results of success function of sequelize.query can be storage in the json variable too.

I could go wit this... But is there a way i can have a cleaner query? — Solomon Ayoola, Oct 29 '15 at 15:12

How to return rows from three tables at once?

3 Answers3

Major points