SQL Server Performance

Multi-condition join

Discussion in 'SQL Server 2008 General Developer Questions' started by katali, Aug 24, 2009.

  1. katali New Member

    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.
  2. Adriaan New Member

    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?).
  3. katali New Member

    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
  4. Adriaan New Member

    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.
  5. katali New Member

    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
  6. davidfarr Member

    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.
  7. katali New Member

    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

Share This Page