Simple JOIN in Access 2002
-
I have three tables: - Article - Album - linkArticleAlbum I want get a list of all the albums and also have some way of determining whether it is linked to the currently selected article. What I WANT to do is something like
SELECT
linkArticleAlbum.Article,
Album.ID,
Album.Title
FROM Album LEFT JOIN linkArticleAlbum ON linkArticleAlbum.Album = Album.ID
WHERE linkArticleAlbum.Article = 7 OR linkArticleAlbum.Article IS NULLHere's what my sample data looks like
Album
| ID |
1
2
3Article
| ID |
7
8
9linkArticleAlbum
| Article | Album
7 1
8 1
9 2As you can see, I won't get a record with Album 2 since my WHERE clause eliminates it. I'm stuck. Advise anything, including a redesign of my DB. I've apparently done something stupid and my murky brain can't figure it out right now. ········ to die is gain ········
-
I have three tables: - Article - Album - linkArticleAlbum I want get a list of all the albums and also have some way of determining whether it is linked to the currently selected article. What I WANT to do is something like
SELECT
linkArticleAlbum.Article,
Album.ID,
Album.Title
FROM Album LEFT JOIN linkArticleAlbum ON linkArticleAlbum.Album = Album.ID
WHERE linkArticleAlbum.Article = 7 OR linkArticleAlbum.Article IS NULLHere's what my sample data looks like
Album
| ID |
1
2
3Article
| ID |
7
8
9linkArticleAlbum
| Article | Album
7 1
8 1
9 2As you can see, I won't get a record with Album 2 since my WHERE clause eliminates it. I'm stuck. Advise anything, including a redesign of my DB. I've apparently done something stupid and my murky brain can't figure it out right now. ········ to die is gain ········
Well, you haven't done anything clearly wrong in your database design, but your query needs some help. If you include a WHERE clause, only the rows that satisfy the condition will be listed, so LEFT JOINing won't help. If it's really important to have the results indicate whether the album is linked to the selected article, this would probably do the trick:
SELECT al.ID, al.Title, l.Article, IIf([l].[Article] Is Not Null AND l.Article = [article ID],"Linked","Unlinked") AS IsLinked FROM Album AS al LEFT JOIN linkArticleAlbum AS l ON al.ID = l.Album;Given your setup, where a single Album may be associated with many Articles, you should be aware that you may get multiple results for a single Album (from your example, Album 1 would appear twice in the results). If you don't care about indicating in the results whether the Album is linked (i.e. you want to handle that in code) then the query is even shorter:SELECT al.ID, al.Title, l.Article FROM Album AS al LEFT JOIN linkArticleAlbum AS l ON al.ID = l.Album;Hope this helps.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
Well, you haven't done anything clearly wrong in your database design, but your query needs some help. If you include a WHERE clause, only the rows that satisfy the condition will be listed, so LEFT JOINing won't help. If it's really important to have the results indicate whether the album is linked to the selected article, this would probably do the trick:
SELECT al.ID, al.Title, l.Article, IIf([l].[Article] Is Not Null AND l.Article = [article ID],"Linked","Unlinked") AS IsLinked FROM Album AS al LEFT JOIN linkArticleAlbum AS l ON al.ID = l.Album;Given your setup, where a single Album may be associated with many Articles, you should be aware that you may get multiple results for a single Album (from your example, Album 1 would appear twice in the results). If you don't care about indicating in the results whether the Album is linked (i.e. you want to handle that in code) then the query is even shorter:SELECT al.ID, al.Title, l.Article FROM Album AS al LEFT JOIN linkArticleAlbum AS l ON al.ID = l.Album;Hope this helps.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
Thanks for the help. The example does help to some degree, but in one iteration of my attempts I achieved essentially the same thing. To clarify, I would prefer to have each Album appear only once. With the current queries, each album shows up at least once, but will appear as many times as it is referenced in the linkArticleAlbum table. Can I eliminate the duplicates while ensuring that I can test for l.Article = [article ID]???