SQL如何在一条记录中获取OR语句

Question

I have a query that returns more than one record because I am using an or

    SELECT m_o.ordernum, m_CD.fieldvalue
    FROM measurements.orders m_o
    INNER JOIN measurements.custom_data m_cd
    ON m_cd.ordernum = m_o.ordernum
    WHERE m_o.custnum = 'xxx'
    AND (m_cd.fieldname = 'primary_name' or m_cd.fieldname = 'secondary_name');

There is a way to get this returning one row but I am stumped.

Answer 1

To obtain the value associated with the primary name, if any, and the value associated with the secondary name, if any, in the same result rows, you can join measurements.custom_data twice. If you cannot rely on which one of those names has a value associated with it, then you must use outer joins. For example:

SELECT
  m_o.ordernum,
  m_cd1.fieldvalue AS fieldValue1
  m_cd2.fieldvalue AS fieldValue2
FROM
  measurements.orders m_o
  LEFT JOIN measurements.custom_data m_cd1
    ON m_cd1.ordernum = m_o.ordernum AND m_cd1.fieldname = 'primary_name'
  LEFT JOIN measurements.custom_data m_cd2
    ON m_cd2.ordernum = m_o.ordernum AND m_cd2.fieldname = 'secondary_name'
WHERE
  m_o.custnum = 'xxx'
  AND (m_cd1.ordernum IS NOT NULL OR m_cd2.ordernum IS NOT NULL)

Note that the fieldname predicates are moved into the (outer) join conditions. That directs which field value goes into which result column, and helps prevent duplicates.

Note also the NOT NULL conditions in the WHERE clause. These prevent results being returned that have no value for either name.