Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mihamil
Starting Member
8 Posts |
 Posted - 2008-08-18 : 11:54:15
|
| I'm still really new at SQL and I can't figure out why this is happening. This query returns the correct results but returns them about 609 times over and over again. Select Offices.Responsible_Member,Offices.Office_Contact_Member, Offices.Office_Number, Offices.Office_Namefrom Office Offices Left Join Member_Association MAon Offices.Responsible_Member=MA.Member_Number or Offices.Office_Contact_Member=MA.Member_NumberLeft Outer Join Office_Association OAon MA.Association_ID=OA.Association_IDwhere Offices.Office_Number in ( select Actives.Office_Number from Office_Association Actives where Actives.Association_ID in ('STC') and (Actives.Status='A' or Actives.Status='S') )and MA.Status not in ('A','S')and MA.Association_ID in ('STC');Any help would be greatly appreciated |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-18 : 12:04:18
|
| What is the point of joining to Member_Association and Office_Association when you don't include any columns from those tables in your result set?Boycott Beijing Olympics 2008 |
 |
|
|
mihamil
Starting Member
8 Posts |
Posted - 2008-08-18 : 12:22:45
|
| I am trying to find active offices that have inactive Responsible Member/Office Contact Member (Can be two different people or same person). Before I did that I would get Active offices whos RM/OCM were active in the same association as the office but inactive in another association (two different rows in the table) so it would get returned as a result. Is there a better way to ensure that I am comparing the members status to only the row with the same Association as the office? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-18 : 12:36:51
|
| The only thing i guess that this could happen is because your join condition is not correct. Can i ask the meaning of or condition in join? |
|
|
|
mihamil
Starting Member
8 Posts |
Posted - 2008-08-18 : 13:30:13
|
| The first join is to get the status of the RM/OCM for each active office. The second is to make sure that the RM/OCM record that is being compared is for the same association as the office record. There are several different associations and the members and offices could have a record for each association (they would still have the same member/office number) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-18 : 13:47:14
|
| i was asking the maening of thison Offices.Responsible_Member=MA.Member_Number or Offices.Office_Contact_Member=MA.Member_Number?? |
|
|
|
mihamil
Starting Member
8 Posts |
Posted - 2008-08-18 : 17:35:48
|
| Thanks everybody for your help. I took a small break and then went back to look at it and nothing in the query made too much sense to me either. I guess I was looking at it too long. I ended up rewriting the entire query in about 15 minutes and it works perfectly. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-08-18 : 18:03:31
|
| You might want to post your table structure some sample data and desired results. If your new query that your wrote resembles your first one, it is probably doing a lot more work than necessary (Even if you are getting the correct results). |
|
|
|
|
|
|
Query returning results 609 times