Skip to main content
Database Administrators

Return to Question

edited tags; formatting
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

NULL in the following table essentially means that if it is not an exact match with another row thanthen choose this one. A wildcard if you will. I can have 100% control over this table so if this is a bad design for what I am trying to accomplish please advise.

tblRateCategory:###tblRateCategory

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

with this table tblJob###tblJob

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

Desired###Desired results

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

I have tried this which gave duplicate job numbers and incorrect RateCategoryIDRateCategoryID if CategoryCategory and/or CustomerCustomer has match. So I know I need to somehow look for match first and then settle for NULL or Any if no match found.

Results from above query.:

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50

NULL in the following table essentially means that if it is not an exact match with another row than choose this one. A wildcard if you will. I can have 100% control over this table so if this is a bad design for what I am trying to accomplish please advise.

tblRateCategory:

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

with this table tblJob

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

Desired results

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

I have tried this which gave duplicate job numbers and incorrect RateCategoryID if Category and/or Customer has match. So I know I need to somehow look for match first and then settle for NULL or Any if no match found.

Results from above query.

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50

NULL in the following table essentially means that if it is not an exact match with another row then choose this one. A wildcard if you will. I have 100% control over this table so if this is a bad design for what I am trying to accomplish please advise.

###tblRateCategory

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

###tblJob

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

###Desired results

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

I have tried this which gave duplicate job numbers and incorrect RateCategoryID if Category and/or Customer has match. So I know I need to somehow look for match first and then settle for NULL or Any if no match found.

Results from above query:

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50
Source Link
Matt Cullinan
Matt Cullinan
  • 45
  • 1
  • 4

Relating 2 tables with possible wildcards?

I am having trouble getting the desired results when trying to relate the following tables.

NULL in the following table essentially means that if it is not an exact match with another row than choose this one. A wildcard if you will. I can have 100% control over this table so if this is a bad design for what I am trying to accomplish please advise.

tblRateCategory:

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

with this table tblJob

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

Desired results

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

I have tried this which gave duplicate job numbers and incorrect RateCategoryID if Category and/or Customer has match. So I know I need to somehow look for match first and then settle for NULL or Any if no match found.

Select JobNumber, RateCategoryID 
From tblJob J
Inner Join tblRateCategory RC
ON isnull(RC.Category,J.Category)=J.Category and
RC.Managed=J.Managed and isnull(RC.Customer,J.Customer)

Results from above query.

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50