Can I have DISTINCt for two fields?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a datbase table like this
caseworker_firstname | caseworker_surname | clients case number |
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,
$Result=mysql_db_query ($DBName, $Query, $Link);
while ($Row=mysql_fetch_array ($Result))

while ($n<=$count)

$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))

Thank you.

Re: Can I have DISTINCt for two fields?

On 18 Dec, 11:07, wrote:
Quoted text here. Click to load it

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

Re: Can I have DISTINCt for two fields? wrote:
Quoted text here. Click to load it

Fire up mysql command line utility and try it out.

Re: Can I have DISTINCt for two fields?

joboils wrote:

Quoted text here. Click to load it

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

A simple example is:

        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

    Table: cases

    Table: assignments

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

    CREATE VIEW personnel AS
        e.forename AS caseworker_firstname,
        e.surname AS caseworker_surname,
        c.case_id AS clients_case_number,
    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:

        e.forename AS caseworker_firstname,
        e.surname AS caseworker_surname,
        a.max_case_id AS clients_case_number
    FROM employees e
            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, up 10 days, 23:41.]

                      Sharing Music with Apple iTunes

Site Timeline