Solved

Trouble with join

Posted on 2009-03-30
20
225 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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