Объединение UNION ALL и OUTER JOIN в операторе SQL

У меня есть два запроса, один использует функцию объединения всех.

select * from (

Select 

ticket_id as Ticket_Num
, name as Name
, approved_at as Approval_Date
, price as Price
, quantity as Quantity
, (price * quantity) as Cumulative_Price

From purchase_list_items

union all 

select
   ''
, ''
, datetime('now')
, ''
, 'Total'
,  sum(in1.Cumulative_Price) 


from (

select 
  ticket_id as Ticket_Num
, name as Name
, approved_at as Approval_Date
, price as Price
, quantity as Quantity
, (price * quantity) as Cumulative_Price

From purchase_list_items
    )
  in1 

  ) 
fullInner1



order by Approval_Date

А другой — простое внешнее соединение слева/справа.

select 
  uw.first_name as Purchased_By
from tickets t

left outer join ticket_work w on t.id = w.ticket_id
left outer join users uw on w.user_id = uw.id

where time_spent is not null

group by uw.id, uw.first_name, t.c_location

Я хочу иметь возможность объединить второй запрос в первый, чтобы создать столбец «Purchased_By». Однако я борюсь с его реализацией из-за UNION ALL.

По сути, каждый билет имеет связанный с ним user_id. К каждому user_id прикреплено имя first_name. У меня две части разделены, работают нормально.

Например, user_id — это Боб. И это отлично работает во втором запросе. Я просто хочу показать, что Боб написал тикет № 12345.

РЕДАКТИРОВАТЬ: вот операторы создания таблицы

CREATE TABLE "purchase_list_items" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(200) NOT NULL, "user_id" integer NOT NULL, "purchased" boolean DEFAULT 'f', "ticket_id" integer, "created_at" datetime, "received" boolean DEFAULT 'f', "received_at" datetime, "purchased_at" datetime, "price" float DEFAULT 0.0, "approved" boolean DEFAULT 'f', "approved_at" datetime, "charge_to" varchar(255) DEFAULT '', "agreement_id" integer, "part_number" varchar(255), "purchase_order" varchar(255), "notes" text, "quantity" integer DEFAULT 1, "shipping_code" varchar(255), "updated_at" datetime, "purchased_for_id" integer, "purchased_for_type" varchar(255), "category" varchar(255) DEFAULT 'Miscellaneous', "subcategory" varchar(255) DEFAULT 'Uncategorized', "order_number" varchar(255), "quote_id" integer DEFAULT 0, "upc" varchar(255) DEFAULT '', "gid" varchar(255) DEFAULT '', "research_class" varchar(255) DEFAULT '', "research_code" varchar(255) DEFAULT '', "product_image" varchar(255) DEFAULT '', "purchase_link" varchar(255) DEFAULT '', "vendor_id" integer);

CREATE INDEX "index_purchase_list_items_on_user_id" ON "purchase_list_items" ("user_id");

CREATE INDEX "index_purchase_list_items_on_ticket_id" ON "purchase_list_items" ("ticket_id");

CREATE INDEX "index_purchase_list_items_on_agreement_id" ON "purchase_list_items" ("agreement_id");

CREATE INDEX "index_purchase_list_items_on_purchased_for" ON     "purchase_list_items" ("purchased_for_id", "purchased_for_type");

Вторая таблица:

CREATE TABLE "tickets" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "summary" varchar(50) NOT NULL, "status" varchar(255) NOT NULL, "description" text, "priority" integer, "created_at" datetime, "updated_at" datetime, "closed_at" datetime, "created_by" integer, "assigned_to" integer, "viewed_at" datetime, "reopened" boolean, "requires_purchase" boolean, "category" varchar(255), "external_id" varchar(255), "email_message_id" varchar(255), "status_updated_at" datetime, "warning_alert_count" integer DEFAULT 0, "error_alert_count" integer DEFAULT 0, "muted" boolean, "site_id" integer, "master_ticket_id" integer, "c_location" varchar(255), "c_jobscope_case_id" text, "reported_by_id" integer, "due_at" datetime, "c_status" varchar(255) DEFAULT 'Active', "c_jobscope_database" varchar(255) DEFAULT 'ALL', "remote_id" integer, "synced_at" datetime, "sharer_id" integer, "parent_id" integer, "c_original_est_due_date" date, "c_jobscope_priority" varchar(255), "c_priority" integer, "c_sciforma_ticket_number" integer DEFAULT NULL);

CREATE INDEX "index_tickets_on_remote_id" ON "tickets" ("remote_id");

CREATE INDEX "index_tickets_on_synced_at" ON "tickets" ("synced_at");

CREATE INDEX "index_tickets_on_sharer_id" ON "tickets" ("sharer_id");

CREATE INDEX "index_tickets_on_parent_id" ON "tickets" ("parent_id");

CREATE INDEX "index_tickets_on_assigned_to" ON "tickets" ("assigned_to");

CREATE INDEX "index_tickets_on_status" ON "tickets" ("status");

CREATE INDEX "index_tickets_on_due_at" ON "tickets" ("due_at");

mysql sqlite left-join union-all right-join
person Kjbojang1es    schedule 18.06.2015    source источник
comment
Можете ли вы предоставить операторы создания таблиц для таблиц, на основе которых вы хотите построить окончательный результат? И несколько строк поддельных выборочных данных тоже помогут.   -  person mba12    schedule 18.06.2015
comment
Также было бы полезно изложение варианта использования. Что-то вроде того, что я хотел бы получить все whoosies для whatsies, где у whoosie есть шутка, а у whatsie есть подмигивание, и подмигивание было добавлено на прошлой неделе.   -  person John    schedule 18.06.2015

Ответы (1)


arrow_upward
0
arrow_downward

Таблица purchase_list_items уже содержит user_id. Запрос для поиска соответствующего имени будет выглядеть так:

SELECT first_name
FROM users
WHERE user_id = ?

Его можно напрямую интегрировать в более крупный запрос как коррелированный подзапрос:

SELECT ...,
       (price * quantity) as Cumulative_Price,
       (SELECT first_name
        FROM users
        WHERE user_id = purchase_list_items.user_id
       ) AS Purchased_By
FROM purchase_list_items
...
person CL.    schedule 18.06.2015