November 6, 2008 at 10:04 am
I'm trying to pull data from several tables where a certain field in any one of those tables is like a string entered by the user. This is the query I'm using:
Select Table1.ID,Table1.FirstName,Table1.LastName,Table2.Answer,Table4.Title,Table5.PageHTML,Table6.Location
From Table1
Left Join Table2 On Table1.ID=Table2.AgentID
Left Join Table3 On Table1.ID=Table3.AgentID
Inner Join Table4 On Table3.SpecializationID=Table4.ID
Left Join Table5 On Table1.ID=Table5.AgentID
Left Join Table6 On Table1.ID=Table6.AgentID
Where Table1.TemporaryLeave=0 And
(
Table1.FirstName Like '%test%' Or
Table1.LastName Like '%test%' Or
Table2.Answer Like '%test%' Or
Table4.Title Like '%test%' Or
Table5.PageHTML Like '%test%' Or
Table6.Location Like '%test%'
)
The problem I am having is that every time one of the fields is like 'test', a seperate row is returned for all field-combination-variation. Each table (except for Table1) may have multiple rows for each agent. Does that even make sense the way I am describing it? I know that this is the way that joins are supposed to work, but I was hoping to find an alternative.
For example, if Table2.Answer is like 'test' for a particular agent, then I don't care about the other 5 rows where Table2.Answer is like 'test' but there are different values for Table4.Title for that same agent.
Is this sort of thing even possible without running multiple independent queries?
November 11, 2008 at 11:37 am
This may or may not work for you. NOTE carefully the ON part of each JOIN statement
Select Table1.ID, Table1.FirstName, Table1.LastName, Table2.Answer, Table4.Title, Table5.PageHTML, Table6.Location
From Table1
Left Join Table2
On Table1.ID=Table2.AgentID
and Table2.Answer Like '%test%'
Left Join Table3
On Table1.ID=Table3.AgentID
Inner Join Table4
On Table3.SpecializationID=Table4.ID
and Table4.Title Like '%test%'
Left Join Table5
On Table1.ID=Table5.AgentID
and Table5.PageHTML Like '%test%'
Left Join Table6
On Table1.ID=Table6.AgentID
and Table6.Location Like '%test%'
Where Table1.TemporaryLeave=0 And
(Table1.FirstName Like '%test%' Or Table1.LastName Like '%test%')
The secret to this code is that when you take a Left Outer or Right Outer JOIN and put a condition for the "outer" table in the WHERE clause, SQL Server treats the JOIN like an INNER JOIN instead of an Outer Join. To put conditions on the records returned from the outer tables, stick the conditions into the ON statement where it will only apply to that table's records and will not affect records coming from other tables.
Does this make sense?
November 12, 2008 at 5:34 am
Excellent! Thanks for the help.
November 12, 2008 at 5:37 am
NP. Glad I could help. @=)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply