Advanced Sarch

TiGGiAuthor

Inspiring

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?

Participating Frequently

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

TiGGiAuthor

Inspiring

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, ...)
????

Participating Frequently

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

TiGGiAuthor

Inspiring

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????

Participating Frequently

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

A

Anonymous

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)

Participating Frequently

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

D

Dan_Bracuk

Inspiring

What is the relationship between catid and cattype?

TiGGiAuthor

Inspiring

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

A

Anonymous

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))

TiGGiAuthor

Inspiring

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')

TiGGiAuthor

Inspiring

Thought I had an answer but I don't

TiGGiAuthor

Inspiring

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.