Using UNION to pull data into grid

问题:

I have a data grid on my site where i'm pulling Company Information from 2 tables.

I'm creating a form to sort these results based on 3 filters (Category,State,City)

This is my query to load data into the grid initially. I want it populated will ALL my table data UNTIL a user decides to filter it, which is why i'm using UNION as without it my query is waiting for me to pass a query-string value to complete it's WHERE statement.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID

UNION

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = '[querystring:filter-Category]' OR     '[querystring:filter-Category]' is NULL)

I'm not sure if this is the proper procedure to do this.

I'm having difficulties understanding the NULL parameter of this query. On page-load there is no query-string.

Only once a user presses the "SORT" button does a query-string get added into the URL. A user can choose to sort by 1 or all 3 of the filters but in the case they only sort by 1 of the options. The query-string will pass an ALL for the other two that were left alone.

IE: filter-Category/Barber%20Shop/filter-City/All/filter-State/All Here a user only choose 'Barber Shop' from 1 drop-down and left the City & State drop downs as their default selections.

My query that is "listening" for the query-string parameters is not working correctly on my application. However when using the following in SQL Management studio it does pull my records properly.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = 'Barber Shop' OR '[querystring:filter-Category]' is      NULL)

This would be my data-grid query with all 3 "Listening" WHERE statements included.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID

UNION

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = '[querystring:filter-Category]' OR     '[querystring:filter-Category]' is NULL)
AND (city = '[querystring:filter-City]' OR '[querystring:filter-City]' IS     NULL)
AND (state ='[querystring:filter-State]' OR '[querystring:filter-State]' IS     NULL)

Any help, input, examples, insight highly appreciated!

Examples Online & SQL Studio

回答1:

Union isn't going to get you what you want. It's purpose it to join the results of 2 or more queries together keeping the distinct values. In your case the first half of the union captures all of the data and the second half captures the filtered data. Since first half captures all, all gets returned.

You appear to be doing this because you don't have filters for the first run but you want filters on the second run.

Don't add the where clause if the query strings are not present. IF they are present, add where and the appropriate clauses.

You mention sort but your query lacks an order by clause. You'll want to add one if you want the data to be sorted.

1st load, no where clause or all filters are set to 'ALL'

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
order by CategoryName, state, city

Subsequent loads, with where clauses.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = '[querystring:filter-Category]' OR     '[querystring:filter-Category]' is NULL)
AND (city = '[querystring:filter-City]' OR '[querystring:filter-City]' IS     NULL)
AND (state ='[querystring:filter-State]' OR '[querystring:filter-State]' IS     NULL)
order by CategoryName, state, city

Edit: You can't have 2 queries because the plugin only allows 1.

I assume by "listening" you mean the querystring:filter-Category will be replaced by whatever is in the category query string. Since it's replaced in your query you can't compare to null. since no filter is 'ALL' you'll need to take that into account. Try this:

SELECT * 
FROM BND_listing 
left join BND_ListingCategories
    on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE   
    (
        '[querystring:filter-Category]' = 'ALL' -- for when the queryString is set to ALL
        or '[querystring:filter-Category]' = '' -- for when the queryString isn't available like first load. this replaces the is null comparison. you can't compare a string literal to null as they are wrapped in quotes
        or CategoryName = '[querystring:filter-Category]' -- this is the comparison to your column
    )
    AND (
         '[querystring:filter-City]' = 'ALL'
        or '[querystring:filter-City]' = ''
        or city = '[querystring:filter-City]' 
    )
    AND (
        '[querystring:filter-State]'  = 'ALL'
        or '[querystring:filter-State]' = ''
        or state = '[querystring:filter-State]' 
    )
order by CategoryName, state, city

回答2:

Thank you for all the help & examples. I was able to get my queries sorted in fact I had an issue with the grid that needed to be addressed 1st.

select * FROM BND_listing right join BND_ListingCategories
on BND_Listing.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')

I simply have a page redirect that always passes atleast a ALL value for all my query-string filters which got my grid populated.