SQL Select question

[Guest]

Hello,

My apologies if I have selected the wrong discussion group.
I need some help coding a Select query on 2 tables. Details are:

Table Definitions:
tblCompany:
CompanyID Autonumber Primary Key
CompanyName Text

tblJobTitles
TitleID AutoNumber Primary Key
TitleName Text

There are only 2 companies in tblCompany (company1 and company2)
There are approx. 15 seperate job titles. Company 1 has 2 titles. Company
2 has the remaining job titles.

I need a query that will return all jobs, Grouped by company.
Optionally, within each group, the job titles should be sorted.
It should look like this:

Company 1 Title 1
Company 1 Title 2

Company 2 Title 3
Company 2 Title 4
Company 2 Title 5
Company 2 Title 6
..
..
..

Company 2 Title 15


Right now, my query is:

Select tblJobTitles.TitleName, tblCompany.CompanyName
FROM tblJobTitles.TitleName, tblCompany
WHERE tblCompany.CompanyName = "Company1"
UNION
Select tblJobTitles.TitleName, tblCompany.CompanyName
FROM tblJobTitles.TitleName, tblCompany
WHERE tblCompany.CompanyName = "Company2"


This returns a recordset where each job title is listed twice, once for each
company. The set is sorted on the job title. Also, each job title appears
for each company, which is not correct.

Results: (These are made up titles) (oops, Company is on left, job title on
right)
Job Title Company
---------- ----------
Mfg. Mgr Company 1
Mfg. Mgr Company 2
VP. Marketing Company 1
VP. Marketing Company 2
Dir. of Prod Company 1
Dir. of Prod Company 2
IS Mgr. Company 1
IS Mgr. Company 2
HR Dir. Company 1
HR Dir. Company 2
Intern Company 1
Intern Company 1

The problem is that Company 1 has only the following 2 job titles:
Mfg. Mgr
Intern

Can anyone help me fix my query?

TIA,

Rich

 

[Sylvain Lafontaine]

This is definitively not the right newsgroup because we are talking here
about problems between SQL-Server and ADP. You should ask this question in
the m.p.a.queries or the m.p.a.tablesdbdesign newsgroups.

In your case, you need to add the CompanyID foreign key to the tblJobTitles
table (by the way, it won't be a bad idea to forget about using this tbl___
prefix notation) and use an INNER JOIN junction instead of a CROSS JOIN:

Select tblJobTitles.TitleName, tblCompany.CompanyName
From FROM tblJobTitles Inner Join tblCompany on tblJobTitles.CompanyID =
tblCompany.CompanyID
Where tblCompany.CompanyName = "Company1"
UNION ALL ...

Using aliases would be another good idea:

Select T.TitleName, C.CompanyName
From FROM tblJobTitles T Inner Join tblCompany C on T.CompanyID =
C.CompanyID
Where C.CompanyName = "Company1"
UNION ALL ...

and finally, remove the UNION ALL:

.... Where C.CompanyName = "Company1" or C.CompanyName = "Company2"

or :

.... Where C.CompanyName in ("Company1", "Company2")