Select Statement Not Working from multiple tables

[daveofgv]

Hello All -

I have created a windows form application for work in vb.net. Since we have different SQL databases and host names - I created it connection to work with My.Settings files. This works perfect....

The problem is trying to query a select statement to 3 different tables...

Right now my select statement is:

da = New OleDbDataAdapter("Select EMPLOYEE.AGE, EMPLOYEE.NAME, EMPLOYEE.JOBSTART, EMPLOYEE.SEX, EMPLOYEE.MILITARY, EMPLOYEE.ETHNIC, EMPLOYEE.STATE, EMPLOYEE.JOBID FROM EMPLOYEE INNER JOIN EMPLOYPREV ON EMPLOYEE.JOBID = EMPLOYPREV.JOBID WHERE EMPLOYEE.ISDELETED = 'false' and EMPLOYPREV.NAME like '%" & Textbox1.Text & "%'", con)

Now, the above code works but I cannot add another WHERE clause that will pull another column that will not effect the above criteria.

For example: If I need all the above criteria but I also want to pull a value from another table (select value from employstats where heightstat = tall)

If I use:

da = New OleDbDataAdapter("Select EMPLOYEE.AGE, EMPLOYEE.NAME, EMPLOYEE.JOBSTART, EMPLOYEE.SEX, EMPLOYEE.MILITARY, EMPLOYEE.ETHNIC, EMPLOYEE.STATE, EMPLOYEE.JOBID FROM EMPLOYEE INNER JOIN EMPLOYPREV ON EMPLOYEE.JOBID = EMPLOYPREV.JOBID WHERE EMPLOYEE.ISDELETED = 'false' and EMPLOYPREV.NAME like '%" & Textbox1.Text & "%'" and INNER JOIN employstats.JOBID ON EMPLOYEE.JOBID WHERE employee.value  = employstats.heightstats = 'tall', con)

I hope this makes sense

If not, I will try to explain it better...... please let me know.

 

[Herman]

First thing that comes to mind looking at your pasted code is "AHHHH MY EYES!".

Having said that, you are not too far off. First here is your first query, formatted properly:

SELECT e.Age, e.Name, e.JobStart, e.Sex, e.Military, e.Ethnic, e.State, e.JobID
FROM Employee e
INNER JOIN EmployPrev ep ON e.JobID = ep.JobID
WHERE e.IsDeleted = 'false'
  AND ep.Name LIKE '%' + @SomeVariable + '%'

Please learn to use indenting, proper casing, aliases, and variables if you expect other people to actually read your queries in the future. Now you will notice the query is suddenly much easier to read. Formatting aside, that first query was formed properly. In the second one, it seems you want to include an additional field from another table in the result set, and add more conditions. In SQL, all your selected fields belong together, all your joins belong together, and all your conditions belong together. Logically, the FROM and JOINs should probably come first, and the SELECT should be last, but that is just how T-SQL was made. Linq addresses that quirk of syntax, but that is an entirely different discussion. So instead of your original form:

SELECT e.Age, e.Name, e.JobStart, e.Sex, e.Military, e.Ethnic, e.State, e.JobID, es.HeightStats
FROM Employee e
INNER JOIN EmployPrev ep ON e.JobID = ep.JobID
INNER JOIN EmployStats es ON e.JobID = es.JobID
WHERE e.IsDeleted = 'false'
  AND ep.Name LIKE '%' + @SomeVariable + '%'
  AND es.HeightStats = 'tall'

That leaves the Employee.Value mystery to solve only.

Also, notice how Name is highlighted? That is a potential bug right there, because "Name" is a reserved word. The best thing to do would be to change the field name to something else, but in case that is not possible, you can enclose Name in brackets ( e.[Name] ) to explicitly tell the compiler it's a field name.

 

[daveofgv]

Thank you for the quick reply. I will be testing this shortly. With the extra WHERE clause what if the "heightstats = tall is not in the database.... Will it still pull all the data from the other two tables? If not, is there a way to do it?

Thanks in advanced

 

[Herman]

If it is in the WHERE clause, it will only pull records where heighstats = 'tall'. If you want all records, just remove that condition.

 

[daveofgv]

Perfect!!! It's working now .

I have one more question though....

Since I am filling the datagrid programmatically, is there a way to change the header name of "value" to say something else?

The table column is named "value" but I need it to say "amount" in my program.

Thanks

 

[Herman]

Use an alias.

SELECT e.Value AS Amount, e.SomeOtherField AS OtherField

 

[daveofgv]

Perfect.... One more question if you don't mind...

If on the value column, how would I add several rows numbers in that column to place in the new "amount"?

 

[Herman]

Maybe a picture or something would help me understand, I don't get what you mean there...

 

[daveofgv]

Let me try this.....

The heighstats column which equals "tall" is in the database several times (up to 6).... the value for all of them will be 0 except for maybe 2 times.... this causes multiple rows in my datagridview with all the same data on both lines except for "value".

Is there a way to have all the data on one row in the datagridview except for the "value" column?

 

[Herman]

What you need here is grouping. Let's take a very simple data set, with two fields:

HeightStats    |    Value
---------------------
Tall        |    1
Tall        |    2
Short        |    1
Tall        |    1
Medium        |    2
Tall        |    1

This give you 6 rows, but only 4 distinct rows:

Tall, 1 (3x)
Tall, 2
Medium, 2
Short, 1

The most basic query to return 6 rows:

SELECT HeightStats, Value
FROM SomeTable

Now if you want to return the 4 distinct rows:

SELECT HeightStats, Value, COUNT(*) AS RowCount
FROM SomeTable
GROUP BY HeightStats, Value

A more in-depth explanation and example is here: SQL GROUP BY Statement