selection on many to many with uniqueness of one field

Marloes83

hi, first of all, sorry for my bad english!

i have 3 tables: companies, contacts and an inbetween table: compcont

i want to select some companies and i need only one contact for each company (but there are a lot more in the database)

what contact isn't important, the first one the script finds is ok for me.

how to i build this querry?
it's probably quite simple but i can't find it on the web or in my books.

the script i have so far is: (i just wrote it out of my head, probably with error's)

Expand|Select|Wrap|Line Numbers

 SELECT compName, street, number, city, firstName, lastName

FROM companies INNER JOIN compcont ON companies.companyNr = compcont.companyNr INNER JOIN contacts ON compcont.contactNr = contacts.contactNr

But this script generates of course multiple records for one company if it has more then one contact.

i hope you understand what i mean and can help me out, i've been hurting my poor little brains over this!

thanks in advance!
greetz
Marloes

mwasif

Use GROUP BY

Expand|Select|Wrap|Line Numbers

 SELECT compName, street, number, city, firstName, lastName

FROM companies INNER JOIN compcont ON companies.companyNr = compcont.companyNr INNER JOIN contacts ON compcont.contactNr = contacts.contactNr

GROUP BY companies.companyNr

selection on many to many with uniqueness of one field

re: selection on many to many with uniqueness of one field

/bytes/development

/bytes/it

/bytes/about