Query help 2

[overDeveloper]

I have three tables

tbl_main
id officeID title
1 2 aaaaa
2 2 bbbbb
3 4 ccccc

tbl_offices
id groupID title
1 2 officeA
2 2 officeb
3 1 officec

tbl_group
id groupname
1 groupA
2 groupb
3 groupc

I need a query that will return, for example, all of the records from tbl_main where I am given a group ID. So if I am given a group id of 3 I want all of the records from tbl_main who have an office with a groupID of 3....

How would I go about this?

 

[gmmastros]

This is a very basic inner join. I encourage you to learn more about them.

Select tbl_main.*
From   tbl_main 
       Inner Join tbl_offices
         On tbl_main.officeID = tbl_offices.ID
Where  tbl_Offices.GroupId = 3

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[overDeveloper]

Thanks - I actually need to fit this into a larger query - how would I assimilate the join into something like:

Select * from tbl_main where field1 = 'xxx' and field3 = 'yyy'

would it be something like:

Select * from tbl_main where field1 = 'xxx' and field3 = 'yyy' and (Select tbl_main.*
From   tbl_main 
       Inner Join tbl_offices
         On tbl_main.officeID = tbl_offices.ID
Where  tbl_Offices.GroupId = 3)

 

[bborissov]

Basesd on George's example:

Select tbl_main.*
From   tbl_main
       Inner Join tbl_offices
         On tbl_main.officeID = tbl_offices.ID
Where  tbl_Offices.GroupId = 3     AND
       tbl_main.field1     = 'xxx' AND
       tbl_main.field3     = 'yyy'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP