CODEWITHSUNDEEP
mysqlsql
KosherNation
KosherNation

Reputation: 11

MySQL Inline View "unknown fields"

I am presently in a DBS course and I am working on an inline view:

SELECT userId, firstname, lastname, gender
FROM  
(SELECT COUNT(dvdId) dvdId, u.userId
FROM userDVD
JOIN users u ON userDVD.userId = u.userId
GROUP BY userId) as T
WHERE gender = 'F';

When I run the Query it returns the error unknown column in field list. If I try to specify

u.firstname, u.lastname, u.gender

I return the same error. Any thoughts?

Upvotes: 0

Views: 193

Answers (2)

KosherNation
KosherNation

Reputation: 11

SELECT T.userId, T.firstname, T.lastname, T.gender 
FROM (
SELECT users.userId, users.firstname, users.lastname, users.gender
FROM userDVD 
JOIN users ON userDVD.userId = users.userId 
WHERE gender = 'F' GROUP BY userId
 ) as T;

I worked through it and it turns out I didn't realize that because I had to alias my inline view that I needed to specify it in the original select statement. This concept was sadly not covered in my course. I appreciate those who gave helpful tips versus rude comments. So, Thank you Drew and Matt

Upvotes: 1

Matt
Matt

Reputation: 14361

Drew is right that you have a derived table T, but I'll attempt to add some details which might be useful to you as you are in a course.

  • you have a derived table T
  • an outer query (first select)
  • an inner query (second select in brackets)
  • the inner query uses a table alias of u for users.
  • the final columns of the inner query are dvdId which is a count of the dvdIds in userDvd table and userId
  • Because an outer query is constrained to use the final recordset of the inner query only those 2 columns are available to be selected and seeing they don't include firstname, lastname, or gender you are recieving the error.

If you want to keep your query the same but to use those columns you could join your derived table back to the user table and get the columns you desire. Such as this:

SELECT userId, firstname, lastname, gender
FROM  
    (SELECT COUNT(dvdId) dvdId, u.userId
    FROM userDVD
    JOIN users u ON userDVD.userId = u.userId
    GROUP BY userId) as T
    INNER JOIN users u2 
    ON t.user_id = u2.user_id
WHERE gender = 'F';

That technique to join back to the original table is great for when you have to optimize an aggregation that is on a large table or look for duplicates or something like that. However in your case you really just need a single query aggregating on all of the columns you want in the result set such as this:

SELECT
     u.userId, u.firstname, u.lastname, u.gender, COUNT(d.dvdId) as DVDCount
FROM
    userDVD d
    JOIN users u
    ON d.userId = u.userId
WHERE u.gender = 'F'
GROUP BY
    u.userId, u.firstname, u.lastname, u.gender
;

Upvotes: 0

Related Questions