AND/OR problems...
-
[my apologies... reposted from incorrect forum]
This will probably be a bit longwinded, so bear with me.
[request]
I have been tasked with building a search interface (in ASP) that will allow our employees to search from a list of documents stored in our SQL database
[tables]
for the query I need help with, there are 6 tables involved:
Proposals (contains ProposalID, ProjectName, ProjectCode, ProposalTypeID, IndustryID, CustomerID)
Customers (contains CustomerID, CustomerName)
ProposalTypes (contains ProposalTypeID, ProposalTypeName)
ProposalFiles (contains FileID, ProposalID, File, Filename)
Keywords (contains KeywordID, Keyword)
ProposalKeywords (contains ProposalID, KeywordID)
Proposals is the "main" table, and each of the others are tied to it via an ID field.
[problem]
in an attempt to be more efficient, I have broken up some of the data (specifically Keywords), into a new table. previously, in the Proposals table, there was a column called Keywords. it housed information like 12, 23, 51 (representing the IDs of the Keywords associated with this proposal).
i didnt like that approach, so i created a ProposalKeywords table that contains an individual entry for every Proposal and the ID(s) associated with it.
now comes my problem. my users have to be able to search for Proposals based on Keywords (among other things), which wouldnt be a problem if it were a simple search (im currently using an IN to grab all keywords and proposals, etc).
the problem arrises when i need to allow AND vs. OR searches. i cant seem to find an elegant way to basically say "Give me all records where Keyword = 12 AND Keyword = 23"
this is the current query i have that will give me ALL the informtion i need, without any sort of WHERE filtering involved (it looks worse that it is
:------------------------------------------------------------
SELECT Customers.CustomerName, Proposals.ProjectCode, ProposalTypes.ProposalTypeName, ProposalFiles.FileID, ProposalFiles.Filename, Proposals.ProposalID, Keywords.KeywordID
FROM Keywords INNER JOIN ((ProposalTypes INNER JOIN ((Customers INNER JOIN Proposals ON Customers.CustomerID = Proposals.CustomerID) INNER JOIN ProposalFiles ON Proposals.ProposalID = ProposalFiles.ProposalID) ON ProposalTypes.ProposalTypeID = Proposals.ProposalTypeID) INNER JOIN ProposalKeywords ON Proposals.ProposalID = ProposalKeywords.ProposalID) ON Keywords.KeywordID = ProposalKeywords.KeywordID;
------------------------------------------------------------
is there -any- way i can use a variation of this query, AND provide a WHERE component that grabs a specific set of Keywords?
as i mentioned, i am using a version of the above with IN, but i need the query to be more flexible and allow for AND/OR variations on the KeywordID values.
just some additional info.
old Proposals table example (formatting will most likely be foobared
:ProposalID | CustomerID | IndustryID | Keywords
1 | 12 | 7 | 12, 23, 51
2 | 18 | 2 | 5, 19
new Proposals table example:
ProposalID | CustomerID | IndustryID
1 | 12 | 7
2 | 18 | 2
ProposalKeywords table
ProposalID | KeywordID
1 | 12
1 | 23
1 | 51
2 | 5
2 | 19
and i am trying to write a query that will allow for "Keyword = 12 AND Keyword = 19" (the OR version is basically handled with my IN piece)
oh, and in code, i have built a loop that will generate the "Keyword = XX AND Keyword = YY" piece... the problem is that i get no resultset.
if any additional clarification is needed, please let me know... thanks!
-
I've already answered your question here :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=186697
-
thanks Remi!
in case you hop back online, i've responded to your reply in that topic...
-
I know... but it's to make sure everybody else can find the rest of the thread
. Also makes sure that we don't have two identical threads going on at the same time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply