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:
Code:
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:
Code:
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!
dBforums
>
Trouble with dynamic SQL SELECT query
07-22-11, 10:52
Trouble with dynamic SQL SELECT query
Linear Mode
