I am using Access 2007 to create an SQL query to join two tables. I was able to do that but then I don't have the rows where the columns from the second table are NULL; I just have the rows where there is information in the second table that matches. I tried to do a LEFT JOIN but Access didn't like this. Instead I am trying to create a better 'join/on' addition to my query (seen below) but now I am getting a "Syntax error in FROM clause". Do you have any ideas what I am doing wrong?
SELECT *
FROM dbo_table1 AS t1
JOIN dbo_table2 AS t2
ON (
(t1.id = t2.id) // where the two ids match so the info in table 2 match with table 1
OR
((SELECT COUNT(*) FROM 开发者_Python百科dbo_table2 AS t3 WHERE t1.id = t3.id)=0) // there is no match but I want the row with NULL for the values from the second table
);
If you want all rows from dbo_table1, whether or not there is a matching row in dbo_table2, use a LEFT JOIN. Access should accept this one:
SELECT *
FROM
dbo_table1 AS t1
LEFT JOIN dbo_table2 AS t2
ON t1.id = t2.id;
You can have an outer / cross join with a WHERE statement (a.id=b.id or b.id is null).
Or a UNION, the first being all a.id = b.id, the second where b.id is null.
(Depending on your exact requirements)
精彩评论