SQL - Adding Column field information from other table

Applications SQL Builder
#1

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.

#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

#4

[QUOTE=marc2014;24906]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[/QUOTE]

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?

#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

#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.

#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

#8

[QUOTE=marc2014;24944]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][/QUOTE]

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