February 5, 2011 at 11:28 am
I have rather large and complex query with over 15 fields and multiple tables that i need to return data from, but the data needs to been filtered based on data in another table. I cannot use procedures for this, only a VIEW.
The exclusions table contains 3 columns, SALESORDER, PRODUCT, PRODUCTTYPE.
So anything within the exclusions table should NOT be in the results of the main query.
My problem is how to do the join based on multiple fields. I know i can simply do the classic LEFT JOIN and IS NULL. But can only get that working for a single column.
Data in the exclusions table would be something like:
SALESORDER............PRODUCT............PRODUCTTYPE
11911.....................NULL..................NULL------------------ I want to exclude all rows with this sales order.
19124.....................TAPE_DECK.........AUDIO----------------I want to exclude this salesorder / product / producttype only.
NULL.......................NULL.................VIDEO----------------I want to exclude this producttype.
Can anyone provide a solution for this?
Cheers.
February 5, 2011 at 4:30 pm
AND 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 5, 2011 at 6:20 am
Hi
The tables first need to be joined. The rows to be excluded can be filtered using a where clause or by specifying the criteria in the join itself.
from table2
join table1
on (table1.id=table2.id and table2.sumcolumn != somevalue)
The second method is a bit harder to maintain and read but does give the query a performance boost, especially if there is an index on the filtered column.
Hope this helps you...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply