Multi-condition join

I've tried several methods of trying to implement this query but to no avail. Any help would be greatly appreciated
What I'm trying to do:
I have a temp table i've built up with names, values and data types. I need to query this against a table which has an index title field, which matches to my temp table names column, and then the other data could be in one of four columns in the table, dependant on the indexes data type. Once I have a match on the correct indexname and datatype I can then compare their values.
Regards
Mike
[<b>edited by:</b> katali at 1:16 PM (GMT -8) on 24 Aug 2009]
A clearer version of what I want and some example SQL is noted a few comments below and I&#39;ve removed the spaghetti code which was here before for ease of reading.

Looks like you need a refresher on the CASE syntax ...
You can use a CASE construct only as an expression: however complex it is, in the end all it does is return a single value. It is not like the "Select Case" syntax you might know from programming languages like Visual Basic.
Also you have your JOINs and ONs split. For your own ease of use, put the ON immediately after the JOIN to which it applies. Looks like you may be coming from a different database engine, with different syntax (Access/Jet?).

Hi Adriaan,
Thanks for your reply. I understand what you're saying and I'm sure you're right. To be honest my code at the moment is a bit of a mishmash of ideas that didn't work out, so it probably doesn't make a lot of sense asis. However my coding aside, I describe my problem of what I'm trying to do in TSQL and obviously I'm not achieving what I want, so any constructive ideas or pointers on how to achieve what I'm looking for?
Regards
Mike

Hi Mike,
If you could start by cleaning up the code a bit, it will help you focus, and save us all a lot of headaches.

Hi,
Ok cleaned up SQL below and example data.
Keywords table
KeyNameKeyValueKeyDataType
PONumber123Text
POCreatormouText
PODate10/08/2009Date
POAmount1500.00Currency
Main Index Table (n indexes per document)
DocumentIndexIdDocumentIdDocumentTypeIndexIdCurrencyIndexNumericIndexDateIndexTextIndex
10DFFFEDA9-5DF2-44EC-BFD3-91807CDFE5B98NULLNULLNULL12345PO
11DFFFEDA9-5DF2-44EC-BFD3-91807CDFE5B99NULLNULL2009-07-05 00:00:00.000NULL
12DFFFEDA9-5DF2-44EC-BFD3-91807CDFE5B912NULLNULLNULLPO for software
13DFFFEDA9-5DF2-44EC-BFD3-91807CDFE5B9102300.04NULLNULLNULL
14DFFFEDA9-5DF2-44EC-BFD3-91807CDFE5B911NULLNULLNULLM.Mouse
15B81B2907-C9C7-461E-9739-515BE116CF068NULLNULLNULL12345PO
16B81B2907-C9C7-461E-9739-515BE116CF069NULLNULL2009-07-31 00:00:00.000NULL
17B81B2907-C9C7-461E-9739-515BE116CF0612NULLNULLNULLPO for software
18B81B2907-C9C7-461E-9739-515BE116CF06102300.04NULLNULLNULL
19B81B2907-C9C7-461E-9739-515BE116CF0611NULLNULLNULLM.Mouse
Example SQL below which works when I just check against the one text index, but how do I make it so I can check against each of these indexes (within a documentId) and if one index doesn't match return no matches and if all indexes match return the documentId as a match. I appreciate that each of my indexes for a document is in a different row and I can't change the way the DB is designed.SELECT
DISTINCT [Document].DocumentIdFROM
DocumentTypeIndex RIGHT OUTER JOINDocumentIndex
ON DocumentTypeIndex.DocumentTypeIndexID = DocumentIndex.DocumentTypeIndexId RIGHT OUTER JOIN[Document]
ON DocumentIndex.DocumentId = [Document].DocumentId,@Keywords KeywordsWHERE
([Document].DocumentTypeId = 3AND
[Document].Title LIKE
(
CASE WHEN (@DocumentTitle is not null) then'%' + @DocumentTitle + '%'
ELSE[Document].Title
END)
AND
DocumentIndex.TextIndex LIKE
(CASE Keywords.KeyDataType
WHEN 'Text' Then
CASE WHEN(Keywords.KeyValue is not null) Then
'%' + Keywords.KeyValue + '%'
ELSEDocumentIndex.TextIndex
END
END
)
)
Thanks in anticipation.
Regards
Mike

You have only provided us with the table design for two tables; DocumentIndex and Keywords
However, your query references four tables; DocumentIndex, Keywords, [Document] and DocumentTypeIndex
Without more detail about the structure and relevance of the other two tables, it is difficult to know what you are trying to do.
There also appears to be a problem with having different date formats in two tables.
The DocumentIndex table shows a date example of '2009-07-05 00:00:00.000'.
The Keywords table shows a date example of '10/08/2009'.
You will not be able to directly match these date values if they are different data types and different formats.
I assume that your Keywords.KeyValue field is a text or varchar data type, but it seems as though some of the Index fields in the DocumentIndex table are not text or varchar.
Your query will need to convert the data values to the same data types before matching.
Take a look at the query below. Based on the limited information you have provided, this is my guess at a query that might help:
SELECT DISTINCT t1.DocumentId,t1.Title
FROM Document t1
INNER JOIN DocumentIndex t2 ON t1.DocumentId = t2.DocumentId AND t1.DocumentTypeId = 3
INNER JOIN Keywords t3 ON convert(varchar(100),t2.CurrencyIndex) = t3.KeyValue AND t3.KeyName = 'POAmount'
INNER JOIN Keywords t4 ON convert(varchar,t2.DateIndex,103) = t4.KeyValue AND t4.KeyName = 'PODate'
INNER JOIN Keywords t5 ON convert(varchar(100),t2.NumericIndex) = t5.KeyValue AND t5.KeyName = 'PONumber'
INNER JOIN Keywords t6 ON charindex(t6.KeyValue,t2.TextIndex)>0 AND t6.KeyName = 'POCreator'
I will explain a few things about the query above:
I have used only INNER joins (no LEFT or RIGHT) because you stated that the query must only return a result if ALL index fields match.
Do not be confused by the "t1, t2, t3..." names. These are just alias names for the tables. Alias names makes it possible to join onto the same table (the Keywords table) multiple times in one query.
I have used "convert(varchar" on some index fields in order to match all data values as text values.
I have used "convert(varchar,t2.DateIndex,103)" for the DateIndex field because this will convert "2009-07-05 00:00:00.000" to "05/07/2009" in order to match the date format in your Keywords table.
I have used CHARINDEX instead of LIKE for the TextIndex match because I believe it is more efficient and works just as well.

Hi David, Thanks for your reply, I appreciate your help.
I maybe need to expand on what I need to do, I was trying to keep it as simple as possible before. However I think I need to give a fuller explanation. I may be going about this completely the wrong way.
Ok, the keywords table is built up dynamically, because there will be different indexes required (and I don't know what these will be at design time), for each different document type I process. So I can't hard code an indexes name before hand. There are four potential index types, these being:

• Text
• Numeric
• Date
• Currency

So I get a string of delimited parameters in which I break down into rows to represent an index, and there are three columns which are:

• IndexName
• IndexValue
• IndexDataType

Example of temp keywords table below.
IndexName IndexValue IndexDataType
PONumber 541 Text
POCreator Mil Text
PODate 10/08/2009 Date
POAmount 1500.00 Currency
So I have a DocumentType which has specified indexes and their datatypes associated with it. I have many Documents for a DocumentType and potentially many Indexes per document.
What I want to do is check a couple of parameters against the document table (such as CreatedDate & DocumentTitle), so these are fixed parameters I know at design time and then also check the document indexes against keywords table below. One problem is that each index against a document is held in it's own row, so 1 document could have 5 rows of index information against it in a documentIndex table. I only want to output a document Id where all the indexes have matched on the keywords and the fixed parameters mentioned earlier. If a null parameter is passed in, that should be counted as a match.
Hope that makes some kind of sense. I may need to break it down into several steps. It could be potentially run against a large database, so performance is also key.
Thanks in advance
Mike