Advanced Sarch

Thanks Phil,

So do I need to loop through every user and check if they have something selected for each category, and then based on that I include the condition?

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

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?

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

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

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/

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)

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/

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?

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/