Advice on Simple Structured Sql Query (Search)

Hi All,
I have created a well structured cross relationship db.. To extract all
the details out is not a problem but i am trying to now include a
simple search to filter results.. Where is the best place to build the
query, within my .Net web app or within a sp itself..
My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
(JobID, Title, Keywords) and then another table that lists the
locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
What i am then trying to do is include a keyword search and a location
search together, i need to make it boolean as well and split keywords
up and then search within these to make sure they are in the
locations.. In my head it seemed fairly approchable but cant seem to
grasp it and cant find any decent tutorials.. Could you please point me
in the right direction: Do i need to be using dynamic sql', Should i
be doing most of the work within .net
A typical search would be for:
Keywords: IT,Graduate
Within Location: 12,35,48
I can do one or the other but not combined.. I also dont know how to
split up the keywords to make them two seperate keywords..
If anyone can point me in the right direction as this is my last
hurdle.. On this project at least :)Have a look at
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ponyoo" <anthonykallay@.hotmail.com> wrote in message
news:1133857887.366250.221060@.g14g2000cwa.googlegroups.com...
> Hi All,
> I have created a well structured cross relationship db.. To extract all
> the details out is not a problem but i am trying to now include a
> simple search to filter results.. Where is the best place to build the
> query, within my .Net web app or within a sp itself..
> My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
> (JobID, Title, Keywords) and then another table that lists the
> locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
> What i am then trying to do is include a keyword search and a location
> search together, i need to make it boolean as well and split keywords
> up and then search within these to make sure they are in the
> locations.. In my head it seemed fairly approchable but cant seem to
> grasp it and cant find any decent tutorials.. Could you please point me
> in the right direction: Do i need to be using dynamic sql', Should i
> be doing most of the work within .net
> A typical search would be for:
> Keywords: IT,Graduate
> Within Location: 12,35,48
> I can do one or the other but not combined.. I also dont know how to
> split up the keywords to make them two seperate keywords..
> If anyone can point me in the right direction as this is my last
> hurdle.. On this project at least :)
>

AdventureWorksDW

Hi everybody,

I'm searching for a full description of AdventureWorksDW and also its relationship with AdventureWorks database.

Tanx

Hello. I have not found a full documentation but perhaps Books On Line can tell you something.

A short description is that AdventureWorks is the OLTP-version(read transaction system version) of a fictive company that sells bicycles and related equipment on the internet.

AdventureWorksDW is the data warehouse structure of this company's business that include several starschemas /data marts. This structure is for analysis, not for entering transactions. After installing tools and samples, that is part of the SQL Server 2005 installation, you will also find an Aventure Works cube project built on top of the AdventureWorksDW database.

HTH

Thomas Ivarsson

AdventureWorks DB question

Hi, I have been looking at the Adventure works DB diagram and I am confused about the relationship that employees, customers and individuals has with contact i.e. a 1-1, 1-many or many-many relationship. Also why is it broken down like that.

Thanks,

Nadim.

Hi Nadim,

AdventureWorksDB is designed to show a complex data schema in a nomalized structure. AdventureWorks is probably a bit overcomplex if you're just learning about normalization, but once you understand the concepts, it becomes more clear.

Note: If you are new to normalization, you should search for a few topics about it to get the basics. You might also find the Northwind or Pubs sample databases to be a bit more understandable. You can download either from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en. They are SQL 2000 samples, but they will still work in 2005.

On to the description...

If you look at the fields in each of the tables, you'll see that the Contact table contains the base information about a person, e.g. first name, last name, phone, etc. The Contact table is linked to both the Employee and Individual tables. Both Employees and Individuals are types of people that have special properties in addition to the base person information stored in the Contact table. So an Employee has a ManagerID and LoginID, but an Individual doesn't. Because of the relationship between Contact and Employee, there is no need to store the first name, last name, etc. in the Employee table, it is retrieved through a join.

If I wanted to get a list of Employee names, with Titles and Hire Dates, I'd need to write a query that uses both the Contact and Employee table, as follows:

SELECT C.FirstName, C.LastName, E.Title, E.HireDate
FROM Person.Contact C INNER JOIN HumanResources.Employee E
ON C.ContactID = E.ContactID

The relationship with Individual is similar, only it extends through Individual onto Customer. Each Customer has an Individual associated with it, and that Individual, is a person who has Contact information. I'll be honest, I don't really know why they split Customer and Individual, it could have something to do with the way they wanted to use the data being stored.

Hopefully that brings a little light to the subject.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!