Solved

Trouble with join

Posted on 2009-03-30
20
218 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:MFredin
  • 10
  • 9
20 Comments
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
 
LVL 8

Expert Comment

by:vinurajr
Comment Utility
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
 

Author Comment

by:MFredin
Comment Utility
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
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
 

Author Comment

by:MFredin
Comment Utility
Error Executing Database Query.

Unknown table 'DL'
0
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
 

Author Comment

by:MFredin
Comment Utility

Duplicate column name 'purchase_contact_id'
0
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
 

Author Comment

by:MFredin
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 14

Expert Comment

by:racek
Comment Utility
AUCTION_ID should bu a column in the table deal_master    
I cannot se any table with name master
0
 

Author Comment

by:MFredin
Comment Utility
MASTER is actually the name of my query... using coldfusion

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

0
 

Author Comment

by:MFredin
Comment Utility
Are there any GUI Query builder tools out there that you would recommend to use for something like this?  
0
 
LVL 14

Expert Comment

by:racek
Comment Utility
toad for mysql - open source
0
 

Author Comment

by:MFredin
Comment Utility
Any other ideas on this query, or should I try it with toad?
0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
Comment Utility
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
 
LVL 14

Expert Comment

by:racek
Comment Utility
and from coldfusion call just select * from my_master
0
 
LVL 14

Expert Comment

by:racek
Comment Utility
before you use your query from coldfusion  is it better to test the query in MySQL browser Toad or SQLyog
0
 

Author Comment

by:MFredin
Comment Utility
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
 

Author Comment

by:MFredin
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now