inner join SQL

Hi All:
I've two table approximately  with same structure First table should be inner join with "agency" and the second should be inner join with the same table "agency"
First One: "tktrans"
agentid , tktno, fare, total, comm, issuedate

Second " extktrans"
agentid , tktno, fare, total, comm, issuedate

i'd like to make query to get me the count and sum of the both table. I'd wrote the following code but the result was incorrect
                SQL.Add('Select a.agentid, bb.Agency , count(a.agentid)+count(cc.agentid) as tktcount,Sum(a.Fare)+Sum(cc.Fare) as fare, sum(a.totax)+sum(cc.totax)  as tax,');
                SQL.Add('Sum((a.agentcomm*a.fare)/100)+Sum((cc.agentcomm*cc.fare)/100) as agentComm, Sum((a.OfficeComm*a.fare)/100)+Sum((cc.OfficeComm*cc.fare)/100) as OfficeComm, sum(a.grandtot)+sum(cc.grandtot) as grandtot,');
                SQL.Add('Sum(a.grandtot)-(Sum((a.agentcomm*a.fare)/100)+Sum((a.OfficeComm*a.fare)/100))+Sum(cc.grandtot)-(Sum((cc.agentcomm*cc.fare)/100)+Sum((cc.OfficeComm*cc.fare)/100)) as Airlinesdue, sum(a.paid)+sum(cc.paid) as paid,  sum(a.credit)+sum(cc.credit) as credit');
                SQL.Add('From tktrans a');
                SQL.Add('INNER join extktrans cc on a.agentid=cc.agentid');
                SQL.Add('inner join Agencies bb on  a.agentid=bb.agencyid');
                SQL.add('where a.issuedate>= :D1 and a.issuedate<= :D2 and cc.issuedate>= :D1 and cc.issuedate<= :D2');

Open in new window

DETAILS.png
Err1.png
khaled salemSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Have you forgotten the "GROUP BY"?  GROUP BY a.agentid, bb.Agency

when you say you are getting incorrect results are values smaller than expected?

The inner joins requires that both all tables tables have a match, is it possible for records to exist in tktrans that don't match to extktrans? (or vice-versa?)

perhaps try this join structure as a test:

FROM Agencies bb
LEFT JOIN extktrans cc ON bb.agentid = cc.agentid
                      AND cc.issuedate >= :D1
                      AND cc.issuedate <= :D2
LEFT JOIN tktrans a ON bb.agentid = a.agentid
                   AND a.issuedate >= :D1
                   AND a.issuedate <= :D2

Open in new window

some sample data from each table would help us identify a solution.

At the extreme, it may pay to treat both transaction tables as independent subqueries, like this:
SELECT
          bb.agentid 
        , bb.Agency
        , tk.countOf + ex.countOf       AS countOf
        , tk.fare + ex.fare             AS fare
        , tk.tax + ex.tax               AS tax
        , tk.agentComm + ex.agentComm   AS agentComm
        , tk.OfficeComm + ex.OfficeComm AS OfficeComm
        , tk.grandtot + ex.grandtot     AS grandtot
        , tk.paid + ex.paid             AS paid
        , tk.credit + ex.credit         AS credit
        , tk.grandtot - ((tk.agentcomm * tk.fare) / 100) + ((tk.OfficeComm * tk.fare) / 100)
        + ex.grandtot - ((ex.agentcomm * ex.fare) / 100) + ((ex.OfficeComm * ex.fare) / 100) AS Airlinesdue
FROM Agencies bb
LEFT JOIN (
            SELECT
                  a.agentid
                , count(a.agentid)                    AS countOf
                , Sum(a.Fare)                         AS fare
                , sum(a.totax)                        AS tax
                , Sum((a.agentcomm * a.fare) / 100)   AS agentComm
                , Sum((a.OfficeComm * a.fare) / 100)  AS OfficeComm
                , sum(a.grandtot)                     AS grandtot
                , sum(a.paid)                         AS paid
                , sum(a.credit)                       AS credit
            FROM tktrans a
            WHERE a.issuedate >= :D1
              AND a.issuedate <= :D2
            GROUP BY
                   a.agentid
            ) AS tk ON bb.agentid = tk.agentid
LEFT JOIN (
            SELECT
                  cc.agentid
                , count(cc.agentid)                    AS countOf
                , Sum(cc.Fare)                         AS fare
                , sum(cc.totax)                        AS tax
                , Sum((cc.agentcomm * cc.fare) / 100)  AS agentComm
                , Sum((cc.OfficeComm * cc.fare) / 100) AS OfficeComm
                , sum(cc.grandtot)                     AS grandtot
                , sum(cc.paid)                         AS paid
                , sum(cc.credit)                       AS credit
            FROM extktrans cc
            WHERE cc.issuedate >= :D1
              AND cc.issuedate <= :D2
            GROUP BY
                   cc.agentid
           ) AS ex ON bb.agentid = ex.agentid

Open in new window

0
PortletPaulfreelancerCommented:
sorry, as I went that far I may as well do this too. IF there is a possibility that the 2 transaction sources don't align by agency, then nulls may result due to the left joins, and (I believe) this caters for that possibility (untested of course). Note I have separated the two count columns so that it becomes obvious if a mis-match exists.
SELECT
          bb.agentid 
        , bb.Agency
        , tk.countOf                    AS tkcountof
        , ex.countOf                    AS excountOf
        , IFNULL(tk.fare,0) + IFNULL(ex.fare,0)             AS fare
        , IFNULL(tk.tax,0) + IFNULL(ex.tax ,0)              AS tax
        , IFNULL(tk.agentComm,0) + IFNULL(ex.agentComm,0)   AS agentComm
        , IFNULL(tk.OfficeComm,0) + IFNULL(ex.OfficeComm,0) AS OfficeComm
        , IFNULL(tk.grandtot,0) + IFNULL(ex.grandtot,0)     AS grandtot
        , IFNULL(tk.paid,0) + IFNULL(ex.paid,0)             AS paid
        , IFNULL(tk.credit,0) + IFNULL(ex.credit,0)         AS credit
        , IFNULL(tk.grandtot - ((tk.agentcomm * tk.fare) / 100) + ((tk.OfficeComm * tk.fare) / 100),0)
        + IFNULL(ex.grandtot - ((ex.agentcomm * ex.fare) / 100) + ((ex.OfficeComm * ex.fare) / 100),0) AS Airlinesdue
FROM Agencies bb
LEFT JOIN (
            SELECT
                  a.agentid
                , count(a.agentid)                    AS countOf
                , Sum(a.Fare)                         AS fare
                , sum(a.totax)                        AS tax
                , Sum((a.agentcomm * a.fare) / 100)   AS agentComm
                , Sum((a.OfficeComm * a.fare) / 100)  AS OfficeComm
                , sum(a.grandtot)                     AS grandtot
                , sum(a.paid)                         AS paid
                , sum(a.credit)                       AS credit
            FROM tktrans a
            WHERE a.issuedate >= :D1
              AND a.issuedate <= :D2
            GROUP BY
                   a.agentid
            ) AS tk ON bb.agentid = tk.agentid
LEFT JOIN (
            SELECT
                  cc.agentid
                , count(cc.agentid)                    AS countOf
                , Sum(cc.Fare)                         AS fare
                , sum(cc.totax)                        AS tax
                , Sum((cc.agentcomm * cc.fare) / 100)  AS agentComm
                , Sum((cc.OfficeComm * cc.fare) / 100) AS OfficeComm
                , sum(cc.grandtot)                     AS grandtot
                , sum(cc.paid)                         AS paid
                , sum(cc.credit)                       AS credit
            FROM extktrans cc
            WHERE cc.issuedate >= :D1
              AND cc.issuedate <= :D2
            GROUP BY
                   cc.agentid
           ) AS ex ON bb.agentid = ex.agentid        
 WHERE  tk.agentid IS NOT NULL
     OR ex.agentid IS NOT NULL

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
A grading of "C" is the lowest possible- and considered very harsh. This is not a C grade answer as far as I can see. Please explain why you chose a C grading.
C should only be given for an incomplete solution that does not fully address or answer the question.  ... The asker must justify giving a C grade and give the experts an opportunity to improve it.

...

If you feel that an inappropriate grade has been awarded, you may use the Request Attention feature to contact the site Moderators, who are able to change the grade awarded at their discretion.
SEE: What grade should I award?
0
PortletPaulfreelancerCommented:
khaledsalem

I have requested attention on this question and hope you will re-consider the grading, or provide details on why the answer didn't meet your needs.

Thank you,
PortletPaul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.