Announcement

Collapse
No announcement yet.

SQL - Adding Column field information from other table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL - Adding Column field information from other table

    I am working for a pdf report and its sql builder.

    My sql requirement is to add the existing field records to table 1 from table 2 where they have common fields as employee name

    I initially tried the following sql builder
    SELECT
    Table1.employeename,
    Table1.sales
    Table2.commision
    FROM
    Table1,
    Table2


    The problem is that when I ran the pdf or sql reports it appears several names/records of the same employee causing the pdf report not to function due to large number of pages. It multiply the same employee information, where I expect that it will provide only 1 page per employee.


    Following are my requirement to appear or if I ran the sql, by adding existing column field information from 2 tables.


    Column1 Column2 Column3
    employeename (from table1) sales (from table1) commision(from table2)

    Please help

  • #2
    this is a sql issue: in general these commands go: select t1.field, t1.otherfield, t2.field, t2.otherfield from table1 t1, table2 t2 where t1. etc.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      My problem is that it multiply the single employee record in column more than 50x where I expect that it should appear only 1 employee record

      So how can I correct the sql builder as show below

      SELECT
      Table1.employeename,
      Table1.sales,
      Table2.commision
      FROM
      Table1,
      Table2

      Please help

      Comment


      • #4
        Originally posted by marc2014 View Post
        My problem is that it multiply the single employee record in column more than 50x where I expect that it should appear only 1 employee record

        So how can I correct the sql builder as show below

        SELECT
        Table1.employeename,
        Table1.sales,
        Table2.commision
        FROM
        Table1,
        Table2

        Please help
        That is because there's no 'where/join' clause and you have no matching key:

        SELECT
        Table1.employeename,
        Table1.sales,
        Table2.commision
        FROM
        Table1,
        Table2
        WHERE
        Table1.employeeid = Table2.employeeid

        you could add

        GROUP BY employeeid.

        Woud that be better?
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          Yes, it becomes better, it never multiply the information by this time for 50x times. The only things is it duplicate the same name and its information 2x where it should appear only 1 information

          See details below. Table 1 and Table 2 have the same common name John Doe

          Table1

          Employee Name Sales
          John Doe 10,000


          Table2

          Employee name Commission
          John Doe 1,000

          Result after the sql you suggested

          Employee Name Sales Commission
          John Doe 10,000 1,000
          John Doe 10,000 1,000

          It double the information where I need only a single information as per below

          Employee Name Sales Commission
          John Doe 10,000 1,000

          Please help again

          Comment


          • #6
            From sql point of view records should not be duplicated. So if that is the case then I'm pretty sure that you have double data in your tables.
            Albert Drent
            aducom software netherlands
            scriptcase partner, reseller, support and (turn-key) development
            www.scriptcase.eu / www.scriptcase.nl

            Comment


            • #7
              After checking there is no double data in the table, the only similar information from both tables is the employee name.

              See the other sample result below



              Capture.jpg

              Comment


              • #8
                Originally posted by marc2014 View Post
                After checking there is no double data in the table, the only similar information from both tables is the employee name.
                See the other sample result below
                [/ATTACH]
                Whilst I am inclined to agree with the other replies that there is an underlying data table issue, you could nevertheless try

                SELECT DISTINCT etc.. If you are using MS-Access I think it is SELECT DISTINCTROW. In any case, using DISTINCT can often cough up the reason for unexpected results when you are using joined tables in your SQL.

                Cheers
                The GuiGuy
                ... from Down Under

                Comment

                Working...
                X