CODEWITHSUNDEEP
sqlsql-servert-sqljoin
user3487243
user3487243

Reputation: 193

SQL JOIN multi-part identifier error

I have two tables, Customers [CustomerID, Email] and History[CustomerID, BikeID, Checkout, StationIDout, Checkin, StationIDin].

Given a customer's e-mail, I want to query the History table and obtain all rows containing that email's corresponding CustomerID where the bike has been returned (WHERE Checkin IS NOT NULL), ordering by most recent Checkin date.

I tried the following query but am getting the error:

"The multi-part identifier "[email protected]" could not be bound."

[email protected] exists in the database. I saw other posts explaining how it's either a typo or some column is being wrongly selected, but I don't see my query doing any of these. Is there something else I'm missing? 

email = "[email protected]";

 sql = string.Format(@"
    SELECT BikeID, Checkout, StationIDout, Checkin, StationIDin 
    FROM History INNER JOIN 
    Customers ON Customers.CustomerID = History.CustomerID
    AND Customers.Email = {0} 
    WHERE Checkin IS NOT NULL
    ORDER BY Checkin DESC", email);

Upvotes: 2

Views: 114

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300559

Missing string quotes:

 sql = string.Format(@"
    SELECT BikeID, Checkout, StationIDout, Checkin, StationIDin 
    FROM History INNER JOIN 
    Customers ON Customers.CustomerID = History.CustomerID
    AND Customers.Email = '{0}' 
                          ^   ^
    WHERE Checkin IS NOT NULL
    ORDER BY Checkin DESC", email);

But rather than using concat or format, use parameters

Upvotes: 4

Related Questions