Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zoikina
Starting Member
5 Posts |
 Posted - 2005-10-20 : 11:07:52
|
| Dear all,In general, I know how to use the MAX(date) function in a simple query, the way all the books explain it. However, I now have something a little bit more complex, and I can't seem to make it work. I have a table called transactions, that records various transactions that all our clients do. I have another table called clients, for basic client information, and then one called contacts (one client can have multiple contacts, and contacts have moved from client to client, so we put the client), and then information on countries and regions. I'm trying to make it so a web page will only list the last time a client conducted a transaction. My query looks something like this (the big ??? shows where I know something is missing but can't figure out what).SELECT dttm_completed, comment, client, country, region, l_name, f_name FROM transactions, clients, countries, regions, contacts WHERE transactions.client_id=result_of_some_search_form AND transactions.contact_id=contacts.contact_id AND transactions.client_id=clients.client_idAND clients.country_id=countries.country_id AND regions.region_id=countries.region_id AND dttm_completed=(SELECT max(dttm_completed) FROM recon WHERE ???? ) GROUP BY ???????What is happening now is that clearly the Max(dttm_completed) is the max for the whole table, not just for the ones with that client_id. I tried putting in AS clauses in certain places, but I keep getting errors. All the help pages I've found show me how to deal with such a query using only one table, or only querying one field (I can do SELECT max(dttm_completed) FROM transactions), but that is not enough right now.Any help is greatly appreciated!Z. |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-10-20 : 11:26:11
|
| Will this work? I'm not sure of the structure of your recon table...SELECT dttm_completed, comment, client, country, region, l_name, f_name FROM transactionsINNER JOIN clients ON transactions.client_id=clients.client_id INNER JOIN contacts ON contacts transactions.contact_id=contacts.contact_idINNER JOIN countries ON clients.country_id=countries.country_idINNER JOIN regions ON regions.region_id=countries.region_idWHERE transactions.client_id=result_of_some_search_form AND dttm_completed=(SELECT max(dttm_completed) FROM recon WHERE client_id = result_of_some_search_form)ThanksKarunakaran |
 |
|
|
|
|
|
|
|
MAX Date