# 用XOR语句连接子句[英] Join Clause With a XOR Statement

### 本文来自：IT宝库（https://www.itbaoku.cn）

```SELECT t1.COMPANY, t1.MILES,
CASE WHEN t2.MILES IS NULL THEN t3.MILES
ELSE t2.MILES
END AS MILES2,
CASE WHEN t2.MILES = t1.MILES AND t2.MILES != 9999 THEN t2.FLATRATE
ELSE t3.RATEBASIS
END AS RATE
FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2
ON t1.[COMPANY] = t2.[COMPANYCODE] AND (t1.[MILES] = t2.[MILES])
INNER JOIN (
SELECT TOP 1 TRUCKERCODE, MILES, RATEBASIS, FLATRATE FROM TABLE2 WHERE MILES = 9999
) AS t3
ON t1.[COMPANY] = t3.[COMPANYCODE]
```

```TABLE1                          TABLE2
ID COMPANY MILES ETC            ID COMPANYCODE MILES RATE
1  ILLINI  50                   1  ILLINI      50    3.2
2  ILLINI  110                  2  ILLINI      110   5.2
3  ILLINI  150                  3  ILLINI      150   2.4
4  ILLINI  200                  4  ILLINI      200   1.9
5  ILLINI  250                  5  ILLINI      9999  1.5
6  ILLINI  300
7  ILLINI  350
8  ILLINI  400
9  ILLINI  450
10 ILLINI  500

Desired Output
COMPANY MILES MILES2 RATE
ILLINI  50    50    3.2
ILLINI  110   110   5.2
ILLINI  150   150   2.4
ILLINI  200   200   1.9
ILLINI  250   9999  1.5
ILLINI  300   9999  1.5
ILLINI  350   9999  1.5
ILLINI  400   9999  1.5
ILLINI  450   9999  1.5
ILLINI  500   9999  1.5
```

## 推荐答案

```SELECT t1.COMPANY, t1.MILES,
CASE WHEN t2.MILES IS NULL THEN t3.MILES
ELSE t2.MILES
END AS MILES2,
CASE WHEN t2.MILES IS NULL THEN t3.RATE
ELSE t2.RATE
END AS RATE
FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2
ON t1.[COMPANY] = t2.[COMPANYCODE] AND (t1.[MILES] = t2.[MILES])
INNER JOIN (
SELECT TOP 1 COMPANYCODE, MILES, RATE FROM TABLE2 WHERE MILES = 9999
) AS t3
ON t1.[COMPANY] = t3.[COMPANYCODE]
```

.

.

```COMPANY MILES   MILES2  RATE
----------------------------
ILLINI  50      50      3,2
ILLINI  110     110     5,2
ILLINI  150     150     2,4
ILLINI  200     200     1,9
ILLINI  250     9999    1,5
ILLINI  300     9999    1,5
ILLINI  350     9999    1,5
ILLINI  400     9999    1,5
ILLINI  450     9999    1,5
ILLINI  500     9999    1,5
```

## 其他推荐答案

```SELECT
T1.*,
coalesce( T2.miles, T3.miles ) as Miles2,
coalesce( T2.rate, T3.rate ) as MileageRate
FROM
TABLE1 T1
LEFT JOIN TABLE2 T2
on T1.Company = T2.CompanyCode
AND T1.Miles = T2.Miles
LEFT JOIN TABLE2 T3
on T1.Company = T3.CompanyCode
AND T3.Miles = 9999
```

### 问题描述

I am doing a join and I can't seem to make this XOR to properly work.

```SELECT t1.COMPANY, t1.MILES,
CASE WHEN t2.MILES IS NULL THEN t3.MILES
ELSE t2.MILES
END AS MILES2,
CASE WHEN t2.MILES = t1.MILES AND t2.MILES != 9999 THEN t2.FLATRATE
ELSE t3.RATEBASIS
END AS RATE
FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2
ON t1.[COMPANY] = t2.[COMPANYCODE] AND (t1.[MILES] = t2.[MILES])
INNER JOIN (
SELECT TOP 1 TRUCKERCODE, MILES, RATEBASIS, FLATRATE FROM TABLE2 WHERE MILES = 9999
) AS t3
ON t1.[COMPANY] = t3.[COMPANYCODE]
```

I need the ON clause to join if the miles are the same then get the given fields that match otherwise the default data I need to get out of the second table is where the miles is equal to 9999. Right now with that ON clause I get many extra rows where the MILES equals lets say 45, it gets the data from TABLE2 where miles equals 45 and all the data where miles equals 9999. I need it to do one or the other but not both. This is what my tables would look like

```TABLE1                          TABLE2
ID COMPANY MILES ETC            ID COMPANYCODE MILES RATE
1  ILLINI  50                   1  ILLINI      50    3.2
2  ILLINI  110                  2  ILLINI      110   5.2
3  ILLINI  150                  3  ILLINI      150   2.4
4  ILLINI  200                  4  ILLINI      200   1.9
5  ILLINI  250                  5  ILLINI      9999  1.5
6  ILLINI  300
7  ILLINI  350
8  ILLINI  400
9  ILLINI  450
10 ILLINI  500

Desired Output
COMPANY MILES MILES2 RATE
ILLINI  50    50    3.2
ILLINI  110   110   5.2
ILLINI  150   150   2.4
ILLINI  200   200   1.9
ILLINI  250   9999  1.5
ILLINI  300   9999  1.5
ILLINI  350   9999  1.5
ILLINI  400   9999  1.5
ILLINI  450   9999  1.5
ILLINI  500   9999  1.5
```

## 推荐答案

I think this will give you what you want:

```SELECT t1.COMPANY, t1.MILES,
CASE WHEN t2.MILES IS NULL THEN t3.MILES
ELSE t2.MILES
END AS MILES2,
CASE WHEN t2.MILES IS NULL THEN t3.RATE
ELSE t2.RATE
END AS RATE
FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2
ON t1.[COMPANY] = t2.[COMPANYCODE] AND (t1.[MILES] = t2.[MILES])
INNER JOIN (
SELECT TOP 1 COMPANYCODE, MILES, RATE FROM TABLE2 WHERE MILES = 9999
) AS t3
ON t1.[COMPANY] = t3.[COMPANYCODE]
```

If there is a match in MILES, then output contains both records from TABLE1, TABLE2. Otherwise, output contains record from left table, i.e. TABLE1 and the specific record from TABLE2 with MILES = 9999.

Please note that TOP 1 is used in the last sub-query just in case more than one records with MILES = 9999 exist in TABLE2.

Output:

```COMPANY MILES   MILES2  RATE
----------------------------
ILLINI  50      50      3,2
ILLINI  110     110     5,2
ILLINI  150     150     2,4
ILLINI  200     200     1,9
ILLINI  250     9999    1,5
ILLINI  300     9999    1,5
ILLINI  350     9999    1,5
ILLINI  400     9999    1,5
ILLINI  450     9999    1,5
ILLINI  500     9999    1,5
```

SQL Fiddle Demo here

## 其他推荐答案

I would do based on a double left-join.. once for a match, and if not, then default to the 9999 instance

```SELECT
T1.*,
coalesce( T2.miles, T3.miles ) as Miles2,
coalesce( T2.rate, T3.rate ) as MileageRate
FROM
TABLE1 T1
LEFT JOIN TABLE2 T2
on T1.Company = T2.CompanyCode
AND T1.Miles = T2.Miles
LEFT JOIN TABLE2 T3
on T1.Company = T3.CompanyCode
AND T3.Miles = 9999
```

Confirmed working at this SQL Fiddle link