Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trouble with join

Posted on 2009-03-30
20
Medium Priority
?
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 2000 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

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

688 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