SQL and joins. Get too much results

Results 1 to 7 of 7

Thread: SQL and joins. Get too much results

  1. #1
    Imar Spaanjaars Guest

    Default SQL and joins. Get too much results

    This might be a stupid question, but hey.<BR><BR>I try to get some data from two tables: one with companies, the other with employees of that company. My query must retrieve the company-name of all companies that have an employee called &#039Smith&#039 All fine, but when I have a company that employs three persons called smith, the query returns three records.<BR>How do I limit the output to one. I already have tried various joins, and the DISTINCT statement, but nothing helps.<BR><BR>Any suggestions??<BR>Thanks in advance.<BR><BR>Imar<BR>

  2. #2
    Nathen Grass Guest

    Default RE: SQL and joins. Get too much results

    DISTINCT would probably work in this case. What was the SQL statement you tried with DISTINCT?

  3. #3
    Imar Spaanjaars Guest

    Default RE: SQL and joins. Get too much results

    Hello,<BR><BR>I&#039m using the following SQL statement:<BR><BR>SELECT DISTINCT <BR> Company.*, CompanyEmployee.ID, <BR> CompanyEmployee.re_first_name, <BR> CompanyEmployee.re_last_name, <BR> CompanyEmployee.re_initials, CompanyEmployee.re_title, <BR> CompanyEmployee.re_job_type, <BR> CompanyEmployee.re_home_address, <BR> CompanyEmployee.re_home_zip, <BR> CompanyEmployee.re_home_city, <BR> CompanyEmployee.re_home_country, <BR> CompanyEmployee.re_work_phone1<BR>FROM Company INNER JOIN<BR> CompanyEmployee ON <BR> Company.comp_id = CompanyEmployee.re_in_company<BR>WHERE (Company.comp_status = &#039A&#039) AND <BR> ((CompanyEmployee.re_first_name LIKE &#039%Imar%&#039) OR<BR> (CompanyEmployee.re_last_name LIKE &#039%Spaanjaars%&#039))<BR><BR>I should get two records, but I get three.<BR><BR>Is this enough for you?

  4. #4
    Join Date
    Dec 1969
    Posts
    29

    Default RE: SQL and joins. Get too much results

    Try This: Select * from employee inner join company on employee.companyid = company.companyid group by empname;<BR><BR>adjust field, table names accordingly of course... (using ms access syntax)


  5. #5
    Nathen Grass Guest

    Default RE: SQL and joins. Get too much results

    From the nature of your first post it sounded like all you needed to know was whether or not there was an employee with a certain name in a company. If this is the case then you SQL statement would look like:<BR><BR>SELECT DISTINCT Company.Name<BR>FROM Company<BR>INNER JOIN CompanyEmployee <BR>ON Company.comp_id = CompanyEmployee.re_in_company<BR>WHERE (Company.comp_status = &#039A&#039) AND <BR>((CompanyEmployee.re_first_name LIKE &#039%Imar%&#039) OR<BR>(CompanyEmployee.re_last_name LIKE &#039%Spaanjaars%&#039))<BR><BR>Your statement was bringing back too many records because it brings back each Distinct record taking into account all fields in your SELECT clause. So two people may have "Smith" as their last name but their first name may be different or their job type or their title and in this case both records will be brought back. I hope this helps you out some.

  6. #6
    Imar Spaanjaars Guest

    Default RE: SQL and joins. Get too much results

    Tried that, but I get an error saying that Company.Comp_id is invalid in the select list because it is not contained in either an aggregate function or the Group BY clause.<BR><BR>Aaaaaaaaaah ;-)

  7. #7
    Imar Spaanjaars Guest

    Default Ah I see

    Because I select fields from the Employee table as well, DISTINCT does not work, because there are no identical records to filter out. If I only select columns from the Company table, there will be duplicates which are filtered out.<BR><BR>This is it.<BR><BR>Thanks a lot.<BR><BR>Imar<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •