query showing duplicate data

[dan-gauci]

I am having problems displaying data in a report that is being populated by a query.


Here are my tables:



Table1: Company_Details
Company_Id
Company_Name
...

Table 2: Shareholder
Shareholder_Id
Company_id
Company_Representative_Id
...

Table 3: Director
Director_Id
Company_id
Company_Representative_Id

Table 4: Secretary
Secretary_Id
Company_id
Company_Representative_Id
...
Table 5: Company_Representative
Company_Representative_Id
Name
...



This is the outcome I would like to achive (in the report):



Company Name; Shareholder Name, Director Name; Secretary Name
ABC Ltd ; Share1; Director 1; Sec 1
Share 2; Director 2;
Share 3



My query (below) is showing duplicate and non required data...



SELECT DISTINCT cd.company_name, sh.shareholder_id, crsh.name_surname as Shareholder, crd.name_surname AS Director, crs.name_surname AS Secretary
FROM client_detail AS cd, shareholder AS sh, company_representative AS crsh, company_representative AS crd, director AS di, company_representative AS crs, secretary AS se
WHERE cd.company_id = sh.company_id
AND sh.company_representative_id = crsh.company_representative_id
AND crsh.name_surname IN (SELECT name_surname FROM company_representative)
AND cd.company_id = di.company_id
AND di.company_representative_id = crd.company_representative_id
AND crd.name_surname IN (SELECT DISTINCT name_surname FROM company_representative)
AND cd.company_id = se.company_id
AND se.company_representative_id = crs.company_representative_id
AND crs.name_surname IN (SELECT DISTINCT name_surname FROM company_representative)



any ideas of how I can optimise this query to get the desired results please?



Thanks,

[NoellaG]

Hi,

first of all I would not create a new table for each function but a table with function descriptions:

tlkpFunctions
-----------------
*functionID (LONG) = PK
*functionDescription (TEXT)

and

tblContacts
-------------
*conID (LONG)
*conName (TEXT)
*conFirstName (TEXT)
*conCompany (LONG) FK to Company_Details.Company_Id
*conFunction (LONG) FK to tlkpFunctions.functionID

This of course if 1 person can only have 1 function in 1company.

You'll find querying a lot easier with a good normalized structure.

greetings
NG

[DMJ]

Have you set your primary keys? Have you set the fields so "no duplicates" are allowed, setting the Indexed field to Yes?