Mysql – Inherit INNER JOIN fields in MySql SELECT QUERY

join;MySQLoptimizationquery-performancesubquery

I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.

Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC reference as shown in the image schema.

SELECT table_A.idC, table_A.nameA, table_B.nameB
FROM table_Ref
INNER JOIN table_A ON table_Ref.idA=tableA.idA
INNER JOIN table_B ON table_Ref.idB=tableB.idB;

My wanted result would be nameA,NameB, and NameC using the idC field in table_A to know which nameC belongs to that idC.

I hope you can help me or give me a reference of this topic :)!

Best Answer

you can join on table_C

SELECT nameC, nameA, nameB
FROM table_Ref r
INNER JOIN table_A a ON r.idA=a.idA
INNER JOIN table_B b ON r.idB=b.idB
INNER JOIN table_C c ON a.idC=c.idC;

Related Solutions

MySQL JOIN query produces wrong result

The problem is the subquery, where you combine in the select list both an aggregated (max(cr.posted_dt)) and non-aggregated expressions/columns from the tables.

MySQL allows you to run this kind of inconsistent queries - with default settings - which basically means it depends on the developer to write consistent queries and not fire themselves on the foot. You can change the sql mode to ONLY_FULL_GROUP_BY and see what happens if you try to run your query.

Now to solve the issue, it seems you want a [greatest-n-per-group] type of query. There are several ways to do this, all quite complicated in MySQL (because it lacks window functions). Check the relevant tags in this site and in the SO main site.

Here's one way:

SELECT c.id,
       c.title,
       c.member_id,
       cr.mem_id    AS last_replier,
       CONCAT(cr.posted_date, 'T', cr.posted_time) AS last_posted_dt
FROM complaints AS c
  LEFT JOIN complaints_reply AS cr
    ON  cr.id = 
        ( SELECT crl.id
          FROM complaints_reply AS crl
          WHERE crl.complaint_id = c.id
            AND crl.mem_id <> c.member_id
          ORDER BY posted_date DESC, posted_time DESC
          LIMIT 1
        ) ;

It will give you consistent results and also be quite efficient, if you add an index on (complaint_id, posted_date, posted_time, mem_id)

Tested at SQLfiddle.

Postgresql – Left join not working with sub-query

You could use a CROSS JOIN from score_sort to all the students and then a LEFT join to score:

SELECT st.student_id, 
       AVG(COALESCE(sc.value, default_value)) AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort AS sort 
  CROSS JOIN student AS st
  LEFT JOIN score AS sc
    ON  sort.subject_id = sc.subject_id 
    AND   st.student_id = sc.student_id
GROUP BY st.student_id ;

A different approach would be to alter your query to count the available scores each student has (in the 5 top subjects):

SELECT score.student_id, 
       (SUM(score.value) + (5 - COUNT(score.subject_id)) * default_value) / 5 
           AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
JOIN score ON score_sort.subject_id = score.subject_id 
GROUP BY score.student_id ;

But even with this, you would still not get any result for the students that have no score in any of the 5 top subjects.

Best Answer

Related Solutions

MySQL JOIN query produces wrong result

Postgresql – Left join not working with sub-query

Related Question