The column prefix 'a' does not match with a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

The column prefix 'a' does not match with a table

Author

Topic

drdream
Starting Member

29 Posts

Posted - 2006-09-11 : 11:29:31

Hello, This SP is for a report that shows container current balances, that part works fine. The bosses wanted to see if there were any pending orders underneath each area so Im trying to do a left join with the orders table but getting the error below. There are many orders per area but not all areas have orders.


SELECT C.ROrg, C.ContainerCapacity, C.ContainerCurrentBalance, LEFT(C.ROrg,1) as District, 
       MO.MaterialOrderQuantity, MO.CreatedBy, MO.CreatedDate, MO.MaterialOrderAmountDelivered, 
       MR.MaterialRequestDistrict AS District, MT.MaterialTypeDescription,
       MR.MaterialRequestArea AS AreaDescription, MR.MaterialRequestStatus

FROM [snow].[dbo].[tblMaterialOrders] MO

INNER JOIN [MHDGlobalData].[dbo].[tblLUDepots] D on C.ROrg = D.ROrg
INNER JOIN [tblMaterialRequests] MR on MR.MaterialRequestID = MO.MaterialRequestID
INNER JOIN [tblLUMaterialTypes] MT on MR.MaterialTypeID = MT.MaterialTypeID
LEFT JOIN  [dbo].[tblDepotMaterialContainers] C on MR.MaterialRequestArea = D.AreaDescription  AND D.MaterialTypeID = MR.MaterialTypeID

WHERE MO.MaterialOrderQuantity > MO.MaterialOrderAmountDelivered

ERROR IS:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'C' does not match with a table name or alias name used in the query.

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 11:36:19

This won't work:


FROM [snow].[dbo].[tblMaterialOrders] MO
INNER JOIN [MHDGlobalData].[dbo].[tblLUDepots] D on C.ROrg = D.ROrg

because the table tblDepotMaterialContainers, aliased to C, is not in scope yet.

Kristen

drdream
Starting Member

29 Posts

Posted - 2006-09-11 : 11:45:56

How do I fix that?

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 12:08:02

Well normally you can change the order of the tables, but that would muck up the logic in your case:

You are saying you want to INNER JOIN from tblMaterialOrders to MHDGlobalData, but MHDGlobalData is based on tblDepotMaterialContainers, but tblDepotMaterialContainers is an OUTER JOIN from something else.

Actually looking at it a bit more it seems rather more screwy!!


LEFT JOIN  [dbo].[tblDepotMaterialContainers] C 
     on MR.MaterialRequestArea = D.AreaDescription  
    AND D.MaterialTypeID = MR.MaterialTypeID

seems a bit unusual to have a JOIN where NONE of the columns in the table (tblDepotMaterialContainers) are referenced. That's probably going to have the effect of creating a Cartesian join, but only where the two criteria are found on the tblMaterialRequests table.

You probably need to describe the FROM clause in plain English and then see if we can suggest what the JOINs should look like.

Kristen

drdream
Starting Member

29 Posts

Posted - 2006-09-11 : 12:22:02

Ok.. I'll try.. Im actually quite new to TSQL and Stored Procedures. But here goes..

Put MHDGlobalData.tblLUDepots, tblDepotMaterialContainers, tblLUMaterialTypes aside as they all work well together and are used in several queries interdependantly. For reference each depot has multiple containers and multiple material types. Again this is not relevant the above tables work together fine.

When it comes to tblMaterialRequest and tblMaterialOrders is where im confused and mickey moused that code above.
The new thing im trying is this.

The tblMaterialOrders table does not have sufficient info to match to an area but tblMaterialRequest has the area info so thats why you see tblMaterialRequest.

Basically in english I have a list of data that looks like this

tblDepotMaterialContainers


DISTRICT     AREA     ROrg     Capacity     Balance   MaterialTypeID
1            1A       1011     3000         1000      1   
2            2B       2022     3000         1400      2 
2            2A       2223     3000         1200      1
3            3B       3233     4000         2000      1
3            3B       3322     5000         3000      2 
1            1B       1221     5000         100       2
4            4A       1234     5000         2000      2

For each area there can be orders which have not been completely delivered so if say area 4A (MR.MaterialRequestArea) has a line in tblMaterialOrders then I would like to see MaterialRequestStatus and MaterialOrderAmountDelivered only on lines where there is a pending order so some Areas/Rows would be blank/null

The reason why im doing it this way is because of report designer. Im new to report designer so I have found that giving it flat data works best as it goes a nice job of grouping stuff for you. I tried doing a subquery but no luck. Perhaps there is a better way to do this but I thought a left join was it

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 12:39:21

OK, if I've "got" this you want to see all areas, PLUS any data about Pending Orders for any given area - if there is any.

In principle that will be:


SELECT MR.MaterialRequestArea,
       MR.MaterialRequestStatus,
       MO.MaterialOrderAmountDelivered -- Will be NULL if no matching MaterialOrders for this tblMaterialRequests
FROM tblMaterialRequests as MR
     LEFT OUTER JOIN tblMaterialOrders as MO
         ON MO.MaterialRequestID = MR.MaterialRequestID

Kristen

drdream
Starting Member

29 Posts

Posted - 2006-09-11 : 12:57:04

Ok thanks so much for your help Kristen.. That helped alot.. but now im still confused about how to get the end result. which is to combine your above query with the original query:


SELECT C.ROrg, M.MaterialTypeDescription AS Material, C.ContainerCapacity, C.ContainerCurrentBalance, LEFT(C.ROrg, 1) as District, 
D.AreaDescription

FROM dbo.tblDepotMaterialContainers C

INNER JOIN dbo.tblLUMaterialTypes M ON C.MaterialTypeID = M.MaterialTypeID
INNER JOIN MHDGlobalData.dbo.tblLUDepots D ON C.ROrg = D.ROrg

WHERE (C.MaterialTypeID = @MaterialTypeID)

-- Below is a permissions thing for end users to see only thier depots.. 
AND LEFT(C.ROrg,1) IN (SELECT DISTINCT LEFT(ROrg,1) FROM (SELECT ROrg From dbo.fnGetROrgsByUserID(@CurrentUserID)) T1)

Which spits out data like this


Rorg    MaterialType    Capaci  Balan   Distri  Area
1701	Salt (CaCl)	2200.00	1675.47	1	1A
1702	Salt (CaCl)	2600.00	1843.78	1	1A
1703	Salt (CaCl)	3800.00	1400.00	1	1A
1704	Salt (CaCl)	2600.00	2000.00	1	1A
1710	Salt (CaCl)	4600.00	4600.00	1	1B
1711	Salt (CaCl)	1400.00	687.33	1	1B
1712	Salt (CaCl)	1200.00	1200.00	1	1B
1713	Salt (CaCl)	3400.00	3400.00	1	1B
1714	Salt (CaCl)	2800.00	185.76	1	1B
2701	Salt (CaCl)	2250.00	1021.00	2	2A
2702	Salt (CaCl)	8000.00	161.59	2	2A

Also as you may notice.. Because there are many ROrg's(depots) per area but orders are for areas not depots so not sure about 1) combining the two queries, and 2) like area 1A has 3 depots which line should have the MaterialRequest Status?

So in the end it should look like


Rorg    MaterialType    Capaci  Balan   Distri  Area  MaterialRequestStatus     MaterialOrderAmountDelivered
1701	Salt (CaCl)	2200.00	1675.47	1	1A    Approved                  1283
1702	Salt (CaCl)	2600.00	1843.78	1	1A    Null?                     Null?
1703	Salt (CaCl)	3800.00	1400.00	1	1A    Null?                     Null?
2704	Salt (CaCl)	2600.00	2200.00	2	2A    Pending                   0
3701	Salt (CaCl)	2600.00	2100.00	3	3A    Approved                  1000
4704	Salt (CaCl)	2600.00	2000.00	4	4B    Pending                   0
5702	Salt (CaCl)	2600.00	2200.00	5	5B    Pending                   0

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 13:10:29

I reckon you can just combine them:


FROM dbo.tblDepotMaterialContainers C
INNER JOIN dbo.tblLUMaterialTypes M ON C.MaterialTypeID = M.MaterialTypeID
INNER JOIN MHDGlobalData.dbo.tblLUDepots D ON C.ROrg = D.ROrg

and


     INNER JOIN tblMaterialRequests as MR
         ON MR.MaterialRequestArea = D.AreaDescription
     LEFT OUTER JOIN tblMaterialOrders as MO
         ON MO.MaterialRequestID = MR.MaterialRequestID

If there MAY not be tblMaterialRequests row for EVERY tblLUDepots row then use an OUTER JOIN, for tblMaterialRequests, instead.

Somehow I doubt its going to be quite that easy though!

I have a non-convention style to Outer Joins, which might help you - my guys tell me they prefer it, once they've got the hang of it, but I rarely see it used. If it helps you fine, if not the "As you were" !!


SELECT *
FROM   Table1 AS T1
       JOIN Table2 AS T2
            ON T2.SomeT2Column = T1.SomeT1Column
           AND T2.OtherT2Column = T1.OtherT1Column

In the JOIN I always put the columns for the table which is the subject of the join first, and I format it over multiple lines as shown. My idea is that I need to satisfy certain criteria in the Target table in order to get the rows I want. Most often its the PK, or some of the columns of the PK (in significant-column-order).

My thinking is that this is easier to debug because you can see what you are trying to "pull" from Table 2 - whereas if its all on one line, OR the T2 columns are on the right, and not neatly aligned, its easier to miss the fact that the wrong columns in T2 are being targeted.

Kristen

drdream
Starting Member

29 Posts

Posted - 2006-09-11 : 13:29:55

You're a genius!

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 13:37:37

I very much doubt it, but rich would be nice ...

drdream
Starting Member

29 Posts

Posted - 2006-09-12 : 09:40:38

There is just one more problem which took all day yesterday trying to figure out.. The MaterialTypeID seems to have no effect on the records returned via tblMaterialRequest and tblMaterialOrders. But when adding

INNER JOIN tblMaterialRequests as MR ON
(MR.MaterialRequestArea = D.AreaDescription)
AND
(MR.MaterialTypeID = C.MaterialTypeID)

I get NO ROWS at all.. NO ROWS from tblDepots I need so see all Depots regardless if there are orders/request for that area/depot. When taking the materialtypeID out.. I do get the proper rows from tblDepots but the joined order/request data is the same regardless of material type.

Kristen
Test

22859 Posts

Posted - 2006-09-12 : 11:57:42

If you want Depots regardless, and MaterialRequests ONLY if there is data for BOTH AreaDescription and MaterialTypeID you need an OUTER JOIN

Kristen

drdream
Starting Member

29 Posts

Posted - 2006-09-12 : 12:10:06

I tried OUTER JOIN on tblMaterialRequest and tblMaterialOrders with no records returned when materialtypeID is specified.. There are no Request/Orders for a certain material type thats why im not seeing any records. But I still need to see all the depots. We really tried to even randomly try inner/outer/left/right and nothing is working. I think it some type of "cancelling each other out" or some type of circular reference?

The Final Code which has no regard for material type in tblMaterialOrders/Request



SELECT MR.MaterialRequestID, C.ROrg, M.MaterialTypeDescription AS Material, C.ContainerCapacity, C.ContainerCurrentBalance, LEFT(C.ROrg, 1) as District, 
D.AreaDescription, MR.MaterialRequestStatus, MO.MaterialOrderQuantity as ApprovedAmount, MO.MaterialOrderAmountDelivered, (MO.MaterialOrderQuantity-MO.MaterialOrderAmountDelivered) as AmountAvailable

FROM dbo.tblDepotMaterialContainers AS C

INNER JOIN dbo.tblLUMaterialTypes M ON C.MaterialTypeID = M.MaterialTypeID
INNER JOIN MHDGlobalData.dbo.tblLUDepots D ON C.ROrg = D.ROrg AND C.ROrg = '2710'
LEFT OUTER JOIN  tblMaterialRequests as MR ON 
 (MR.MaterialRequestArea = D.AreaDescription)
  AND
 (MR.MaterialTypeID = C.MaterialTypeID)

LEFT OUTER JOIN tblMaterialOrders as MO ON (MO.MaterialRequestID = MR.MaterialRequestID) 

WHERE 
 (C.MaterialTypeID = MR.MaterialTypeID) 
 AND
 (
  MR.MaterialRequestStatus <> 'Pending'
 ) 
 AND
 MO.MaterialOrderAmountDelivered < MR.MaterialRequestQuantityApproved

Kristen
Test

22859 Posts

Posted - 2006-09-12 : 12:27:28

You can't have an OUTER join and then some stuff like that in the WHERE because that, in effect, turns it into an Inner Join ...

... OUTER JOIN means you get the data, if there is an, or NULLs. So if you put

C.MaterialTypeID = MR.MaterialTypeID

in the WHERE you will only get records where the MR.MaterialTypeID matches the tblLUMaterialTypes value - and thus you will NOT get any which are NULL!

Two choices:

1) Use stuff like

(C.MaterialTypeID = MR.MaterialTypeID OR MR.MaterialTypeID IS NULL)

which is a bit dangerous if MR.MaterialTypeID can be NULL in ordinary tblMaterialOrders records, rather than just when there is no data found and the OUTER JOIN is making it NULL!! - This column might be NOT NULL and thus there will never be any Nulls in the data, but even if it is this will bite you in the ar$e next time around!

so:

2) Include it in the outer join - so tblMaterialRequests rows will ONLY be selected if they match all the criteria, but tblDepotMaterialContainers row will be matched in all circumstances. Something like:


FROM dbo.tblDepotMaterialContainers AS C
INNER JOIN dbo.tblLUMaterialTypes M ON C.MaterialTypeID = M.MaterialTypeID
INNER JOIN MHDGlobalData.dbo.tblLUDepots D ON C.ROrg = D.ROrg AND C.ROrg = '2710'
LEFT OUTER JOIN  tblMaterialRequests as MR
     ON MR.MaterialRequestArea = D.AreaDescription
    AND MR.MaterialTypeID = C.MaterialTypeID
    AND MR.MaterialRequestStatus <> 'Pending'
LEFT OUTER JOIN tblMaterialOrders as MO 
     ON MO.MaterialRequestID = MR.MaterialRequestID
    AND MO.MaterialOrderAmountDelivered < MR.MaterialRequestQuantityApproved

The slight snag with this is that tblMaterialRequests will be selected with tblMaterialOrders being all NULL if the MaterialOrderAmountDelivered < MaterialRequestQuantityApproved test fails - which is probably not what you want.

There is a syntactical way around this which is to make the tblMaterialOrders and INNER JOIN of tblMaterialRequests, rather than an OUTER JOIN of the whole query:


FROM dbo.tblDepotMaterialContainers AS C
INNER JOIN dbo.tblLUMaterialTypes M ON C.MaterialTypeID = M.MaterialTypeID
INNER JOIN MHDGlobalData.dbo.tblLUDepots D ON C.ROrg = D.ROrg AND C.ROrg = '2710'
LEFT OUTER JOIN
    (
        tblMaterialRequests as MR
        INNER JOIN tblMaterialOrders as MO 
            ON MO.MaterialRequestID = MR.MaterialRequestID
           AND MO.MaterialOrderAmountDelivered < MR.MaterialRequestQuantityApproved
    )
     ON MR.MaterialRequestArea = D.AreaDescription
    AND MR.MaterialTypeID = C.MaterialTypeID
    AND MR.MaterialRequestStatus <> 'Pending'

This will only select rows from tblMaterialRequests if the join to tblMaterialOrders can also be satisfied, otherwise values for columns from BOTH tables will show as NULL.

Kristen

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:02:41

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources