0

List the number of Judges graduated from each law school. The tables are Presidents, Judges, and Appoints. Judges has columns JName, DateOfBirth, LawSchool. I have tried the following statement and am running into an error I am not sure I understand completely:

SELECT jname, COUNT(jname)
FROM Judges J1, Judges J2
WHERE J1.LawSchool = J2.LawSchool AND J1.Jname != J2.Jname;

The error is: ERROR 1052: Column 'jname' in field list is ambiguous.

lurker
  • 56,987
  • 9
  • 69
  • 103
Joey Clements
  • 1
  • Use `SELECT J1.name`, that is, qualify `name` using one of the aliases. – Tim Biegeleisen Nov 03 '18 at 00:53
  • Ambiguous means that the column (in this case, the jname) was found in more than one table in the query, so you need to specify which table reference should be used to fetch the column, as Tim said. – Paul T. Nov 03 '18 at 00:55
  • 3
    I believe you could use a different statement here, something like `select lawschool, count(*) from judges group by lawschool;` – Nicholas Hirras Nov 03 '18 at 00:56
  • Can you show sample data for the `Judges` table along with the expected output? – Tim Biegeleisen Nov 03 '18 at 01:10
  • Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 03 '18 at 05:02

1 Answers1

0

The field jname exist in more then one table that you are selecting data from (Judges J1 & Judges J2). You have to specify which table you want this data from. But, according to your task, I think the right way to do this is:

SELECT LawSchool, COUNT(*)
FROM Judges
Group By LawSchool
Coral Kashri
  • 3,436
  • 2
  • 10
  • 22