Implementing a Junction Table

Question

Current Build:

Table (BND_Listing) as A contains store location information etc...
Table (BND_ListingCategories) as B contains Categories to classify A

I'm using query-strings to create a "Filter" function on my website that sorts A based on a user selection. I'm using the following select statement to populate a grid on my site:

SELECT *
FROM   bnd_listing_testing
       RIGHT JOIN bnd_listingcategories
               ON bnd_listing_testing.catid = bnd_listingcategories.catid
WHERE  ( categoryname = '[querystring:filter-Category]'
          OR '[querystring:filter-Category]' = 'All' )
       AND ( city = '[querystring:filter-City]'
              OR '[querystring:filter-City]' = 'All' )
       AND ( region = '[querystring:filter-State]'
              OR '[querystring:filter-State]' = 'All' )
       AND ( country = '[querystring:filter-Country]'
              OR '[querystring:filter-Country]' = 'All' )
       AND ISNULL(company, '') <> ''
ORDER  BY company ASC

I have a form on the site that based on three select boxes will pass City/State/Country values into a query-string that the above query is "Listening" for to populate my grid.

Everything works as it stands now, however I'm needing to attribute multiple categories to any one record in table A that currently is just a column with one value.

I've read up on Junction Tables/Many to Many relationships and think this is the route I need to go. I understand the basic concept but I am having trouble updating my SELECT statement that is populating my grid to take into account the Junction table I've named Table (BND_Junction) as C.

I'd really appreciate any examples one of you might have as to how I can manipulate my select statement to involve table C but still function the same.

If any points need clarification please let me know.

UPDATE

I've tried out the following query that works but for simplicity removing the querystring filters section.

SELECT DISTINCT *
FROM   bnd_listingjunction
       JOIN bnd_listing
         ON bnd_listing.catid = bnd_listingjunction.catid
       JOIN bnd_listingcategories
         ON bnd_listingcategories.catid = bnd_listingjunction.catid

Do you realize `'[querystring:filter-Category]' = 'All'` always evaluates to `FALSE`? In boolean logic, `X OR FALSE` is equivalent to `X`. I fail to see why you would add that comparison to your where clause... Can you enlighten us? Do you replace those expressions before executing the query? — TT., Oct 29 '16 at 07:16
[querystring:filter-category]' is a token that is replaced by my web app depending on what a user selects as a category — UserSN, Oct 29 '16 at 14:47

score 0 · Accepted Answer · edited Oct 29 '16 at 21:49

SELECT *
FROM   bnd_listingjunction
       JOIN bnd_listing_testing
         ON bnd_listing_testing.lid = bnd_listingjunction.junc_lid
       JOIN bnd_listingcategories
         ON bnd_listingcategories.catid = bnd_listingjunction.junc_catid
WHERE  ( categoryname = '[querystring:filter-Category]'
          OR '[querystring:filter-Category]' = 'All' )
       AND ( city = '[querystring:filter-City]'
              OR '[querystring:filter-City]' = 'All' )
       AND ( region = '[querystring:filter-State]'
              OR '[querystring:filter-State]' = 'All' )
       AND ( country = '[querystring:filter-Country]'
              OR '[querystring:filter-Country]' = 'All' )
       AND ISNULL(company, '') <> ''
ORDER  BY company ASC

Implementing a Junction Table

1 Answers1