Webmaster Forum / ColdFusion / Advanced Techniques / March 2008

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

[Q]I just want the ones that match all of these categories[/Q]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 [b][i]and[/b][/i] 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

I'm assuming that you are allready 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

[Q].....placing the IDs of all categories into 1 field as comma delimited
list?[/Q]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

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:
http://www.databasejournal.com/features/mssql/article.php/3640431 
http://www.utexas.edu/its-archive/windows/database/datamodeling/

Phil

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/

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/

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/

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

Phil

It is... it is INNER JOINed ...

....INNER JOIN Users u ON uc.UserID = u.UserID

Phil

Hey Phil, thank you again for your help.  Can I bother you once more?
I am gonna use the query you suggested last, I was wondering what happens if
user hasn't selected one of the categories?  Isn't this going to bring back
incorrect result?

[q][i]Originally posted by: [b][b]paross1[/b][/b][/i]
I suppose this equivalent would do the same thing.

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

Phil[/q]