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 |
|
Thor376
Starting Member
4 Posts |
 Posted - 2005-11-16 : 09:25:17
|
| I want to join three tables. The first is Adress which contains addresses with an ID field and some other text fields. The second table is Company with an ID field and a FID field which is reference to an address ID. There are many addresses, but fewer companies. Some addresses are not associated with any company.The third table is Details with phone numbers for each company. In the Details table there is an ID field and a FID field which is a reference to a Company ID. Company records may be associated with zero or more records from Details.I want to create an SQL statement that combines the three tables and lists every address, whether it is associated with a company record or not. In the same query I want records from the Details table, if any such record exists for a given company. I think that I should use LEFT OUTER JOIN to join the three tables, but I don't know how. How do I do that in MS Acess? |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-11-16 : 09:40:12
|
| this should get you cookin'select * from Address a left outer join Company c on c.fid = a.addressid i would probably reconsider the fid to be addressid :)________________________________________________SQL = Serious Quaffing of Liquor |
 |
|
|
Thor376
Starting Member
4 Posts |
Posted - 2005-11-16 : 11:49:21
|
| I have tried with this:SELECT * FROM Address A LEFT OUTER JOIN Companies C ON C.FID=A.ID LEFT OUTER JOIN Details D ON D.FID=C.ID WHERE A.Address LIKE "H%" ORDER BY A.City;But Access says Syntax error (missing operator) in query expression. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-11-16 : 11:58:14
|
| Build up the query bit-by-bit and try to see what is tilting it over the hill and thus to be invalid. Start with the most basic statement and then add in the extra clauses/joins 1-by-1. |
|
|
|
Thor376
Starting Member
4 Posts |
Posted - 2005-11-16 : 13:07:18
|
| Ok, bit by bit. This query shows the result that I expected:SELECT * FROM Address ALEFT OUTER JOIN Companies C ON C.FID=A.ID WHERE A.Address LIKE "H%" ORDER BY City;Then I added one line:SELECT * FROM Address ALEFT OUTER JOIN Companies C ON V.FID=A.IDLEFT OUTER JOIN Details D ON D.FID=C.IDWHERE A.Address LIKE "H%" ORDER BY City;Access says Syntax error (missing operator) in query expression. |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-11-16 : 13:40:51
|
| WellAccess sucks. Unfortunately I have had to work on it in the past. The easiest way:Do this graphically in the query builder and setup your relationships properly, then check out the sql. Probably a parenthesis missing somewhere or [] around a field name.________________________________________________SQL = Serious Quaffing of Liquor |
|
|
|
Thor376
Starting Member
4 Posts |
Posted - 2005-11-17 : 05:15:00
|
| Thanks. I finally made it with the graphical view. It works.Here at work, our manager considers sending two of us programmers to a course in Oracle databases. We may convert our database to Oracle some day. That will be an improvement. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-11-17 : 05:28:53
|
| 2 things...a) ".... We may convert our database to Oracle some day. That will be an improvement."..it might be, but then you won't be able to get any help from SQLTeam.com...we're focussed on the MS version of SQL only. Upgrading to SQL server should be a consideration!b) "SELECT * FROM Address A LEFT OUTER JOIN Companies C ON C.FID=A.ID WHERE A.Address LIKE "H%" ORDER BY City;"You started to adopt a good practice of using the alias for referencing tables + columns...except in the 'order by statement'..."order by a/b.City" would make it 100% clear where this data was coming from. |
|
|
|
|
|
|
Joining three tables