Solved

Trouble with join

Posted on 2009-03-30
20
227 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
ID: 24026060
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
ID: 24026174
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
ID: 24028644
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 14

Expert Comment

by:racek
ID: 24028990
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
ID: 24029274
Error Executing Database Query.

Unknown table 'DL'
0
 
LVL 14

Expert Comment

by:racek
ID: 24029449
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
ID: 24029693

Duplicate column name 'purchase_contact_id'
0
 
LVL 14

Expert Comment

by:racek
ID: 24029826
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
ID: 24029876
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
ID: 24030326
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
 
LVL 14

Expert Comment

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

Author Comment

by:MFredin
ID: 24030997
MASTER is actually the name of my query... using coldfusion

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

0
 

Author Comment

by:MFredin
ID: 24031004
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
ID: 24031037
toad for mysql - open source
0
 

Author Comment

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

Accepted Solution

by:
racek earned 500 total points
ID: 24031224
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
ID: 24031246
and from coldfusion call just select * from my_master
0
 
LVL 14

Expert Comment

by:racek
ID: 24031309
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
ID: 24031545
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
ID: 24032212
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Creating and Managing Databases with phpMyAdmin in cPanel.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

829 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