如何通过LIKE关联CONTROL与PAYMENT表实现条件查询？

Hey there! Let's break down how to get this query working correctly, since you're trying to link your CONTROL and PAYMENT tables with a LIKE match while filtering by serial.

First, Clarify the Match Logic #

You mentioned using %LIKE% between CONTROL.cnumber and PAYMENT.number—we need to be clear on which field contains the other:

• Does cnumber include the value from number? (e.g., cnumber = "INV-1234" and number = "1234")
• Or does number include the value from cnumber? (e.g., number = "PAY-INV-1234" and cnumber = "INV-1234")

Working Query Examples #

Here are the two common scenarios, both filtered by CONTROL.serial:

Scenario 1: CONTROL.cnumber contains PAYMENT.number

SELECT 
    c.serial,
    c.cnumber,
    p.status
FROM CONTROL c
-- Use LEFT JOIN if you want to keep CONTROL records even without a PAYMENT match
-- Use INNER JOIN if you only want records with a matching PAYMENT entry
LEFT JOIN PAYMENT p 
    ON c.cnumber LIKE CONCAT('%', p.number, '%')
WHERE c.serial = 'YOUR_TARGET_SERIAL'; -- Replace with your actual serial value

Scenario 2: PAYMENT.number contains CONTROL.cnumber

SELECT 
    c.serial,
    c.cnumber,
    p.status
FROM CONTROL c
LEFT JOIN PAYMENT p 
    ON p.number LIKE CONCAT('%', c.cnumber, '%')
WHERE c.serial = 'YOUR_TARGET_SERIAL';

Key Fixes & Tips #

• Proper Wildcard Placement: Using CONCAT('%', field, '%') ensures the wildcard is correctly wrapped around the matching value—this avoids syntax errors and ensures partial matches work.
• Choose the Right JOIN:
  • INNER JOIN will only return rows where a matching PAYMENT record exists (which aligns with your initial snippet).
  • LEFT JOIN preserves all CONTROL records that match your serial filter, even if there's no corresponding PAYMENT (status will be NULL in those cases).
• Data Type Checks: If cnumber and number are different data types (e.g., one is a string, the other a number), cast the numeric field to a string first:

  -- Example if PAYMENT.number is numeric
  ON c.cnumber LIKE CONCAT('%', CAST(p.number AS CHAR), '%')
  

• Performance Note: A LIKE with leading % will bypass indexes, so if you're working with large datasets, you might want to explore full-text search options for better speed.

Troubleshooting Common Issues #

• If you're getting no results: Double-check that the partial match logic is correct (maybe you have the fields reversed in the LIKE clause).
• If you're getting duplicate rows: Add a DISTINCT keyword to the SELECT clause if multiple PAYMENT records match a single CONTROL entry.

内容的提问来源于stack exchange，提问作者Irvıng Ngr