Advanced Sarch

[TiGGi]

Hi,

I would appreciate some help with a query...
I have a table with 3 rows companyid, categoryid and categoryType
This is basically bunch of different category ids in one row and each company gets a set of categoryIDs
Looks something like this
companyID CatID CatType
5225 10 LOC
5225 12 LOC
5225 150 TYPE
5225 214 DUR
So i need to make a search with different options to find companies that matches these options
My dilemma is how can I search same row for all these options
If I do find where (( catID = 12 or catID = 10) and (catType = 'LOC')) then I can't match the other category types since LOC type has already narrow down the results?????

[paross1]

Well, if you use AND, then each parameter will restrict the result set further. If you use OR, then you will return rows matching any of the parameters.

If you did this:

SELECT companyID,CatID,CatType
FROM your_table
WHERE CatType = 'LOC'
OR CatID = 150

then you would get these rows:
5225 10 LOC
5225 12 LOC
5225 150 TYPE

but this:

SELECT companyID,CatID,CatType
FROM your_table
WHERE CatType = 'LOC'
AND CatID = 150

would return nothing. So, what is it that you want to do? Do you want the results to be "narrowed down" for each parameter, or do you want them to be additive?

Phil

[TiGGi]

Thanx for the reply,

what I am trying to get is the companyID

and if I do something like

where ((catID = 10 or catID = 14) and (catType = 'LOC'))
and ((catid= 150 or catid= 151) and (catType = 'TYPE'))
and it's not working

my prob is since everything is in one row how do I cross match using the cattype.

[TiGGi]

Thought I had an answer but I don't

[Guest]

Your query is "and"ing all of your requirements together.

If I've counted my parentheses correctly, this statement should get the data you specified in your original post:

SELECT companyID
FROM your_table
WHERE ((catType = 'LOC') AND ((catID=10) OR (catID=14)) ) OR
((catType='TYPE') AND ((catID=150) OR (catID=151))) OR
((catType='DUR') AND (catID=214))

[TiGGi]

I've already tried that but that gives me full list, I need to be able to cross match between categories like,

select employerid where (catid = 1 and catType = 'loc' ) and (catID = 150 and catType = 'type')

[TiGGi]

here's the actual query that I have right now

select distinct employerid where ACTIVE = 1 AND ((( CATID = 1 or CATID = 3 or CATID = 17 ) AND (catTYPE = 'cat')) OR (( CATID = 1) AND (catTYPE = 'DUR')) OR (( CATID = 53 or CATID = 54 or CATID = 56 or CATID = 59 or CATID = 61 or CATID = 1589 or CATID = 1590 or CATID = 1591) AND (catTYPE = 'LOC')) )

this brings back a list of any of these categories which I don't want. I just want the ones that match all of these categories

[Dan_Bracuk]

What is the relationship between catid and cattype?

[Guest]

I've rearranged the order of the criteria to make it more readable.
"condition 1" selects cattype='cat' and catid=1,3 or 17
"condition 2" selects cattype='DUR' and catid=1
"condition 3" selects cattype='LOC' and catid=53,54,56,59,61,1589 or 1590
"condition 4" is obvious.

ANY row matching "condition 1" OR "condition 2" OR "condition 3" that is active fulfills the criteria. If you really want employerid's that match ALL of these criteria, simply change the "OR ((catTYPE=" TO "AND ((catTYPE=" and leave the other "OR" statements in place.

SELECT DISTINCT employerid
FROM table_name
WHERE

condition 1:
((catTYPE='cat') AND ((CATID=1) OR (CATID=3) OR (CATID=17)))

condition 2:
OR ((catTYPE='DUR') AND (CATID=1))

condition 3:
OR ((catTYPE='LOC') AND ((CATID=53) OR (CATID=54) OR (CATID=56) OR (CATID=59) OR (CATID=61) OR (CATID=1589) OR (CATID=1590) OR (CATID=1591)))

condition 4:
AND (ACTIVE=1)

[paross1]

quote:

---

I just want the ones that match all of these categories

---

I think the point is that there aren't any that match ALL of the categories without including the companyID in the "test". In other words, you want only those rows that match your specified conditions and have the same companyID, which you haven't specified in a WHERE clause.

How about something that looks like this?

SELECT DISTINCT x.companyID
FROM your_table x
WHERE x.ACTIVE = 1
AND EXISTS (SELECT 1
FROM your_table y
WHERE y.CATID IN (1, 3, 17)
AND y.catTYPE = 'cat'
AND y.companyID = x.companyID)
AND EXISTS (SELECT 1
FROM your_table y
WHERE y.CATID = 1
AND y.catTYPE = 'DUR'
AND y.companyID = x.companyID)
AND EXISTS (SELECT 1
FROM your_table y
WHERE y.CATID IN (53, 54, 56, 59, 61, 1589, 1590, 1591)
AND y.catTYPE = 'LOC'
AND y.companyID = x.companyID)

Each one of the EXISTS sub-selects tests your conditions in addition to ensuring that they must have the same companyID.

Phil

[TiGGi]

I think that will work great, thanks allot bro.

For the future reference so I make things easier, how should I format database so advanced search like this would be easier and faster????

[paross1]

I'm assuming that you are already normalized to 3rd normal form. This is one of those special cases where you have a very specific search critera that requires more demanding SQL than you normally would use.

Phil

[TiGGi]

but is there a better way to create the set of fields like I have in database so they can be searched easier.

Maybe create set of unique id for each category and placing the IDs of all categories into 1 field as comma delimited list?
so maybe I have
DUR ID's are 101, 102, 103
CAT ID's are 201, 202 ..
LOC ID's are 500 - 2000

and just have one field for each employers that wold have IDs to all categories that this employer matches
(101, 103, 205, 504, 795, ...)
????

[paross1]

quote:

---

.....placing the IDs of all categories into 1 field as comma delimited list?

---

Oh no, this is a very bad idea. From what you said, are you saying that category is not a separate table with unique CatID values? If so, then your database is NOT in 3rd normal form. You should migrate your categories to a category table, and if there is a many to many relationship between company and category, you should resolve it with a associative entity (link table) between category and company. In other words if a company can be inked to more than one category, and a category can be linked to more than one company, then you should have a table between the two that contains companyID and catID pairs where they link. Then your query should be a snap.

Phil

[TiGGi]

ty again for posting....

well let me start from the beginning...
my employer table has lots of columns and among them i got columns:
employer_loc, employer_cat, employer_dur which have comma delimited list of ID's that correspond to other tables such as Location, Category.
In the process of entering data for employer employer_cat table gets populated also, and this is the table that we were doing the search from.

does this makes sense?

[paross1]

The statement "...employer_dur which have comma delimited list of ID's..." is a clue to me that you have an improperly normalized data model which may be at the root of most of your troubles. Quite often it is necessary to perform wildly unusual SQL work arounds in order to return desired results when a properly normalized database would vastly simplify the process. If you don't know what normalization is, then I suggest that you do a little research on what you would need to do to normalize your database.

Couple of places you might want to browse:
Relational Databases 101
Introduction to Data Modeling

Phil

[Newsgroup_User]

TiGGi,
iirc, in at least 3 of your recent threads you have been advised by
numerous people, many of who happen to be experts in the field, to
normalize your database.

just that would make anyone else stop for a second and think carefully
about what they are doing. it's not just pushing air around - there is a
very good reason for this.

but you have been ignoring this advice, instead just repeating your
question about your "comma-delimited list of values in my field" and how
you are having trouble doing this and that with it.
stop. do not make your life any harder. take the time to
a) learn about data modelling and 3rd normal form
b) implement it in your database

there is a book called 'database design for mere mortals' or something
similar to that. get it. read it. keep it next to you at all times until
you know it by heart.

paross1 has given you good instructions on how to arrange your data so
that it conforms to the 3rd normal form. do it.

if you do not, you will find yourself wasting your time trying to do
something that would take just a minute to do in a normalized database.

normalize your db. it's not an option. it's a must.

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[TiGGi]

paross1, took your advice and I normalized the dB, I think. I took all those different categories that had comma delimited lists in it and made it's own table which has CompanyID and then the id of category.
So now how do I optimize my query to work with this new setup?

[Newsgroup_User]

can you please re-post you current query and explain again what output
you want? there are a lot of bits and pieces of code in this thread -
it's hard to put together what you might have now...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[TiGGi]

What I am trying to acomplish is have a user search where user can pick different categories such as CAT, LOC, DUR.. and each of these categories have multiple selections. User save its search and his selections go into tables that have been normalized now. So I have a user table then I have a table for each category with userID and then the value of the each selection. On the other side I have the employer table which has related tables for it's set of categories.

What I need is to take a user saved selections and find employers that match those selections.

Right now what I have is pretty much what paross suggested
SELECT DISTINCT x.companyID
FROM your_table x
WHERE x.ACTIVE = 1
AND EXISTS (SELECT 1
FROM your_table y
WHERE y.CATID IN (1, 3, 17)
AND y.catTYPE = 'cat'
AND y.companyID = x.companyID)
AND EXISTS (SELECT 1
FROM your_table y
WHERE y.CATID = 1
AND y.catTYPE = 'DUR'
AND y.companyID = x.companyID)
AND EXISTS (SELECT 1
FROM your_table y
WHERE y.CATID IN (53, 54, 56, 59, 61, 1589, 1590, 1591)
AND y.catTYPE = 'LOC'
AND y.companyID = x.companyID)

[Newsgroup_User]

from your query sample it still looks to me like you database is not
normalized... can you post your table structure for al tables involved
in your query?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Guest]

Yeah like Azadi says...only if you could just post your entire table structure...we could possibly understand what you have...so that we could help you out here.

sadb,
http://RapidshareFilms.com

[TiGGi]

The query that I posted is the one I used before the normalizing tables. Now that the tables are normalized I need help with the new query.

I don't know if there's an easy way to get tables structure but this is the basic layout:

[dbo].[Users]
UserID(int), UserMail,UserPassword....

[dbo].[User_LOC]
UserID(int), LOC(int)

[dbo].[User_CAT]
UserID(int), CAT(int)

[dbo].[User_DUR]
UserID(int), DUR(int)

Then I have the employer tables:

[dbo].[employer]
employerID(int), ....

[dbo].[employer_LOC]
employerID(int), LOC(int)

[dbo].[employer_CAT]
employerID(int), CAT(int)

[dbo].[employer_DUR]
employerID(int), DUR(int)


so for each user I need to take the values in User_LOC, User_CAT, User_DUR and find a match in the employer_LOC, employer_CAT, employer_DUR and return the employerID as result

[paross1]

Given your current structure, what does this query give you?

SELECT DISTINCT e.employerID, u.UserID
FROM employer e
INNER JOIN employer_CAT ec ON e.employerID = ec.employerID
INNER JOIN User_CAT uc ON ec.CAT = uc.CAT
INNER JOIN employer_LOC el ON e.employerID = el.employerID
INNER JOIN User_LOC ul ON el.LOC = ul.LOC
INNER JOIN employer_DUR ed ON e.employerID = ed.employerID
INNER JOIN User_DUR ud ON ed.DUR = ud.DUR
INNER JOIN Users u ON uc.UserID = u.UserID
AND ul.UserID = u.UserID
AND ud.UserID = u.UserID

NOTE: You would design your WHERE clause to specify the particular combinations of values for CAT, LOC, and DUR if necessary.

Phil

[TiGGi]

Hey paross, thanks for your help again...
in the SELECT DISTINCT e.employerID, u.UserID
FROM employer e
don't I need the USER table in FROM also?