SQL sort by max Column and Inner join multiple tables

Question

I Have 2 Tables which are

Papers

|ID | Paper_Code | Subject_Code |
| 0 | 2018/Eng/01|     ENG      |
| 1 | 2018/Eng/02|     ENG      |
| 2 | 2018/CS/01 |     CS       |
| 3 | 2018/Sci/01|     Sci      |
| 4 | 2018/Eng/03|     ENG      |

Subjects

|Subject_Code|Subject_Name|
|ENG         |English     |
|Sci         |Science     |
|CS          |Computers   |

I Achieved the following table which only shows the Max Paper_Code per Distinct Subject_Code by using this SQL CODE, which is what i wanted.

$sql = "SELECT Paper_Code FROM papers
       WHERE(Subject_Code,Paper_Code)
       IN(SELECT Subject_Code,MAX(Paper_Code)
       FROM Papers GROUP BY Subject_Code)
        ";

|ID | Paper_Code | Subject_Code |
| 4 | 2018/Eng/03|     ENG      |
| 2 | 2018/CS/01 |     CS       |
| 3 | 2018/Sci/01|     Sci      |

Now I want to INNER JOIN The Subjects Table to give me results like this

|ID | Paper_Code | Subject_Code | Subject_Name |
| 4 | 2018/Eng/03|     ENG      | English      |
| 2 | 2018/CS/01 |     CS       | Computers    |
| 3 | 2018/Sci/01|     Sci      | Science      |

I have tried this but failed

$sql = "SELECT Paper_Code FROM Papers
       WHERE(Subject_Code,Paper_Code)
       IN(SELECT Subject_Code,MAX(Paper_Code)
       FROM Papers GROUP BY Subject_Code)
       INNER JOIN Subjects
       ON Papers.Subject_Code = Subjects.Subject_Code";

jarlh · Accepted Answer · 2018-06-21T17:39:14.230

1

JOIN before WHERE:

EDIT: As Barmar's comment says, qualify all columns:

SELECT p.Paper_Code
FROM Papers p
INNER JOIN Subjects s
    ON p.Subject_Code = s.Subject_Code
WHERE (p.Subject_Code, p.Paper_Code)
   IN (SELECT p2.Subject_Code, MAX(p2.Paper_Code)
       FROM Papers p2
       GROUP BY p2.Subject_Code)

edited Jun 21 '18 at 17:39

answered Jun 21 '18 at 16:58

jarlh

42,561
8
45
63

I tried this but it gives me the error "mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given" – Dreu Davids Jun 21 '18 at 17:20
@DreuDavids That means the query got an error, you should call `mysqli_error()` to get the error message. – Barmar Jun 21 '18 at 17:25
You need to add table qualifiers to the `WHERE` clause. `WHERE (Papers.Subject_Code, Papers.Paper_Code) IN ...` – Barmar Jun 21 '18 at 17:26
@Barmar i did'nt know of this feature it will surely help in the future it says Column 'Subject_Code' in IN/ALL/ANY subquery is ambiguous – Dreu Davids Jun 21 '18 at 17:29
@DreuDavids There are thousands of questions in SO that explain how to resolve that PHP error, and most of them explain that you should look at the MySQL error. E.g. https://stackoverflow.com/questions/2546314/php-mysql-mysqli-num-rows-expects-parameter-1-to-be-mysqli-result-boolean?noredirect=1&lq=1 – Barmar Jun 21 '18 at 17:32
That ambiguity problem is fixed by adding the table qualifiers, as I mentioned above. – Barmar Jun 21 '18 at 17:32
@Barmar Thanks alot, all i needed was to do as you said and add table qualifiers to the WHERE clause and also add the Columns i want selected at the start of the SELECT as well – Dreu Davids Jun 21 '18 at 17:35

SQL sort by max Column and Inner join multiple tables

1 Answers1