CODEWITHSUNDEEP
sql-server
user10466320
user10466320

Reputation: 23

FROM Clause Subquery Add a Column from Another Table

I am having trouble wrapping my head around a subquery in the FROM clause. I have a subquery that I want to add to the FROM clause. I only added the nconst column, then JOIN it to the name_basics table with the column nconst and it does everything but what it should output. Why would that be? It makes no sense that the code should mess up, all I am doing is adding another column, not manipulating the code (as far as I know).

Also, it has to be in the FROM clause for the assignment. I am just looking for clarification. I know how the FROM clause works when linking direct keys for the output column. For this, I don't want to show the nconst value in the output and that is where I am lost.

The output should be the name of everyone in that profession. The NP subquery originally doesn't have nconst values in it.

Here is my code

SELECT  primaryName, profession
   FROM (
        SELECT      profession, COUNT(*) AS TOTAL, nconst
        FROM        name_profession
        GROUP BY    profession, nconst
        HAVING      COUNT(*) < 1000             
    ) AS NP
JOIN    
    (
        SELECT      primaryName, nconst
        FROM        name_basics
    ) AS NB
ON      NP.nconst = NB.nconst
ORDER BY primaryName;

Upvotes: 0

Views: 2552

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35623

Just join the table itself

SELECT  ??.primaryName, ??.profession, GP.total
FROM (
        SELECT      profession, COUNT(*) AS TOTAL, nconst
        FROM        name_profession
        GROUP BY    profession, nconst
        HAVING      COUNT(*) < 1000             
    ) AS GP
INNER JOIN  name_basics AS NB ON GP.nconst = NB.nconst
ORDER BY primaryName;

But please use table aliases in the select and where clauses now - always. Replace the ?? seen above with appropriate table aliases.

If you are joining to a subquery, and you need a specific column from that, you MUST include that column in the select clause of the subquery. i.e. the outer query only has access to the selected columns in that subquery.

If having issues with the join, display more so you can debug:

SELECT
    NB.primaryName, GP.profession, GP.nconst, GP.total
FROM name_basics AS NB
LEFT JOIN (
        SELECT
            profession, COUNT(*) AS TOTAL, nconst
        FROM name_profession
        GROUP BY
            profession
          , nconst
        HAVING COUNT(*) < 1000
    ) AS GP
    ON GP.nconst = NB.nconst
ORDER BY
    primaryName;

for investigating the impact of nconst in the subquery, perhaps this will help:

Please Compare the results of this:

    SELECT
        profession, COUNT(*) AS TOTAL
    FROM name_profession
    GROUP BY
        profession
    HAVING COUNT(*) < 1000

and this:

    SELECT
        profession, COUNT(*) AS TOTAL, nconst
    FROM name_profession
    GROUP BY
        profession
      , nconst
    HAVING COUNT(*) < 1000

Now consider how the second query (using an inner join) will affect work with this:

SELECT DISTINCT
    NB.primaryName, NB.nconst
FROM name_basics AS NB

Upvotes: 1

Related Questions