asked on

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

PortletPaul

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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?

PortletPaul

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