data base command question

[icarus1]

Hi there,

I have a data base with 2 tables:

table1:

Date           Name          Id          Hours_Clocked
=====================================
10/02/2010  Michael        1           15,3
10/02/2010  Nick            2           10,4
11/02/2010  Michael        1            9,5
12/02/2010  Michael        1           10,8
12/02/2010  George        3             5,2

table2:

Id          Name
=============
1           Michael
2           Nick
3           George

I am using VB.NET and DataGridViews to show some questions to the data base.

For example when I am using

Dim date As String
Dim sqlCommand As String
sqlCommand = "SELECT * FROM table1 WHERE Date = """ & date & """ "

The result in the DataGridView is:
Ex. date = 12/02/2010

Date           Name          Id          Hours_Clocked
=====================================
12/02/2010  Michael        1           10,8
12/02/2010  George        3             5,2

Now my proble is that I need to build a DataGridView like this:

Example: date = 12/02/2010

Date           Name          Id          Hours_Clocked
=====================================
12/02/2010  Michael        1           10,8
12/02/2010  George        3             5,2
                 Nick            2

or another Example: date = 11/02/2010

Date           Name          Id          Hours_Clocked
=====================================
11/02/2010  Michael        1            9,5
                 Nick            2
                 George        3

Can you please help me to syntax the right SQL question???

Thank you.

 

[cjard]

I don't recommend you call your column "Date" because that's the name of a type. Aside from being generic and meaningless, calling columns after types, operators etc has caused problems in the past. Make it more descriptive, like "HireDate" "MeetingDate" "BirthDate" etc


For your question, see the DW2 link in my signature, section "Creating a form to Search Data"

Note that dates in a database are stored as a fractional number of days since some time. Thus a date of 12/10/2008 at 6am is NOT the same date as 12/10/2008 6:00:01

If you go looking for dates with =, you'll get very confused. Use this:

SELECT * FROM table WHERE myDate >= @date1 and myDate < @date2

For your 2 date parameters (read the PQ link in my signature to find out what a parameter is and why you should use it) set values:
@date1 = 12/10/2008
@date2 = 13/10/2008


Note; none of this applies if you have stored your date as a string (a bad idea). If you arent sure of the difference between a Date and a String (that contains a sequence of characters that a human would interpret as a date) then have a google for some OO programming tutorial sections on data types

 

[icarus1]

Hallo and thanks for your reply.

Look my problem wasn't the DateTime format. This is something that has been solved days before. My problem was just the syntax of the SQL command in order to get the right results.

I have almost solve the current problem using the RIGHT JOIN command.

In any case thank you very much for your reply.

Michael.

 

[cjard]

Cali mera!

Look my problem wasn't the DateTime format. This is something that has been solved days before. My problem was just the syntax of the SQL command in order to get the right results.

I can only answer on the information you give. You say you're having a problem getting the right results when searching by date. In my long (long) experience this has nearly ALWAYS been because people have no appreciation of how a database stores dates, and need to be told why a date of 9am on 01 Jan 1901 is not the same data as 10am on the same day

I have almost solve the current problem using the RIGHT JOIN command.

Your post above does not clearly mention at all the need to link this table to another table and the only implication present is in the last code box that (in the mess of formatting) has 2 values missing from the hours_cloked "column" - sorry I missed this pivotal point; I got sidetracked with the issue above (I answer a lot of questions and I do have to read them fairly quickly; there's a lot of into in your post but the presentation is very unclear)


The following SQL will serve your needs:

SELECT
  MIN(hours.Date) as Date,
  names.Id,
  names.Name,
  CASE WHEN SUM(hours.Hours_Clocked) = 0 THEN NULL ELSE SUM(hours.Hours_Clocked) END as Hours_Clocked
FROM
  table2 names
  LEFT OUTER JOIN
  table1 hours
  ON
    hours.id = names.id AND
    hours.date = @date
GROUP BY
  names.ID,
  names.Name

and in your vb.net don't forget to add the parameter:

mySqlCommand.Parameters.AddWithValue("date", myDatePicker.Value.DateValue)

When using outer joins you pick a table that will be the "solid" table (all rows from it) and stick to it only rows you want from another table. A classic case where a lot of people go wrong is in using the WHERE clause on the "non-solid" table:

SELECT * FROM a LEFT OUTER JOIN b on a.id = b.id WHERE b.someCol = value

Consider that the database joins all rows first, putting nulls where it cant make a join (thats what an outer join does) and then test the where. If you forgot to put "OR b.someCol IS NULL" in the where clause, then it discards all the nulls and you just effectively performed an INNER join.
I tend to put my stuff in the ON clause for the join, because it saves having to put the "OR IS NULL" in the where:

SELECT * FROM a LEFT OUTER JOIN b on a.id = b.id AND b.someCol = value
is the same as
SELECT * FROM a LEFT OUTER JOIN b on a.id = b.id WHERE b.someCol = value OR b.someCol IS NULL


The members of the ON can be anything, doesnt have to be columns from the tables. It's just like a "a where clause for that join only - anything that cannot be satisfied is put to null during the join"

 

[cjard]

Note if you want results like:

12/10/2010 1 Mick 0
12/10/2010 2 John 20
12/10/2010 3 Terry 12.4

Your SQL should be:

SELECT
  @date as Date,
  names.Id,
  names.Name,
  SUM(hours.Hours_Clocked) as Hours_Clocked
FROM
  table2 names
  LEFT OUTER JOIN
  table1 hours
  ON
    hours.id = names.id AND
    hours.date = @date
GROUP BY
  names.ID,
  names.Name

 

[icarus1]

cjard,

Thanks for the cali mera. Unfortunately I don't see your country in order to reply in your language, so Kalimera to you too.

As I told you I am very new in VB.NET, but I am trying to learn as more as possible. Actually my job is an Embedded Electronics Engineer. This data base project is a small part of a whole system. The data base is beeing filled by external devices connected to the PC USB ports and add new datas in the data base. The microcontroller of an external device knows only this format:
dd/MM/yyyy.
So there is no way of adding a new data with different Date format.

I am sending you a small data base in Access that includes the Question I was trying to find. In my example you will see the solution I used with the LEFT JOIN command.

Please, if you see something wrong don't laugh with me, because I am very new in SQL. Untill now I was using .txt files to make my databases. This may be very stupid, but my needs usually are not so high.

Of course now that I understand the power of SQL I have changed my mind.

Also, thank you very much for your time and much help.

Have a nice day.

Michael

 

[cjard]

Looks fine, and the query you have there achieves what you want. There's actually a logical problem with mine that doesnt work in Access; clearly I've been working with Oracle too long.. Though there's no way I'd ever go back to Access.. that is one stupid database (you should move to sql server as soon as you can; skip over access completely if you can. free versions of sql server exist and they are much better than access)