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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-29 : 09:25:38
|
| conasatatu writes "hi, please help 2 TABLES, accounts, account_updates TABLE 1: account_no.(primary key), date_created, account_type, customer_name, created_by TABLE 2: update_ID (primary key), date_altered, status_changed_to The query is for administrators to see all the account details and see the most recent status of the account, ie is it paid, in arrears etc. The query will be in an asp page where the administrator decides certain criteria. I want the results to include account_no., date_created, account_type, most recent status. In order to get the most recent status i would use SELECT status_changed_to FROM account_updates where account_no. = X order by date_altered DESC LIMIT 1; but i also want a select from table 1 SELECT account_no., date_created,account_type, customer_name FROM accounts WHERE account_type = user input value is there any way to do all this in one query????? Thanks in advance." |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-05-29 : 10:31:15
|
| I don't see any relationship between the two tables. Is account_no in the second table? |
 |
|
|
dataphile
Yak Posting Veteran
71 Posts |
Posted - 2002-05-31 : 10:02:34
|
| Since SELECT status_changed_to FROM account_updates where account_no. = X order by date_altered DESC LIMIT 1; works, account_no is in the secont table.You would then do :Select t1.[account_no.], t1.date_created, t1.account_type, t2.status_changed_to from table1 t1 inner join table2 t2 on t1.[account_no.] = t2.[account_no.]where t2.date_altered = (select max(t2a.date_altered) from table2 t2a where t2a.[account_no.] = t2.[account_no.])English :Join table1 and table2 on account_no and only display the resultswhere the table2.date_altered is the max for this account_no. In stead of date_altered you should acctually use update_ID in the subquery if it is ascending since it is the primary key and a better choice for an index. |
|
|
|
|
|
|
|
|
complex join