The objects "tblIncident" and "tblIncident" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Question

I don't understand why this is not working. I only have one instance of the word tblIncident in the from claus and one in the select. I am specifying sql server to get my the date and charge description from tbl incident.

 string qry = "SELECT tblIncident.Date , tblCharges.ChargeDescription"
               + " FROM tblIncident"
               + " INNER JOIN tblIncident ON tblJuncIncCharges.IncidentID = tblIncident.IncidentID"
               + " INNER JOIN tblJuncIncCharges ON tblCharges.ChargeID = tblJuncIncCharges.ChargeID"
               + " GROUP BY tblCharges.ChargeDescription ORDER BY tblIncident.Date DESC";
            //+ " WHERE tblIncident.VillageID = @VillageID ORDER BY ChargeCount ASC";
            SqlConnection Connection = ConnectionString.getConnection();
            SqlCommand cmd = new SqlCommand(qry, Connection);

            Connection.Open();
            SqlDataReader Reader = cmd.ExecuteReader();

This is a different query but basically uses the same idea and it works. Please help me understand what I am doing wrong here.

string qry = "SELECT DISTINCT COUNT(tblJuncIncCharges.ChargeID) AS ChargeCount, tblCharges.ChargeDescription"
               + " FROM tblCharges"
                //+ " INNER JOIN tblIncident ON tblJuncIncCharges.IncidentID = tblIncident.IncidentID"
               + " INNER JOIN tblJuncIncCharges ON tblCharges.ChargeID = tblJuncIncCharges.ChargeID"
               + " GROUP BY tblCharges.ChargeDescription ORDER BY ChargeCount DESC";
            //+ " WHERE tblIncident.VillageID = @VillageID ORDER BY ChargeCount ASC";

            SqlConnection Connection = ConnectionString.getConnection();
            SqlCommand cmd = new SqlCommand(qry, Connection);

            Connection.Open();
            SqlDataReader reader = cmd.ExecuteReader();

Answer 1

Where is 

tblJuncIncCharges 

in the second line? You are referencing it without using it in the FROM or JOIN

---

Abdallah El-Chal, PMP

Answer 2

You have tblIncident in there twice.  If you truly intend on self-joining the table (it doesn't really look like you do, but maybe), you need to alias them.

Otherwise, make your first query look like:

 string qry = "SELECT tblIncident.Date , tblCharges.ChargeDescription"
               + " FROM tblIncident"
               + " INNER JOIN tblJuncIncCharges ON tblJuncIncCharges.IncidentID = tblIncident.IncidentID"
               + " INNER JOIN tblCharges ON tblCharges.ChargeID = tblJuncIncCharges.ChargeID "
               + " GROUP BY tblCharges.ChargeDescription ORDER BY tblIncident.Date DESC";

It looks like your from join be for tblIncident, joining first to tblJuncIncCharges, then tblCharges.

Edit: I fixed it.  There was an extra "AND" at the end of the 4th line.

Answer 3

abdshall I am not sure what you mean. I have it in one of the joins. I am not that great at sql I am a C# programmer learning from books as I go along.

dgJohnson

I tried your query and it told me I had incorrect syntax near GROUP BY. I have never seen an AND preceding a groub by so I erased it and now got this error 

"Column 'tblIncident.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Not sure what an aggregate function is or means. I am wondering if there is a way to pull the tblIncident.Date as a string of the month it contains so in C# it would be something like DateTime.ToString("MMMM"); and group by the months instead of the actual date times. 

Answer 4

Your entire query is wrong. How are you referring to tablecharges and you don't have it in your query? The order does make a difference. Why the GROUP BY if you have no aggregate? Can you post your table structure and what is the expected outcome?

---

Abdallah El-Chal, PMP

Answer 5

I will post the immediate structure of the 3 tables. Check the screen shot

What I am trying to achieve is to pull data that is organized by Month (tblIncident.Date) that lists every charge that occured in that month and has a count of instances of that charge that occured that month

So if there are 10 burglaries with 5 occurring in june and 5 in july and 10 thefts 5 in each month as well then I want my data to come back as

Burglary - June - 5

Burglary - July - 5

Theft - June - 5

Theft - July - 5

Answer 6

Sorry, MARV, there is an extra "AND" on the fourth line.  Remove that.

See my edited response above.

Answer 7

To join a table to itself you need to give each table a different name

so it would become something like.

 string qry = "SELECT a.Date , tblCharges.ChargeDescription"
               + " FROM tblIncident a"
               + " INNER JOIN tblIncident b ON tblJuncIncCharges.IncidentID = b.IncidentID"
               + " INNER JOIN tblJuncIncCharges ON tblCharges.ChargeID = tblJuncIncCharges.ChargeID"
               + " GROUP BY tblCharges.ChargeDescription ORDER BY a.Date DESC";
            //+ " WHERE tblIncident.VillageID = @VillageID ORDER BY ChargeCount ASC";

Answer 8

Here is one helpful tip - in C# you can use @ to create long string, which is very convenient for writing SQL queries.

As I understood, your query should be:

string qry = @"SELECT tblIncident.Date , tblCharges.ChargeDescription
                FROM tblIncident
                INNER JOIN tblJuncIncCharges ON tblJuncIncCharges.IncidentID = tblIncident.IncidentID
                INNER JOIN tblCharges ON tblCharges.ChargeID = tblJuncIncCharges.ChargeID 
 ORDER BY tblIncident.Date DESC";

Another helpful tip - if you want to use some inline queries in your code (I actually suggest to use stored procedures when possible instead of direct queries), start from testing the query in SSMS. Once the query is working, move it as is using @ character in C#.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog