I've got the following SQL that works great. I recently added a new table called hedge that I am trying to join with the deal_lots table. It is a 1 to many relationship. 1 record in deal_lots to many records in hedge.
The join should look something like this...
deal_lots LEFT JOIN hedge ON hedge.lot_id = deal_lots.lot_id
I also need to add "SUM(hedge.hedge_percentag
e) as hedge_percentage" to the field list.
I am trying to fit it into this query to make it work but so far I haven't had any luck. Any help is appreciated. I'm using MySQL 5.
SELECT deal_master.*, buyer_contact.*, purchase_contact.*, deal_lots.*, sale.*, states.abbreviation, sale_buyer.sale_buyer_name, auction.auction_name, sale_buyer_yard.sale_buyer_yard,
B.first_name as inside_seller_name,
A.first_name as inside_buyer_name
(userinfo A JOIN
(deal_master LEFT JOIN states ON deal_master.origin_state_id = states.state_id)
ON deal_master.inside_buyer = A.user_id)
ON auction.auction_id = deal_master.auction_id)
ON buyer_contact.buyer_contact_id = deal_master.outside_buyer_id)
ON purchase_contact.purchase_contact_id = deal_master.purchase_contact_id)
LEFT JOIN (deal_lots LEFT JOIN
(userinfo B JOIN
(sale LEFT JOIN sale_buyer_yard ON sale.sale_buyer_yard_id = sale_buyer_yard.sale_buyer_yard_id)
ON sale.sale_buyer_id = sale_buyer.sale_buyer_id)
ON sale.inside_seller = B.user_id)
ON sale.sale_id = deal_lots.sale_id)
ON deal_master.deal_master_id = deal_lots.deal_master_id)
WHERE 0 = 0