Присоединение к нескольким таблицам, не возвращающим нужные данные в MySQL

У меня есть три таблицы в моей базе данных (contracts, partners и customers), в которых контракт может использоваться как для клиентов, так и для партнеров.

Я храню данные контракта только в таблице contracts и таблице customers, а таблица partners содержит поле с именем contract_id, которое является внешним ключом для поля contracts id.

Нет, я хочу выбрать контракты и показать их в списке, но если контракт использовался для клиента и партнера одновременно, я хочу, чтобы мой список отображал их обоих, но я не могу заставить его работать.

Мой запрос:

SELECT c.*, p.id AS partner_id, p.name AS partner_name, 
       cu.id AS customer_id, cu.name AS customer_name
FROM contracts AS c 
LEFT JOIN partners AS p ON c.id = p.contract_id
LEFT JOIN customers AS cu ON c.id = cu.contract_id

ОБРАЗЦЫ: записи таблицы contract выглядят следующим образом:

    id  |   title   |   contract_start  |   contract_end
-------------------------------------------------------------
    1   |   Test    |   2012-10-02      |   2013-10-02
    2   |   Test2   |   2012-09-27      |   2013-09-27

Записи таблицы customers выглядят так:

    id  |   code    |   name                |   contract_id
-------------------------------------------------------------
    1   |   123456  |   Customer1           |   1
    2   |   654321  |   Dummy Co. LTD.      |   2

Записи таблицы partners выглядят так:

    id  |   code    |   name                |   contract_id
-------------------------------------------------------------
    1   |   789456  |   Partner1            |   1

Нет, я хочу список с 3 записями, каждая из которых показывает контракт (учитывая, что одна из них повторяется), и каждая показывает имя и идентификатор партнера или клиента. Если в договоре участвует клиент, поле партнера должно быть null и наоборот.


mysql jointable
person Farid Rn    schedule 07.12.2012    source источник
comment
у вас есть ON c.id = cu.id. Разве это не должно быть ON c.id = cu.contract_id?   -  person dnagirl    schedule 07.12.2012
comment
@dnagirl спасибо, но на результаты это не влияет :D   -  person Farid Rn    schedule 07.12.2012
comment
Поскольку исправление условия соединения ничего не исправляет, возможно, вы могли бы опубликовать некоторые образцы данных и результаты вашего запроса по этим образцам данных?   -  person dnagirl    schedule 07.12.2012
comment
Это выглядит правильно, как есть. Пожалуйста, опубликуйте простой тестовый пример.   -  person Michael Berkowski    schedule 07.12.2012
comment
Насколько я понимаю, с LEFT JOIN вы никогда не получите больше строк в своем результирующем наборе, чем в левой таблице предложения соединения. Таким образом, вы, вероятно, видите только одну строку из таблицы контрактов, но для этой строки заполнены как partner_{id,name}, так и customer_{id,name}. Это так?   -  person Justin ᚅᚔᚈᚄᚒᚔ    schedule 07.12.2012
comment
@Justinᚅᚔᚈᚄᚒᚔ Извините, я не смог лучше описать свой вопрос, но это именно то, что вы говорите!   -  person Farid Rn    schedule 07.12.2012
comment
кажется, что с запросом все в порядке. Остаются либо данные, либо определения столбцов. Попробуйте выполнить запрос только с присоединенной таблицей partners, а затем только с присоединенной таблицей customers. Это позволит вам узнать, какая таблица ведет себя неправильно.   -  person dnagirl    schedule 07.12.2012
comment
@dnagirl Я не думаю, что с моими записями в таблице что-то не так. Результаты, которые я сейчас получаю, представляют собой 2 записи, одна из которых содержит данные как о клиенте, так и о партнере, я хочу, чтобы каждая из них была в отдельных строках.   -  person Farid Rn    schedule 07.12.2012
comment
Я обычно выбираю звездочку для всех при устранении неполадок, чтобы увидеть, что возвращается. При левом (или правом) соединении пустые значения будут заполнены для строк, которые не соответствуют исходной таблице слева (или справа).   -  person Mike Cheel    schedule 07.12.2012
comment
@MikeCheel Это именно то, что я хочу, я хочу, чтобы возвращались нули, но он возвращает строку, содержащую данные как о клиенте, так и о партнере.   -  person Farid Rn    schedule 07.12.2012

Ответы (1)


arrow_upward
4
arrow_downward

Ты говоришь:

Нет, я хочу выбрать контракты и показать их в списке, но если контракт использовался одновременно для клиента и партнера, я хочу, чтобы мой список показывал их обоих.

Вероятно, вам нужно объединить две таблицы отдельно и использовать UNION:

SELECT c.*, 
       p.id AS partner_id, p.name AS partner_name, 
       NULL AS customer_id, NULL AS customer_name
FROM contracts AS c 
INNER JOIN partners AS p ON c.id = p.contract_id

UNION ALL

SELECT c.*, 
       NULL AS partner_id, NULL AS partner_name, 
       cu.id AS customer_id, cu.name AS customer_name
FROM contracts AS c 
INNER JOIN customers AS cu ON c.id = cu.contract_id

UNION ALL

SELECT c.*, 
       NULL, NULL,
       NULL, NULL 
FROM contracts AS c 
WHERE NOT EXISTS ( SELECT *  
                   FROM partners AS p
                   WHERE c.id = p.contract_id
                 )
  AND NOT EXISTS ( SELECT *
                   FROM customers AS cu 
                   WHERE c.id = cu.contract_id 
                 ) ;

(Другой способ с другой настройкой вывода)
При желании вы можете объединить последние четыре столбца в два, добавив столбец, чтобы различать партнеров и клиентов:

SELECT c.*, 
       p.id AS partner_customer_id, p.name AS partner_customer_name, 'P' AS type 
FROM contracts AS c 
INNER JOIN partners AS p ON c.id = p.contract_id

UNION ALL

SELECT c.*, 
       cu.id , cu.name, 'C'
FROM contracts AS c 
INNER JOIN customers AS cu ON c.id = cu.contract_id ;
person ypercubeᵀᴹ    schedule 07.12.2012
comment
Я нашел его всего минуту назад, но он мне не понравился, потому что я думал, что смогу найти лучшее решение, вы уверены, что нет более приятных и простых решений? - person Farid Rn; 07.12.2012
comment
Я не могу придумать ничего проще для этого вывода. - person ypercubeᵀᴹ; 07.12.2012
comment
Я хочу создать контракт и использовать его позже! этот запрос показывает только контракты, связанные с клиентом или партнером. - person Farid Rn; 07.12.2012
comment
@faridv: В своем вопросе вы говорите, что хотите получить 3 строки (с учетом данных вашего примера) с дубликатами контракта 1 и с полями клиента или поля партнера как NULL для каждого дубликата. Это именно то, что делает первый запрос в этом ответе. Итак, вам нужны только связанные контракты, как вы говорите в своем вопросе, или вам нужны несвязанные контракты? - person Justin ᚅᚔᚈᚄᚒᚔ; 08.12.2012
comment
@Justinᚅᚔᚈᚄᚒᚔ Когда я писал вопрос, я думал только о связанных контрактах, но когда я нашел это решение и использовал его, я понял, что сначала создаются контракты, а затем им назначаются клиенты или партнеры. Итак, я создаю контракт, но не могу его видеть, пока не свяжу с ним клиента или партнера! Итак, теперь я хочу, чтобы как связанные контракты, так и несвязанные контракты были в одном списке. Конечно, если это возможно! - person Farid Rn; 08.12.2012