OK - I know, wrong forum, but I don’t belong to any SQL forums and this is driving me nuts. In Microsft Access, 3 tables Table1 Fields: Cusip, Date, Return Table2 Fields: CUSIP, TaxID Table3 Fields: TaxID, Volume, Date Alas, Table3 is incomplete and I want to join them up including every record from Table1. In light of the following BS from MS’s website "A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins. ". Assume that I cannot add fields or change the tables and I have to use Access, how the heck do I do this? Thanks.
SELECT T1.Cusip, T1.Date, T1.Return, T2.TaxID, T3.Volume FROM Table1 T1, Table2 T2 LEFT JOIN Table3 T3 ON (T3.TaxID=T2.TaxID) WHERE T1.Cusip = T2.Cusip Something like that
SELECT T1.Cusip, T1.Date, T1.Return, T2.TaxID, T3.Volume FROM (Table1 T1 LEFT JOIN Table2 T2 ON (T1.Cusip = T2.Cusip)) LEFT JOIN Table3 T3 ON (T3.TaxID=T2.TaxID) That if Set(T1.Cusip) - Set(T2.Cusip) > 0
If you are using Access, easiest nasty hack is to do a 2 step query (and use query builder!). So do a straight inner join T2T3, and call this Q4. Then do a query (left or right join, i always forget) joining T1 to Q4. Using query builder, you can change the type of join by right clicking it. And then robert is your mother’s brother.
^— you sir, must be kidding me
JoeyDVivre Wrote: ------------------------------------------------------- > Assume that I cannot > add fields or change the tables and I have to use > Access, how the heck do I do this? The way I said. Set() doesn’t work in Access. You also need an AS statement to use abbreviations for tables in Access. My way might not be pretty or efficient, but qualifies as a nasty hack that gets the job done. How’d you get on with your wife’s mac joey?
FYI, Set is not an operator, i just wrote it out to demonstrate the difference between LEFT JOIN and JOIN
SELECT Table1.Cusip, Table1.Date, Table1.Return, Table2.TaxID, Table3.Volume, Table3.Date FROM (Table1 LEFT JOIN Table2 ON Table1.Cusip = Table2.Cusip) LEFT JOIN Table3 ON Table2.TaxID = Table3.TaxID;
ive been looking for a good sql discussion forum where you can ask sql query questions. do you guys know of any? thx.
Joey: For future reference, a good book for SQL related studd is “SQL Queries For Mere Mortals”. It’s easily readable, and unlike most SQL references, focuses only on the queries material. I’m not the alpha nerd when it comes to programming, but i write a lot of SAS code, and this got me up to speed on SQL pretty quickly.
MFE Wrote: ------------------------------------------------------- > SELECT Table1.Cusip, Table1.Date, Table1.Return, > Table2.TaxID, Table3.Volume, Table3.Date > FROM (Table1 LEFT JOIN Table2 ON Table1.Cusip = > Table2.Cusip) LEFT JOIN Table3 ON Table2.TaxID = > Table3.TaxID; Thats what i said…
Nah. You had a set() function in there *hides*
Try This: SELECT Table1.Cusip, Table1.Date, Table1.Return FROM Table3 LEFT JOIN (Table1 INNER JOIN Table2 ON Table1.Cusip = Table2.CUSIP) ON Table3.TaxID = Table2.TaxID; Assumption: Table1 and Table2 are inner joined by CUSIP Table 2 and Table 3 are left outer joined by Taxid (all from table3 and only matched in table2)
If these were tables in Oracle - you could use a LEFT OUTER JOIN clause to join the tables you wanted… Or since from access, you could get these into Excel spreadsheet tabs – you could use the VLOOKUP function from the table 3 tab and then automate the entire process with VBA macros. CP
Thanks for the responses guys. a) I don’t think table aliasing works in Access in a way that helps solve this problem. b) I should have been clearer that I need this joined on both dates and identifiers. The ID information is complete but some dates are missing hence the need for a left (or right) join. c) I can write a program to do this in every language ever invented by mankind but this particular application calls for a SQL query in Access because other people have to modify it at run time (this is software for this stupid book that’s taking millions of times longer than I thought). d) I though about cpk’s suggestion but bundling oracle with a book or requiring people to buy seems like a bad solution. Kinda raises the price prohibitively. e) Something like SMEKA’s query ought to work but try that in Access. The f-ing program says “Join not supported”. It means something like “Buy SQL Server”. I finally got this done using a union and sub query but it is friggin ugly. It’s like Inner join that leaves out days on which volume is missing union Inner join that includes dates where volume date not in (Seubquery) gross…
Just so you know, there are free versions of Oracle and SQL server - but not of Access. If price point is an issue, then SQL Server Express + Visual Studio Express or Oracle 10g express will be cheaper for the end user.
Hmmm… Microsoft Access drivers, I think, are redistributable. I have somehow missed those other two however. Hmmm… Edit: Oops. - Thanks!