By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 1,873 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

What SQL query ?

P: n/a
PL
Hi,
I have 2 tables, Companies and Contacts. Each contact belongs to a company
in that way :

Table Companies :
- id (primary key)
- other informational fields

Table Contacts :
- id (primary key)
- other informational fields
- company_id (id of the company to which the contact belongs)

Here is what I want : a list of all the companies, with only the first
contact of each company (so, only one row per company). Is it possible to
catch this with a single SQL query ?
Example:

Companies:
1 Intel
2 Microsoft

Contacts
1 John bill, company_id=1
2 tom Bom, company_id=1
3 erik Jans, company_id=2
4 jon jon, company_id=2

Magic query result :
1 Intel John Bill
2 Microsoft erik Jans.

Thanks for any help.
P.
Aug 22 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
use a summary query and First or Last

SELECT tblCompany.CompanyName, First(Employee.Contact) AS
FirstOfContact
FROM tblCompany INNER JOIN Employee ON tblCompany.CompanyID =
Employee.CompanyID
GROUP BY tblCompany.CompanyName;

Aug 22 '06 #2

P: n/a
Try this:

Select First(b.Contacts) From Companies a Join Contacts b
On a.Company_ID = b.Company_ID

Here I am using the First Function and I am uisng alias for the table
names (like shorthand). Assuming the field/table names are the same as
your actual ones, you can paste the sql above into the sql view of a
query window and see if you get the desired results.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 22 '06 #3

P: n/a
I left out the group by part:

Select First(b.Contact) From companies a join Contacts b On a.Company_ID
= b.Company_ID
Group By b.Contact

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 22 '06 #4

P: n/a
PL
Select First(b.Contact) From companies a join Contacts b On a.Company_ID
= b.Company_ID
Group By b.Contact
Thanks you this works well, but if I try to have another selected field,
like:

Select First(b.Contact), a.Company_Name From companies a join Contacts b On
a.Company_ID= b.Company_ID Group By b.Contact

I got an error (3122), which explains that I used a request that doesn't
contain the specified expression a a part of the aggregate function.
How can I extract more fields (all the Company fields) from this query ?

Aug 23 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.