Link to home
Start Free TrialLog in
Avatar of John_2357
John_2357

asked on

mysql INNER JOIN not working like I thought

I have two tables:

muni.issue_details isd     --  multiple identical cusip6, different dollar amounts in Amt_Offer_dlr

identifiers ide                  -- each cusip6 is unique


SELECT CURRENT_DATE, FORMAT(SUM(isd.Amt_Offer_Dlr),0) 'Current Out Standing Debt:'
from muni.issue_details isd
INNER JOIN identifiers ide ON ide.fk_cusip6 = isd.fk_cusip6 AND  isd.fk_cusip6 = '567541'
where isd.maturity > CURRENT_DATE
;



Looking at the attached file the query should return (looking fk_cusip6 = 567090)

4000   131472
......     ......          ......
......     ......          ......
......     ......          ......
......     ......          ......
a whole lot of returns

All I am trying to do is to sum all of the isd.Amt_Offer_Dlr when ever the fk_cusip6 numbers match with the corresponding id number in the identifier table . Right now it is only giving me one result and it looks like it is the sum of all matchs.
sample.txt
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Your join criteria are:

ON ide.fk_cusip6 = isd.fk_cusip6 AND  isd.fk_cusip6 = '567541'

Open in new window


What makes you think you are going to get anything other than results where isd.fk_cusip6 = '567541'?
Avatar of John_2357
John_2357

ASKER

I guess I need to re-word my question.

CREATE  TABLE IF NOT EXISTS `issue_details` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    amt_offer_dlr INT NOT NULL DEFAULT 9999 COMMENT '564,510,000',
   maturity  DATE NOT NULL DEFAULT '1111-11-11' COMMENT '06/28/2012' ,
    fk_cusip6 VARCHAR(6) NOT NULL DEFAULT '' COMMENT '' ,
  PRIMARY KEY (id),
  CONSTRAINT con_issue_details__issuers FOREIGN KEY (fk_cusip6) REFERENCES issuers (fk_cusip6) ON DELETE NO ACTION ON UPDATE CASCADE
)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `identifiers` (
  id     INT NOT NULL AUTO_INCREMENT ,
  fk_cusip6 VARCHAR(6)  NOT NULL DEFAULT 'NA' ,
  PRIMARY KEY (id) ,
  CONSTRAINT con_ident__cusip6  FOREIGN KEY (fk_cusip6) REFERENCES cusip_6 (cusip6) ON DELETE NO ACTION ON UPDATE CASCADE ,
)
COMMENT ''
ENGINE = InnoDB;

--

issue_details table:
note: each id is different
          can have multiple identical fk_cusip6 numbers

+-----+--------------------+-----------------+-------------------------+  
| id | maturity     | fk_cusip6  |  amt_offer_dlr |
+-----+--------------------+-----------------+-------------------------+
|  1 | 2013-06-28 | 567090    |  1000               |
|  2 | 2014-06-05 | 567090    |  1000               |
|  3 | 2013-06-05 | 567100    |  2500               |
|  3 | 2014-06-05 | 567100    |  2500               |
+-----+--------------------+-----------------+-------------------------+

identifier table:
note: each fk_cusip6 number is different

+--------------+----------------+
| id          | fk_cusip6 |
+--------------+----------------+
| 131472 | 567090   |
| 131473 | 567100   |
+--------------+----------------+

I need to generate a query that will match each instance of fk_cusip6 in the identifier table, and then sum amt_offer_dlr for all instances that have the same fk_cusip6 in the issue_details table. The end result should be:

+--------------+----------------------+----------------------------------------+
| ide.id    | isd.fk_cusip6 |  SUM(isd.Amt_Offer_Dlr |
+--------------+----------------------+----------------------------------------+
| 131472 | 567090         | 2000                               |
| 131473 | 567100         | 5000                               |
+--------------+----------------------+----------------------------------------+
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you