Trouble with join

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_percentage) 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
FROM 
((purchase_contact JOIN 
		(buyer_contact JOIN 
        	(auction JOIN
        		(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_buyer 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

Open in new window

MFredinAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
racekConnect With a Mentor Commented:
create view my_master
as
SELECT X.abbreviation, Y.*
FROM
( SELECT DM.deal_master_id, DM.purchase_contact_id, DM.auction_id, DM.origin_city, DM.deal_number, BC.*, PC.purchase_contact_name,
       S.abbreviation,
       AC.auction_name,
       U.first_name as inside_buyer_name      
 FROM purchase_contact PC
 JOIN deal_master    DM ON PC.purchase_contact_id = DM.purchase_contact_id
 JOIN  buyer_contact BC ON BC.buyer_contact_id = DM.outside_buyer_id
 JOIN  auction       AC ON AC.auction_id = DM.auction_id
 JOIN  userinfo      U  ON DM.inside_buyer = U.user_id
 LEFT JOIN  states   S  ON DM.origin_state_id = S.state_id ) AS X
           
LEFT JOIN
(SELECT SB.sale_buyer_name, B.first_name as inside_seller_name, SBY.sale_buyer_yard, DL.deal_master_id, DL.head_count, DL.weight, DL.purchase_price, DL.type, S2.sale_date
   FROM deal_lots DL
   LEFT JOIN (userinfo B              
              JOIN  sale            S2 ON S2.inside_seller = B.user_id
              JOIN  sale_buyer      SB ON S2.sale_buyer_id = SB.sale_buyer_id          
         LEFT JOIN sale_buyer_yard SBY ON S2.sale_buyer_yard_id = SBY.sale_buyer_yard_id)
         
    ON S2.sale_id = DL.sale_id) Y
   
ON X.deal_master_id = Y.deal_master_id?
0
 
racekCommented:
you can rewrite first part to be easy understanding as :
SELECT DM.*, BC.*, PC.*, DL.*, sale.*, states.abbreviation,
       sale_buyer.sale_buyer_name, .auction_name, SBY.sale_buyer_yard,
       B.first_name as inside_seller_name,U.first_name as inside_buyer_name
FROM purchase_contact  PC
JOIN deal_master    DM ON PC.purchase_contact_id = DM.purchase_contact_id
JOIN  buyer_contact BC ON BC.buyer_contact_id = DM.outside_buyer_id
JOIN  auction       AC ON AC.auction_id = DM.auction_id
JOIN  userinfo      U  ON ON DM.inside_buyer = U.user_id
LEFT JOIN  states   S  ON DM.origin_state_id = S.state_id
           
LEFT JOIN ...

second part is difficult to understand


0
 
vinurajrCommented:
You can add something like this......In the code.......
LEFT JOIN (hedge LEFT JOIN 
                 (deal_lots LEFT JOIN 
                           (userinfo B JOIN 
                	(sale_buyer 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 hedge.lot_id = deal_lots.lot_id)
ON deal_master.deal_master_id = deal_lots.deal_master_id)
WHERE 0 = 0

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MFredinAuthor Commented:
Thanks guys.

Vinurair, I tried your code but it returns deal_lots.lot_id as many times as there are hedge.lot_id.

For example.  If I have 5 records in hedge with lot_id of 123, when I run this query it will display the 123 lot_id record 5 times, etc. when it should only display it once.  I need to display the distinct lot_id and SUM the hedge.hedge_percentage for that lot_id.  Hope that makes sense.
0
 
racekCommented:
In SELECT X.* and DM.* etc replace .* with column names ...
SELECT X.*, Y.sale_buyer_name, Y.inside_seller_name
FROM
( SELECT DM.*, BC.*, PC.*, DL.*, sale.*, 
       S.abbreviation,
       AC.auction_name, 
       SBY.sale_buyer_yard,
       U.first_name as inside_buyer_name 
       
 
 FROM purchase_contact  PC
 JOIN deal_master    DM ON PC.purchase_contact_id = DM.purchase_contact_id
 JOIN  buyer_contact BC ON BC.buyer_contact_id = DM.outside_buyer_id
 JOIN  auction       AC ON AC.auction_id = DM.auction_id
 JOIN  userinfo      U  ON ON DM.inside_buyer = U.user_id
 LEFT JOIN  states   S  ON DM.origin_state_id = S.state_id ) AS X
           
LEFT JOIN 
(SELECT SB.sale_buyer_name, B.first_name as inside_seller_name, DL.deal_master_id
   FROM deal_lots DL
   LEFT JOIN (userinfo B               
              JOIN  sale            S2 ON S2.inside_seller = B.user_id 
              JOIN  sale_buyer      SB ON S2.sale_buyer_id = SB.sale_buyer_id           
         LEFT JOIN sale_buyer_yard SBY ON S2.sale_buyer_yard_id = SBY.sale_buyer_yard_id)
         
    ON S2.sale_id = DL.sale_id) Y
    
ON X.deal_master_id = Y.deal_master_id;

Open in new window

0
 
MFredinAuthor Commented:
Error Executing Database Query.

Unknown table 'DL'
0
 
racekCommented:
SELECT X.*, Y.*
FROM
( SELECT DM.*, BC.*, PC.*,
       S.abbreviation,
       AC.auction_name,    
       U.first_name as inside_buyer_name      
 FROM purchase_contact  PC
 JOIN deal_master    DM ON PC.purchase_contact_id = DM.purchase_contact_id
 JOIN  buyer_contact BC ON BC.buyer_contact_id = DM.outside_buyer_id
 JOIN  auction       AC ON AC.auction_id = DM.auction_id
 JOIN  userinfo      U  ON ON DM.inside_buyer = U.user_id
 LEFT JOIN  states   S  ON DM.origin_state_id = S.state_id ) AS X
           
LEFT JOIN
(SELECT SB.sale_buyer_name, B.first_name as inside_seller_name, SBY.sale_buyer_yard, DL.deal_master_id
   FROM deal_lots DL
   LEFT JOIN (userinfo B              
              JOIN  sale            S2 ON S2.inside_seller = B.user_id
              JOIN  sale_buyer      SB ON S2.sale_buyer_id = SB.sale_buyer_id          
         LEFT JOIN sale_buyer_yard SBY ON S2.sale_buyer_yard_id = SBY.sale_buyer_yard_id)
         
    ON S2.sale_id = DL.sale_id) Y
   
ON X.deal_master_id = Y.deal_master_id;
0
 
MFredinAuthor Commented:

Duplicate column name 'purchase_contact_id'
0
 
racekCommented:
In SELECT X.* and DM.* etc replace .* with column names ...

like:  X.abbreviation,
        X.auction_name,    
        X.inside_buyer_name,
.......
Y.sale_buyer_name, Y.inside_seller_name, Y.sale_buyer_yard,
...
0
 
racekCommented:
not only for X.. and Y even for DM.*, BC.*, PC.*,
Just write only column names you need with the correct prefix.
The problem is that you have both AC.auction_id and DM.auction_id it is enough wit only one of them it doesn't matter which one.
Sam thing for PC.purchase_contact_id and DM.purchase_contact_id - choos only one of them in select
0
 
MFredinAuthor Commented:
Element AUCTION_ID is undefined in MASTER.

But it is defined... DM.auction_id and all DM records have an auction_id assigned.
SELECT X.abbreviation, Y.*
FROM
( SELECT DM.deal_master_id, DM.purchase_contact_id, DM.auction_id, DM.origin_city, DM.deal_number, BC.*, PC.purchase_contact_name, 
       S.abbreviation,
       AC.auction_name,
       U.first_name as inside_buyer_name       
 FROM purchase_contact PC
 JOIN deal_master    DM ON PC.purchase_contact_id = DM.purchase_contact_id
 JOIN  buyer_contact BC ON BC.buyer_contact_id = DM.outside_buyer_id
 JOIN  auction       AC ON AC.auction_id = DM.auction_id
 JOIN  userinfo      U  ON DM.inside_buyer = U.user_id
 LEFT JOIN  states   S  ON DM.origin_state_id = S.state_id ) AS X
           
LEFT JOIN 
(SELECT SB.sale_buyer_name, B.first_name as inside_seller_name, SBY.sale_buyer_yard, DL.deal_master_id, DL.head_count, DL.weight, DL.purchase_price, DL.type, S2.sale_date
   FROM deal_lots DL
   LEFT JOIN (userinfo B               
              JOIN  sale            S2 ON S2.inside_seller = B.user_id 
              JOIN  sale_buyer      SB ON S2.sale_buyer_id = SB.sale_buyer_id           
         LEFT JOIN sale_buyer_yard SBY ON S2.sale_buyer_yard_id = SBY.sale_buyer_yard_id)
         
    ON S2.sale_id = DL.sale_id) Y
    
ON X.deal_master_id = Y.deal_master_id
 
WHERE 0=0

Open in new window

0
 
racekCommented:
AUCTION_ID should bu a column in the table deal_master    
I cannot se any table with name master
0
 
MFredinAuthor Commented:
MASTER is actually the name of my query... using coldfusion

<cfquery name="MASTER" datasource="#request.dsn#">
QUERY SQL
</cfquery>

0
 
MFredinAuthor Commented:
Are there any GUI Query builder tools out there that you would recommend to use for something like this?  
0
 
racekCommented:
toad for mysql - open source
0
 
MFredinAuthor Commented:
Any other ideas on this query, or should I try it with toad?
0
 
racekCommented:
and from coldfusion call just select * from my_master
0
 
racekCommented:
before you use your query from coldfusion  is it better to test the query in MySQL browser Toad or SQLyog
0
 
MFredinAuthor Commented:
I guess to me this new query is very confusing.  If we go back to the original query..  which is working great.  

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

FROM
((purchase_contact JOIN
            (buyer_contact JOIN
              (auction JOIN
                    (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_buyer 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



This query is working great.  All I'm trying to do is JOIN hedge with deal_lots ON hedge.lot_id = deal_lots.lot_id  and display SUM(hedge.hedge_percentage)as hedge_percentage.  There can be many hedge records to 1 deal_lot record.  I don't think the queries above are including the hedge table.

0
 
MFredinAuthor Commented:
I think what I will do is change a few things around on my tables to make this work.  Thanks for all the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.