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 |
|
trouble2
Constraint Violating Yak Guru
267 Posts |
 Posted - 2009-04-08 : 07:44:26
|
| Yeah, right, I know this can be done, just not how.Let's say I have a table of persons, with name, lastname, etc.And another table with organisations, the person can belong to.So table 1:PersonID Name Lastname1 Brian Something2 Philip SomethingAnd table 2:OrgID OrgName PersonID1 BlaBla 12 MBI 1So if I would look for "BlaBla" or "MBI", Brian would come up as a result. I already got this far, but this would not work obviously....(@SearchTerm = '' OR [FirstName] LIKE '%' + @SearchTerm + '%' OR [Lastname] LIKE '%' + @SearchTerm + '%' OR (Select OrgName from Organisations as po where po.PersonID = PersonID) LIKE '%' + @SearchTerm + '%') |
|
|
matty
Posting Yak Master
161 Posts |
Posted - 2009-04-08 : 07:55:29
|
| SELECT NameFROM Persons p JOIN Organisations o ON p.PersonID = o.PersonID WHERE OrgName = @SearchTerm |
 |
|
|
trouble2
Constraint Violating Yak Guru
267 Posts |
Posted - 2009-04-08 : 07:57:55
|
| Yes, but this might return the record twice if I had Organisation 1: MicrosoftOrganisation 2: MacrosoftAnd I would search for "soft" |
|
|
|
trouble2
Constraint Violating Yak Guru
267 Posts |
Posted - 2009-04-08 : 08:02:20
|
| I thought if it would concatenate the organisations, it might be faster, so it would do something like"BlaBla, MBI" like @SearchTermIf you can understand....The secret to creativity is knowing how to hide your sources. (Einstein) |
|
|
|
trouble2
Constraint Violating Yak Guru
267 Posts |
Posted - 2009-04-08 : 08:17:46
|
| Got it now, I did it with a function called ConcatOrganisations so it now looks like:... OR (DBO.ConcatOrganisations(PersonID)) LIKE '%' + @SearchTerm + '%' ...The secret to creativity is knowing how to hide your sources. (Einstein) |
|
|
|
|
|
|
|
|
Search like through multiple tables & concatentate