CODEWITHSUNDEEP
sqlms-access
Seann
Seann

Reputation: 91

Microsoft Access joining 4 tables to display specific values from 3 tables but all values from the main table

I have created a database for users to "follow" TV Shows, I need to create a form to display each show and all the relevant information to that specific show.

The four tables I have are as follows:
Shows (Main Table),
Networks,
ShowGenres (Links multiple genre's to one show),
Genres. The relationships and all fields are shown in the image below.

Relationships:

Currently I have a page which displays the following information: showID, showName, showAired, networkName, showStatus, showRuntime, showSeasons, showEpisodes, showOverview.
Ideally i'd like to have a List box to display an array of the genre's associated with the specific show. I have tried for quite a while to come up with a query to do this, the closest I managed to get showed the relevant information but added duplicate pages.
Here's my latest attempt:

SELECT * FROM Shows A
INNER JOIN Networks B ON B.networkID = A.networkID
INNER JOIN ShowGenres C ON C.showID = A.showID
INNER JOIN Genres D ON D.genreID = C.genreID;

Any help would be appreciated, thanks in advance.

Upvotes: 0

Views: 77

Answers (2)

Cahaba Data
Cahaba Data

Reputation: 622

One has to appreciate the presentation level logic that can be used in conjunction with the data logic. Very generically - in the 1:Many - - your query of fields that include both fields will repeat the 1 table values with each of the Many records. That can not be altered via any query design as it is inherent in the data logic.

But at the presentation level you can control the display. Using a report - the data source can be the query - but report properties offer grouping whereby you can put the 1 field value as the group header - thus displaying just once; and then below it list all the many records.

It is not a list box per se though depending on how creative one gets with reports/sub reports one could potentially make that style. But in the end you must work this at the presentation level.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

One method is to use exists:

select g.*
from genres as g
where exists (select 1
              from showgenres as sg inner join
                   shows as s
                   on sg.showID = s.showID
              where sg.genreID = g.genreID and
                    s.showName = ?
             );

Upvotes: 0

Related Questions