Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Joining 2 tables: SQL

Status
Not open for further replies.

zimmer9

Programmer
Jan 12, 2005
66
US
I want to filter out records from a table which I will call Table A which has a date field named "Date Lost". The goal is to extract records from Table A with a Date Lost value that is less than a date value retrieved from Table B.
Table A has the following fields:
1) State
2) Property Type (with a value of either a)Cash b)MF or c) Stocks)
3) Date Lost
Table B has the following fields:
1) State plus Date fields for 2)Cash 3)MF 4)Stocks
The idea is to match the Property Type value from Table A (either Cash, MF or Stocks) against Table B's matching Date field and compare the date value record from Table B against the Date Lost value from Table A. Can this be done with just SQL ?
 
Something like this ?
SELECT A.*
FROM TableA A INNER JOIN TableB B ON A.State = B.State
WHERE ((A.PropertyType='Cash' And A.DateLost<B.DateCash)
OR (A.PropertyType='MF' And A.DateLost<B.DateMF)
OR (A.PropertyType='Stocks' And A.DateLost<B.DateStocks));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top