Help with select statement with one to many relationship

Question

what is the best way to write a select statement with two table that have a one to many relation ship?

I have a table called broker with these fields

id companyname

then another table called brokerContact that has a foreign key to the broker table with these fields

id brokerid contact name phone

How can I write a select statement that will get all the records from the brokertable as well as all of the brokercontacts for each brokerid, Without selecting all the brokers in my C# code then enuerating over them to get the brokerContacts for each, or is this the only way?

If that doesn't make sense or more clarification is needed please let me know. Thank you

also, this will be in a stored procedure

Answer 1

To get all the records from the Broker table, along with all the BrokerContacts, you could use an INNER JOIN:

SELECT B.ID
        ,B.companyname  
        ,BC.ID
        ,BC.contact
        ,BC.[name],
        ,BC.phone  
FROM Broker AS B
INNER JOIN BrokerContact AS BC ON BC.BrokerID = B.ID
ORDER BY B.companyname

If you have multiple contacts, you'll see one row for each contact, with the companyname repeated.