Can I have DISTINCt for two fields?

I have a datbase table like this
caseworker_firstname | caseworker_surname | clients case number |
date_added
and caseworkers can have many many clients but the clients can have
only one casewroker and I want to show these for the office manager to
see who is still busy with a case but he wants only one mention of
each caseworker. Like this

caseworker | client's case number
adam apple | #1234
beta apple | #962
beta orange | #777

The amount of caseworkers and the clients go up and down all the time

I thtink a trouble is there are caseworkers with the same surnames and
caseworkers with the same firstnames. I have tried DISTINCt but it
does not give me the result I want. Is what I want DISTINCT for two
fields? and how do I put DISTINCT for two fields, please?

I have give the code which I copy a lot of bits from a book.

$Query="SELECT caseworker_firstname, caseworker_surname,
clients_case_number FROM personnel order by caseworker_surname,
caseworker_firstname";
$Result=mysql_db_query ($DBName, $Query, $Link);
while ($Row=mysql_fetch_array ($Result))
{
$caseworkers=$Row[caseworker_firstname];
$caseworkers=$Row[caseworker_surname];
}

$count=count($caseworkers);
$n="0";
while ($n<=$count)
{
$caseworker_firstname=$caseworkers[$n];
$n++;
$caseworker_surname=$caseworkers[$n+1];
$n++;

$Query="SELECT caseworker_firstname, caseworker_surname,
clients_case_number, date_added FROM personnel where
caseworker_firstname='$caseworker_firstname' and
caseworker_surname='$caseworker_surname' order by date_added desc
limit 1";
$Result=mysql_db_query ($DBName, $Query, $Link);
while ($Row=mysql_fetch_array ($Result))
{
print("<tr><td>$Row[caseworker_firstname]
$Row[caseworker_surname]</td><td>$Row[clients_case_number]</td></tr>");
}
}

Thank you.

On 18 Dec, 11:07, jobo...@hotmail.com wrote:
> I have a datbase table like this
> caseworker_firstname | caseworker_surname | clients case number |
> date_added
> and caseworkers can have many many clients but the clients can have
> only one casewroker and I want to show these for the office manager to
> see who is still busy with a case but he wants only one mention of
> each caseworker. Like this
>
> caseworker | client's case number
> adam apple | #1234
> beta apple | #962
> beta orange | #777
>
> The amount of caseworkers and the clients go up and down all the time
>
> I thtink a trouble is there are caseworkers with the same surnames and
> caseworkers with the same firstnames. I have tried DISTINCt but it
> does not give me the result I want. Is what I want DISTINCT for two
> fields? and how do I put DISTINCT for two fields, please?
>
> I have give the code which I copy a lot of bits from a book.
>
> $Query="SELECT caseworker_firstname, caseworker_surname,
> clients_case_number FROM personnel order by caseworker_surname,
> caseworker_firstname";
> $Result=mysql_db_query ($DBName, $Query, $Link);
> while ($Row=mysql_fetch_array ($Result))
> {
> $caseworkers=$Row[caseworker_firstname];
> $caseworkers=$Row[caseworker_surname];
>
> }
>
> $count=count($caseworkers);
> $n="0";
> while ($n<=$count)
> {
> $caseworker_firstname=$caseworkers[$n];
> $n++;
> $caseworker_surname=$caseworkers[$n+1];
> $n++;
>
> $Query="SELECT caseworker_firstname, caseworker_surname,
> clients_case_number, date_added FROM personnel where
> caseworker_firstname='$caseworker_firstname' and
> caseworker_surname='$caseworker_surname' order by date_added desc
> limit 1";
> $Result=mysql_db_query ($DBName, $Query, $Link);
> while ($Row=mysql_fetch_array ($Result))
> {
> print("<tr><td>$Row[caseworker_firstname]
> $Row[caseworker_surname]</td><td>$Row[clients_case_number]</td></tr>");
>
> }
> }
>
> Thank you.

I think this is a database query question, not a php one.

joboils@hotmail.com wrote:
> I have a datbase table like this
> caseworker_firstname | caseworker_surname | clients case number |
> date_added
> and caseworkers can have many many clients but the clients can have
> only one casewroker and I want to show these for the office manager to
> see who is still busy with a case but he wants only one mention of
> each caseworker. Like this
>
> caseworker | client's case number
> adam apple | #1234
> beta apple | #962
> beta orange | #777
>
> The amount of caseworkers and the clients go up and down all the time
>
> I thtink a trouble is there are caseworkers with the same surnames and
> caseworkers with the same firstnames. I have tried DISTINCt but it
> does not give me the result I want. Is what I want DISTINCT for two
> fields? and how do I put DISTINCT for two fields, please?
>
> I have give the code which I copy a lot of bits from a book.
>
> $Query="SELECT caseworker_firstname, caseworker_surname,
> clients_case_number FROM personnel order by caseworker_surname,
> caseworker_firstname";
> $Result=mysql_db_query ($DBName, $Query, $Link);
> while ($Row=mysql_fetch_array ($Result))
> {
> $caseworkers=$Row[caseworker_firstname];
> $caseworkers=$Row[caseworker_surname];
> }
>
> $count=count($caseworkers);
> $n="0";
> while ($n<=$count)
> {
> $caseworker_firstname=$caseworkers[$n];
> $n++;
> $caseworker_surname=$caseworkers[$n+1];
> $n++;
>
> $Query="SELECT caseworker_firstname, caseworker_surname,
> clients_case_number, date_added FROM personnel where
> caseworker_firstname='$caseworker_firstname' and
> caseworker_surname='$caseworker_surname' order by date_added desc
> limit 1";
> $Result=mysql_db_query ($DBName, $Query, $Link);
> while ($Row=mysql_fetch_array ($Result))
> {
> print("<tr><td>$Row[caseworker_firstname]
> $Row[caseworker_surname]</td><td>$Row[clients_case_number]</td></tr>");
> }
> }
>
> Thank you.

Fire up mysql command line utility and try it out.

joboils wrote:

> caseworkers can have many many clients but the clients can have only
> one casewroker and I want to show these for the office manager to see
> who is still busy with a case but he wants only one mention of each
> caseworker. Like this
>
> caseworker | client's case number
> ---------------+-----------------------
> adam apple | #1234
> beta apple | #962
> beta orange | #777

When a case worker has multiple case numbers, which case number do you
want to display?

A simple example is:

SELECT
caseworker_firstname,
caseworker_surname,
MAX(clients_case_number) AS clients_case_number
FROM personnel
GROUP BY caseworker_surname, caseworker_firstname
ORDER BY caseworker_surname, caseworker_firstname

However, note that you've got a really badly structured database. How do
you (for example) represent a case that hasn't been assigned a worker yet?
Or a case worker with nothing to do? Or (I know you said there aren't any,
but business structures change) a case that has multiple workers assigned?

A better structure for your tables would be:

Table: employees
worker_id
forename
surname
email

Table: cases
case_id
name
description
date_added

Table: assignments
case_id
worker_id
assignment_started
assignment_finished

And you could create a view that mimics your current table like this:

CREATE VIEW personnel AS
SELECT
e.forename AS caseworker_firstname,
e.surname AS caseworker_surname,
c.case_id AS clients_case_number,
c.date_added
FROM employees e
INNER JOIN assignments a
ON e.worker_id=a.worker_id
INNER JOIN cases c
ON a.case_id=c.case_id
AND a.assignment_started < NOW()
AND COALESCE(a.assignment_finished, '2038-01-01') > NOW()

And the report that you asked for in your original post could be
constructed like this:

SELECT
e.forename AS caseworker_firstname,
e.surname AS caseworker_surname,
a.max_case_id AS clients_case_number
FROM employees e
LEFT JOIN (SELECT
worker_id,
MAX(case_id) AS max_case_id
FROM assignments
WHERE assignment_started < NOW()
AND COALESCE(assignment_finished, '2038-01-01') > NOW()
GROUP BY worker_id) a
ON e.worker_id=a.worker_id

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 10 days, 23:41.]

Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/1...tunes-sharing/