I am working on an app for fight match. and the app should match based on difference in weight for example the weight should have the difference in range with 35 or equal in weight and no recurring in match. This is my table
| cock_id | c_hdr_id | entry_name | owner_name | c_weight | c_date |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 1 | HL | PLAYER1 | 1750.00 | 2023-07-18 |
| 2 | 1 | HL | PLAYER1 | 2750.00 | 2023-07-18 |
| 3 | 1 | HL | PLAYER1 | 1980.00 | 2023-07-18 |
| 4 | 1 | HL | PLAYER1 | 2035.00 | 2023-07-18 |
| 5 | 1 | HL | PLAYER1 | 1850.00 | 2023-07-18 |
| 6 | 1 | HL | PLAYER1 | 3100.00 | 2023-07-18 |
| 7 | 1 | HL | PLAYER1 | 2880.00 | 2023-07-18 |
| 8 | 1 | HL | PLAYER1 | 1950.00 | 2023-07-18 |
| 9 | 1 | HL | PLAYER1 | 1970.00 | 2023-07-18 |
| 10 | 1 | HL | PLAYER1 | 2850.00 | 2023-07-18 |
| 11 | 2 | HB | PLAYER2 | 1780.00 | 2023-07-18 |
| 12 | 2 | HB | PLAYER2 | 2755.00 | 2023-07-18 |
| 13 | 2 | HB | PLAYER2 | 1990.00 | 2023-07-18 |
| 14 | 2 | HB | PLAYER2 | 2535.00 | 2023-07-18 |
| 15 | 2 | HB | PLAYER2 | 1970.00 | 2023-07-18 |
| 16 | 2 | HB | PLAYER2 | 3235.00 | 2023-07-18 |
| 17 | 2 | HB | PLAYER2 | 2875.00 | 2023-07-18 |
| 18 | 2 | HB | PLAYER2 | 1925.00 | 2023-07-18 |
| 19 | 2 | HB | PLAYER2 | 1912.00 | 2023-07-18 |
| 20 | 2 | HB | PLAYER2 | 2862.00 | 2023-07-18 |
Expected output:
| entry_name1 | wing1 | weight1 | entry_name2 | wing2 | weight2 |
|---|---|---|---|---|---|
| PLAYER1 | A | 1750.00 | PLAYER2 | A | 1780.00 |
| PLAYER1 | A | 2750.00 | PLAYER2 | A | 2755.00 |
| PLAYER1 | A | 1980.00 | PLAYER2 | A | 1990.00 |
| PLAYER1 | A | 2880.00 | PLAYER2 | A | 2875.00 |
| PLAYER1 | A | 1950.00 | PLAYER2 | A | 1925.00 |
| PLAYER1 | A | 2850.00 | PLAYER2 | A | 2862.00 |
and this is my query:
SELECT A.entry_name AS entry_name1, A.owner_name AS owner_name1, A.c_weight AS weight1,
B.entry_name AS entry_name2, B.owner_name AS owner_name2, B.c_weight AS weight2
FROM cock_entry A
INNER JOIN cock_entry B
ON A.entry_name > B.entry_name AND ABS(A.c_weight - B.c_weight) <= 35
WHERE NOT EXISTS (
SELECT 1
FROM cock_entry C
WHERE A.entry_name = C.entry_name AND A.c_weight = C.c_weight
AND B.entry_name = C.owner_name AND B.c_weight = C.c_weight
)
1条答案
按热度按时间gopyfrb31#
Your query is almost correct. However, there are a few adjustments needed to achieve the desired output. Here's an updated version of your query:
Here are the changes made to your original query:
In the ON clause of the JOIN, change A.entry_name > B.entry_name to A.entry_name < B.entry_name. This ensures that you avoid duplicate pairs and prevent matching the same entries in reverse order.
Modify the WHERE clause inside the NOT EXISTS subquery. Instead of using AND between the conditions, use OR. This ensures that you exclude rows where either entry_name1/weight1 or entry_name2/weight2 matches the same values in another row.
With these adjustments, the query should produce the expected output, matching entries based on a weight difference of 35 or less, while avoiding recurring matches.