The Multi-Part Identifier Could Not Be Bound - Subquery

The multi-part identifier could not be bound - SubQuery

you can't reference from JOIN clause to another part of JOIN.

Use this instead.

SELECT * 
FROM TableA a
INNER JOIN TableB b
    ON b.B1=a.A1
INNER JOIN TableC c
    ON d.C2=b.B2
      AND c.C1=b.B1

EDITED

SELECT * 
FROM TableA a
INNER JOIN TableB b ON b.B1=a.A1
WHERE b.B2 = (SELECT TOP 1 c.C2 
               FROM TableC c
               WHERE c.C1=b.B1 ORDER BY c.C1)

For further use of TableC in JOIN-s you can use this.

SELECT * 
FROM TableA a
    INNER JOIN TableB b
        ON b.B1=a.A1
    INNER JOIN
       (
           SELECT
               ROW_NUMBER() OVER (PARTITION BY C1 ORDER BY C2) RN,
               C2
               --, other columns fromTableC if needed
           FROM TableC
       ) CC
     ON b.B2 = CC.C2
       AND CC.RN = 1

SQL Server Subquery Error - The Multi-Part Identifier Could Not Be Bound

a Is not available at the top level of your select; it was lost at the moment b was created. If you want to reference columns in a, the subquery b would have to select them and then you call them b.whatever. Because you're grouping you might not realistically be able do this because when you select them at the level of b you'll either have to add them to the group by (in which case they will cause subdivisions of the group) or add them as an aggregate (in which case they will become mixed with other row values an no longer represent) , so you'll have to join the table again at the outer level.

I'm not really sure what the double grouping you have is achieving because it groups and sums then filters and groups and sums again. If you had this:

A,B,C
X,Y,2
X,Y,3
X,Z,4
A,B,1

And you group by A,B, summed C then where only X then group by just A and summed again it would go from:

--group by A B
A,B,C
X,Y,5
X,Z,4
A,B,1

To:

--where x
A,B,C
X,Y,5
X,Z,4

To:

--group by a   
A,C
X,9

But you might as well have just WHERE X GROUP BY A SUM C - the first groupby didn't give you anything

The key point here is that, aside from not being able to use columns at an outer level if they weren't selected at an inner level, once you've lost detail in a grouping operation, you can't get it back (it's like huge lossy compression of an image; once degraded it will always look blocky and pixelated no matter what the movies "I need that grey square of a license plate enhanced and readable" tell you); you have to go back to the source data with the detail and join your grouped data onto it via whatever columns you have left as a group

As such I think you can rewrite your query like this:

SELECT 
    *
 FROM
    AWS_Stage a
    INNER JOIN
     (SELECT 
          yr,
          part_number,
          SUM(a.gross) AS sumgross2019
      FROM
          AWS_Stage
      WHERE  
          yr = 2019
      GROUP BY 
          yr, part_number
      ) x
     ON a.yr = x.yr AND a.part_number = x.part_number

But it's kinda hard to know whether this is what you want from "I tried this non working thing" and "top 50 sku" (why is 2019 in the query when the spec didn't say "from last year" etc).

If you're looking for "all of the 2019 detail plus the sum of all the gross per partnumber for 2019" this is it (except for the top 50 part, which I'm sure you can add). It can also be written using a window function ..

SELECT 
    *,
    SUM(gross) OVER(PARTITION BY a.part_number) as sumgrossperpartnumber
FROM
    AWS_Stage a
WHERE 
    a.yr = 2019

If you're looking for multi level or multi factor grouping, the window function way there might be simpler. If you want to compare years, move yr out of the where clause and add it into the partition by instead.
If you wanted all the detail from all years, but only grossing up from last year you can:

SELECT 
    *,
    SUM(CASE WHEN yr = 2019 THEN gross ELSE 0 END) OVER(PARTITION BY a.part_number) as sumgrossperpartnumber
FROM
    AWS_Stage a

TSQL multi-part identifier could not be bound (explicit join for select x table in inner join)

Move your subquery's WHERE clause to the join's ON clause. Alias 'A' is not defined in your subquery.

SELECT A.OrderID
     , A.ItemID
     , A.Line
     , B.LID
     , B.Quantity 
FROM Sales A 
INNER JOIN (SELECT X.OrderID
                 , X.Line
                 , SUM(X.Quantity) AS Quantity
                 , X.DestinationID
                 , X.SerialNum 
            FROM SalesDetail X 
            --WHERE X.OrderID = A.OrderID 
            GROUP BY X.OrderID, X.Line, X.DestinationID, X.SerialNum
           ) AS C
            ON A.Line = C.Line 
           AND C.OrderID = A.OrderID --<-- move the filter here
           AND B.Quantity = C.Quantity 
           AND B.SerialNum = C.SerialNum
INNER JOIN SalesContents B 
            ON A.OrderID = B.OrderID 
           AND A.ItemID = B.ItemID
WHERE A.OrderID = '12345'
-- you could also put it here instead
AND  A.ItemID = 'ABC123';

Multiple select statements with joins, Multi-part identifier could not bound

When you have a derived query like the above (creating a temp table using another query), the aliases are local. The below tables can only be called within the brackets of your FROM:

ROAD R
INNER JOIN CITY C1 
ON C1.ID = R.IDfrom
INNER JOIN CITY C2
ON C2.ID = R.IDto

At the end you have alised that derived table to R, which means that any data you need from within the brackets needs to be aliased to R (See Below)

 SELECT  R.MAPID,  R.IDFrom, R.IDTo, R.Distance
 FROM (
       SELECT
          R.MapID as MAPID,  C1.Name as IDFrom, C2.Name as IDTo, R.Distance,
          ROW_NUMBER() OVER (PARTITION BY R.MapID ORDER BY R.distance DESC) AS seqnum
       FROM ROAD R
       INNER JOIN CITY C1 
       ON C1.ID = R.IDfrom
       INNER JOIN CITY C2
       ON C2.ID = R.IDto 
 ) R
 WHERE R.seqnum = 1

It is also best practice to alias all Columns throughout the query so I have added R.seqnum = 1

The multi-part identifier could not be bound in SQL Server and it confused

Don't mix implicit and explicit joins! Matter of fact, don't use implicit joins: this is archaic syntax, that should not appear in new code.

The comma in the FROM clause should (probably) be a CROSS JOIN:

SELECT stu.sno, sname, cname
FROM sc scc
CROSS JOIN (SELECT AVG(sc.grade) AS avg_grade FROM sc GROUP BY sc.cno) AS avg_grades
INNER JOIN course c on c.cno = scc.cno
INNER JOIN s stu on stu.sno = scc.sno;

Note that, for this subquery to be useful, you would probably need to select column avg_grade. I would also recommend prefixing each column with the table it belongs to, to remove any possible ambiguity.

Finally: you (probably) can use window functions instead of a subquery:

SELECT stu.sno, sname, cname, scc.
FROM (SELECT *, AVG(grade) OVER() avg_grade FROM sc) scc
INNER JOIN course c on c.cno = scc.cno
INNER JOIN s stu on stu.sno = scc.sno;

The multi-part identifier could not be bound (join sub query)

try this,

SELECT l.id, l.foo, r.id, r.foo
FROM   storyevents l 
         INNER JOIN storyevents r 
               ON l.id = r.id
         RIGHT JOIN 
         (
               SELECT distinct extid, foo 
               FROM storyevents
         ) tmp on l.foo = tmp.foo AND
                  tmp.extid = l.id
where l.foo = 12345

The multi-part identifier could not be bound

You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly.

The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the 'comma' joins, where the join condition is specified in the WHERE clause).

Here's an outline of your query:

SELECT
  …
FROM a, b LEFT JOIN dkcd ON …
WHERE …

You are probably expecting it to behave like this:

SELECT
  …
FROM (a, b) LEFT JOIN dkcd ON …
WHERE …

that is, the combination of tables a and b is joined with the table dkcd. In fact, what's happening is

SELECT
  …
FROM a, (b LEFT JOIN dkcd ON …)
WHERE …

that is, as you may already have understood, dkcd is joined specifically against b and only b, then the result of the join is combined with a and filtered further with the WHERE clause. In this case, any reference to a in the ON clause is invalid, a is unknown at that point. That is why you are getting the error message.

If I were you, I would probably try to rewrite this query, and one possible solution might be:

SELECT DISTINCT
  a.maxa,
  b.mahuyen,
  a.tenxa,
  b.tenhuyen,
  ISNULL(dkcd.tong, 0) AS tongdkcd
FROM phuongxa a
  INNER JOIN quanhuyen b ON LEFT(a.maxa, 2) = b.mahuyen
  LEFT OUTER JOIN (
    SELECT
      maxa,
      COUNT(*) AS tong
    FROM khaosat
    WHERE CONVERT(datetime, ngaylap, 103) BETWEEN 'Sep 1 2011' AND 'Sep 5 2011'
    GROUP BY maxa
  ) AS dkcd ON dkcd.maxa = a.maxa
WHERE a.maxa <> '99'
ORDER BY a.maxa

Here the tables a and b are joined first, then the result is joined to dkcd. Basically, this is the same query as yours, only using a different syntax for one of the joins, which makes a great difference: the reference a.maxa in the dkcd's join condition is now absolutely valid.

As @Aaron Bertrand has correctly noted, you should probably qualify maxa with a specific alias, probably a, in the ORDER BY clause.

---

Related Topics