Can't get SQL query to do what I want?

Question

Hey guys,

I am a complete stranger to SQL and hope you can help me solve this problem. I am supposed to get two tables in a specific manner. There is a table that is called qYears, another one is Countries.
qYears simply contains a list of years, thus looks like this

Year
------
2002
2003
2004
[...]
2011

Countries contains information about when a variable was researched in which country. It also contains a column named Years, however the range on that one is only 2002 - 2008. What I have to do now, is get the both together, so that in the query table it shows the years 2002 thru 2011, regardless of whether there is data for that year in the Countries table.
I have figured out, that I have to do something with outer joins here, my attempt looks like this:

SELECT qYear.Years, Countries.Geo
FROM qYear RIGHT JOIN Countries ON qYear.Years = Countries.Year
WHERE (((Countries.Geo)="AT" Or (Countries.Geo)="BE" Or (Countries.Geo)="DE" Or (Countries.Geo)="ES" Or (Countries.Geo)="FI" Or (Countries.Geo)="FR" Or (Countries.Geo)="IT" Or (Countries.Geo)="SE" Or (Countries.Geo)="UK"));

Sorry for the weird Syntax, I have to use MS Access, and that always screws with it.
So yeah, that is what I am trying to do. I have tried changing it from RIGHT to LEFT and back and forth, but the results do not differ whatsoever. The query only shows the years 2002 through 2008 with the corresponding data.
Thanks for helping me!

Answer 1

Only way to do this is either thru outer joins or unions...an outer join is the far-superior method:



SELECT q.Years, c.Geo FROM qYear q

LEFT OUTER JOIN

Countries c ON q.Years = c.Year

WHERE c.Geo IN ("AT", "BE", "DE", "ES", "FI", "FR", "IT", "SE", "UK")



BTW, if using Access, try switching to SQL View rather than just doing it all with wizards.

Answer 2

I even have this on my gadget on the place of work and to be trustworthy I in no way use it because of fact it would not help me very nicely while examining a question. With a question this massive and that many joins that's a controversy working it in sq.. i could in all hazard recomend breaking this up into sub queries interior the sq. fact and you're able to be able to get extra useful outcomes. Are you utilising any Outer Joins or are all of those inner Joins?

Answer 3

Change 'RIGHT JOIN' to 'LEFT OUTER JOIN' that'll fix the problem.



And in answer to the other question, yes you could do it another way, using a sub-query. But the Outer join is the way to go!

Answer 4

FROM qYear LEFT OUTER JOIN Countries ON qYear.Years = Countries.Year AND COUNTRIES.Geo in ('AT','BE','DE','ES'...)