[SOLVED] distinct in a join

[chronister]

HI all,

I have 2 tables called timeclock and clients.

I am going to use this to keep track of time spent on a project for clients with details as to what I did during that time. The timeclock table has the following fields.

id | time_in | time_out | client_id | details

The clients table has the following fields:

client_id | name | address | city | state | zip | phone

I am trying to get the client_id and name for all records. The timeclock table contains many records for each client. I want to get distinct client_id from the timeclock table, and then get their name from the clients table.

I have a join that gives me all records for everything.
SELECT * FROM timeclock as t LEFT JOIN (clients as c) ON (c.client_id=t.client_id)

How would I make this DISTINCT for the client_id on the timeclock table? When I just plopped in distinct, it did not make a difference 

Should I use a subquery for this instead? If so, how would I do this. I was happy I got the join to produce (almost ) the data I wanted. I have no clue where to begin on a subquery.

Any takers?

[artacus]

As you've probably figured out, you can't use distinct here.
If you are looking to get 1 entry for each client (say you want to total the time worked for each client) you would use GROUP BY c.client_id

[chronister]

thank you,

That did what I wanted it to do. At this point I simply want to get the name, and client id where that client id appears in the timeclock table so I can create a link to another page and display more information there.

I just did not want to do 1 query to get the id's from the timeclock table, and then a foreach loop to run more queries to get the name for that client.

I am trying to find the "shortcuts" to mysql, rather than running multiple queries I know that most information can be retrieved with 1 query even if it involves multiple tables.

Thanks again

[artacus]

Ok, usually you'll want to provide more info that just the name. Seeing how much time has been clocked for each client would be useful. Its easier to spot "anomalies" when everything is summarized in one place.

Try this:
ROUND(SUM(TIME_TO_SEC(TIMEDIFF(time_out , time_in))) / 3600,2) AS hrs_worked

Also, I don't see a date field, I don't know if time_in/out are datetime fields or just time. You'll want to store date in there somewhere so you can bill by month.

[chronister]

I am taking care of the additional info on another page. On timeclock.php I have a form that allows me to clock in and out and at the bottom of the page it gives me the names of clients that I have clocked time for the current month as well as the previous month. These names are simply links to timedetails.php with the client id passing in the url.

I got the timedetails.php page working and it gives date(extracted from the timestamp in punch) time in, time out, details of what I did, total hours, minutes and seconds worked and at the bottom of the table I have a total sum for all timeslices. I also have it calculating the invoice by multiplying total number of seconds-3600(I offer 1 hour of time included in monthly price I charge) by .009722 ($35 per hour / 3600).

So all is working here, I also have links that will pull up daily, weekly, monthly, yearly and all timeslices for a particular client_id. I am trying to work through coding the ability to move to previous and next sets for the time frames listed above. The code is messy, but it's coming along.

This is the first time I have ever tried manipulating time and dates like this, so it's a bit daunting but I will prevail over this code.

Thanks 4 the help,

Nate