Ok, normally I adore SQL. But this time I have to do something that it doesn't like. So I'm wondering if anyone has a um... *cleaner* way to do what I'm trying to do. First, an explanation. It's for a small (ie: low traffic) employment site; employers can post job listings with up to 3 categories, and users can post resumes with up to three categories. The problem comes when I try to *match* them because SQL won't let me say "if any of the resume categories match any of the listing categories" without long, drawn out OR statements. So here's what I have (ugh):



I could combine the three If's into a loop, but then I'll have to add a junk criteria to make the OR syntax work... ugh....

Anyone have a better idea? it's soooo ugly right now! And I have about 5 different versions of the same ugliness for different purposes (ie: notifying employers vs notifying seekers, and all their variations!).

I think I know how you could make this prettier. Try using the IN keyword. As in:



Applied to your problem:



You'll probably want some variation on this. Lemme know if that works.

K,

Even though you were dissing me yesterday I'll still help you...

What you want to do is use a nested SELECT (unless you are in MySQL, which would force you to use a second query and concatenate the categories together)

SELECT id, category, name FROM resume WHERE resume.category IN (SELECT category1, category2, category3 FROM job WHERE id = x)

this would allow you to specify which job req and search for any resumes that match the category. You could reverse the tables and allow the resume submitter to search for jobs that match his categories, but always build tools from the perspective of who is paying (which in most cases like this would be the prospective employer).

On further review this won't do exactly what you asked.

I think you need to either do some sort of complex matching code (there may be a function but I can't think of it imediately) or do two queries:

SELECT cat1, cat2, cat3 FROM job WHERE id = x
WHILE ...
str = cat1 + ', " + cat2 + ', " + cat3;

SELECT id, name FROM resume WHERE cat1 IN(str) OR cat2 IN(str) OR cat3 IN(str)

Looks like malkiri was writing at the same time as me... pretty much the same answer

Hoopster

Hehe...I win!

Well, that helped a lot. I still had to mess it up a bit to get rid of the 0's, but here's how it looks now:



Thanks for both of your help! Now the arduous task of converting all the variations... :P

put it in a function and you only have to edit it once...

Of course you'll have to go change all the places you call it one time but after that if you make changes it would be easy. You can even pass the table name to the function and that way you could possibly use the same function for searches by individuals and companies.

no, it actually has to be different each time...

for example, in one place I'm displaying a list of matches, in another I'm emailing to all the matches, etc. On one, I have more than three categories because I take all the matching categories for all the positions for each employer, so there could be any number of them. Different tables, different columns, etc. None of them match exactly, so it's not worth making a function or subroutine (If I did, I'd end up with half a million parameters... :)

Anyway, I have them all fixed up now and am pleased!

Glad to hear it!

You still could use a function to retrieve the data and use it one way (mail) in one place and another way (display) in another. Since you have 3 cat's in each all you would have needed to do is name the cat's the same thing and you only need to pass a couple variables.

Probably not worth the effort now but possibly something to consider in future table design.

In my head I see passing an id, a table name and a parameter or two to tell it what to return... i don't know your design though and in my head is a pretty scary place...

Hoopster