Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2011
    Inside your mind

    Question Unanswered: Trouble with dynamic SQL SELECT query

    Hiya fella's,

    In a spot of bother... I have some dynamic SQL that I use for a fairly advanced search feature.

    It uses a triple join, which at the moment is INNER, but was also LEFT and gave the same results.

    The two tables involved are 'main' and 'ref_CompanyLocation', which are joined by the [Company] field.

    The SQL when the query is run for my problem looks like:

    SELECT main.[Order Date], main.[Order Number], main.[Company], ref_CompanyLocation.[Country], ref_CompanyLocation.[Area], main.[Production Date], ref_products.[Style], ref_products.[Base Colour], ref_products.[Trim Colour], ref_products.[Size], ref_products.[Type], main.[Serial Number] 
    FROM (main
        INNER JOIN ref_products ON main.[Product Code] = ref_products.[Product Code])
        INNER JOIN ref_CompanyLocation ON main.[Company] = ref_CompanyLocation.[Company] 
    WHERE 1 = 1 
    AND main.[Company] = 'aCompany1' 
    OR main.[Company] = 'aCompany2' 
    AND ref_CompanyLocation.[Country] = 'Brazil'
    And my 'ref_CompanyLocation' table at the moment looks like:

     Company  |    Country     |   Area
    aCompany1 | United Kingdom |    UK
    aCompany2 |     Brazil     | S.America
    Now in my results, for the above selection 'aCompany1', 'aCompany2' and 'Brazil', it will return all the values from 'aCompany1' and 'aCompany2', even those with 'United Kingdom' associated with it. - which is wrong

    If I select 'aCompany1' only, and 'Brazil' it finds no matches. - which is correct

    If I select 'aCompany2' only, and 'Brazil' it returns only 'aCompany2' records. - which is correct

    If I select 'Brazil' on its own, it returns only the results with 'aCompany2'. - which is correct

    MORE AMAZINGLY... If I select 'aCompany1', 'aCompany2' and 'United Kingdom', LEAVING 'BRAZIL' OUT OF THE SEARCH, it returns only the results with 'aCompany1' and 'United Kingdom'. - which is CORRECT

    This leads me to believe that my code is just prejudice against Brazilians, and as I don't wish to have racist code, can anyone shed some light as to why this might be happening?

    It just seems bazaar to me that it works fine with 'United Kingdom', but just not 'Brazil'.

    Any help would be greatly appreciated.

    UPDATE: After a little more fiddling, I'm pretty sure it's to do with the AND and OR statements being applied. I'll try using some brackets to clarify things and see if that makes a difference.

    UPDATE 2: Yup, sodding brackets. What a pain. Oh well... All is well once more!
    Last edited by kez1304; 07-22-11 at 11:05.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts