postgresql – Selecting different values from a table that joins on a single UID


I’m having some trouble with a pretty extensive query/report I’m trying to write.
The error I continue to get is: ERROR: more than one row returned by a subquery used as an expression SQL state: 21000. Now, this report has 60+ columns and references various tables. I believe the problematic part is that I need to select different results using the same unique ID (account.id).

For instance consider these tables:

ACCOUNT:           ORDERS:              Transaction:   
ID (pk)            ID (pk)                 ID (pk)        
fname              buyer_account_id        order_id       
lname              order_number            buyer_id
email                                      seller_id
phone

In the same row, the report calls for both buyer account information as well as seller account information. I assumed I would need to use a subquery to pull the seller information, as if I simply join the tables on one or the other, I won’t be able to get the others’ information.

so I did something like this:

select distinct 
orders.id as "ID",
concat(account.firstname, ' ', account.lastname) as "Buyer",
account.email as "Buyer Email",
account.phone as "Buyer Phone",
(
SELECT
    concat(account.firstname, ' ', account.lastname)
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller",
(
SELECT
    account.email
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Email",
(
SELECT
    account.phone
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Phone"
from orders
inner join transaction on transaction.order_id = orders.id
join account on orders.buyer_account_id = account.id

When I run this in my test environment, it runs without issue. However, when run against a production db with thousands of entries, there are multiple results returned for the subquery(ies).

This is a postgresql db.

How can I improve my query so that I wont have this problem?

The actual query is much longer and uses a few additional tables, but I’m not able to provide a reproducible example or example data for it. If you’d like to have a look, the real query is below.

select distinct
orders.id as "ID",
concat(account.firstname, ' ', account.lastname) as "Buyer",
account.email as "Buyer Email",
account.phone as "Buyer Phone",
(
SELECT
    address.address
    from address
    where address.id = orders.delivery_address_id
    and orders.id = transaction.order_id
) as "Buyer Address",
(
SELECT
    address.zip
    from address
    where address.id = orders.delivery_address_id
    and orders.id = transaction.order_id
) as "Buyer Zip",
(
SELECT
    address.neighborhood
    from address
    where address.id = orders.delivery_address_id
    and orders.id = transaction.order_id
) as "Buyer Neighborhood",
(
SELECT
    concat(account.firstname, ' ', account.lastname)
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller",
(
SELECT
    account.email
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Email",
(
SELECT
    account.phone
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Phone",
(
SELECT
    address.address
    from address
    where address.id = orders.store_address_id
    and orders.id = transaction.order_id
) as "Seller Address",
(
SELECT
    address.zip
    from address
    where address.id = orders.store_address_id
    and orders.id = transaction.order_id
) as "Seller Zip",
(
SELECT
    address.neighborhood
    from address
    where address.id = orders.store_address_id
    and orders.id = transaction.order_id
) as "Seller Neighborhood",
(
SELECT
    order_item.quantity
    from order_item
    where order_item.order_id = orders.id
    and transaction.order_id = orders.id
) as "Quantity",
(
SELECT
    order_item.kitchen_item_name
    from order_item
    where orders.id = order_item.order_id
    and transaction.order_id = orders.id
) as "Store Items",
orders.promo_code_id as "Promo Code",
orders.deliverytype as "Delivery Type",
orders.order_preference as "Order Preference",
orders.pickup_date_time as "Pickup/Delivery DateTime",
orders.placed as "Order Placed",
(
SELECT
    SUM(order_item.purchase_price)
    from order_item
    where order_item.order_id = orders.id
    and orders.id = transaction.order_id
    group by orders.id, order_item.id
) as "Buyer Food Total",
(
SELECT
    COALESCE(SUM(order_item.purchase_price),0) - COALESCE(sum(order_item.iva_on_purchase_price),0)
    from order_item
    where order_item.order_id = orders.id
    and orders.id = transaction.order_id
    group by orders.id, order_item.id
) as "Buyer Food Total Pre IVA",
transaction.delivery_fee as "Delivery Fee",
orders.discounted_amount as "Discounted Amount",
transaction.total as "Buyer Total",
(
SELECT 
    COALESCE(transaction.total,0) - COALESCE(transaction.iva_on_order_amount,0)
    from transaction
    where transaction.order_id = orders.id
) as "Order Total Pre IVA",
transaction.iva_on_order_amount as "IVA On Order Total",
transaction.delivery_fee_pre_iva as "Delivery Fee Pre IVA",
transaction.fees_sub_total_pre_iva as "Savorly Fee Pre IVA",
transaction.iva_on_total_fees as "IVA On Fees",
transaction.total_fees as "Fees Total Incl IVA",
transaction.net_profit_pre_iva as "Net Profit",
transaction.customer_discount as "Customer Discount",
transaction.marketing_discount as "Food Discount",
transaction.delivery_discount as "Delivery Discount",
--DeliveryBlindDiscount,
transaction.marketing_discount_iva_fee as "IVA On Marketing Discount",
transaction.delivery_discount_iva_fee as "IVA On Delivery Discount",
transaction.iva_on_savorly_fee as "IVA On Savorly Fee",
transaction.fees_sub_total_pre_iva as "Fees Sub Total Pre IVA",
transaction.iva_on_net_profit as "IVA On Net Profit",
transaction.net_profit_pre_iva as "Net Profit Pre IVA",
--Merchant Fee pre iva,
transaction.provider_payout_transaction_id as "Provider Transaction Id",
transaction.payout_custom_id as "Provider Payout Transaction Id",
--iVoy Order Id,
transaction.os_type as "OS Type",
transaction.os_version as "OS Version",
transaction.app_version as "App Version",
transaction.notes as "Notes",
transaction.is_paid as "Is Paid",
transaction.status as "Status"
--Fechas, 
--Cupons, 
--Organic, 
--Week
from orders
inner join transaction on transaction.order_id = orders.id
join account on orders.buyer_account_id = account.id
join store_address on orders.store_address_id = store_address.id
join address on store_address.address_id = address.id
join order_item on orders.id = order_item.order_id
where orders.placed >= '2020-09-28' AND orders.placed <= '2020-10-8';