基于意图与槽位属性获取tbl_output.output的SQL合并查询需求

Got it, let's tackle this problem. You want to replace those multiple step queries with a single join-based query that fetches the correct output from tbl_output when matching the intent "Car" AND all three slots: "2018", "Chevrolet", "Corvette".

The key here is ensuring we only return the output_id that is associated with all the required slots (not just some of them). Here's how to do it:

SELECT o.output
FROM tbl_intent i
INNER JOIN xref_intent_slot x ON i.id = x.intent_id
INNER JOIN tbl_slot s ON x.slot_id = s.id
INNER JOIN tbl_output o ON x.output_id = o.id
WHERE 
  i.name = 'Car'
  AND s.name IN ('2018', 'Chevrolet', 'Corvette')
GROUP BY o.id, o.output
HAVING COUNT(DISTINCT s.id) = 3;

Breakdown of how this works: #

• Join the tables: We link the intent to its cross-reference entries, then to the slots, and finally to the output table to connect all related data.
• Filter initial matches: The WHERE clause narrows down results to only the "Car" intent and your three target slots.
• Ensure all slots are matched: The GROUP BY groups results by output, and the HAVING clause checks that we have exactly 3 distinct slot IDs matched (one for each required slot). This filters out any output that only matches a subset of the slots.

Why your previous LEFT JOIN returned extra records: #

A basic LEFT JOIN would keep all entries from the left table even if not all slots are matched, leading to partial matches being included. Using INNER JOIN combined with GROUP BY + HAVING ensures we only get outputs that are associated with every one of your specified slots.

If you need to make this more dynamic (e.g., variable number of slots), you could adjust the count in the HAVING clause to match the number of slots in your IN list, but for your specific case, COUNT(DISTINCT s.id) = 3 works perfectly.

内容的提问来源于stack exchange，提问作者Kauffju3