I am having trouble with the below query. This is attached to a SQLDataAdapter which in turn is connected to a grid view.
@.pram5 is a dropdownlist
all other perameters such as @.nw in the big OR statement are check boxes.
My tables look similar to this:
Company TblComodity TblRegion
---- ----- -----
PK CompanyID PK CommodityID PK RegionID
CompanyName FK CompanyID FK CompanyID
CommodityName North
South, East, etc
What I would am trying to do is have a user slect a commodity which is a distinct value from the comodity table. Then select by tick boxes locations, then in the grid view companies with possible locations and commoditys appear (matching record for commodity name and True values for any one particular location). My problem is even when I select a commodity and leave all tick boxes blank (false) the records still display for the selected commodity- like its only filltering on commodity name. Can anyone help ? I can provide more info if needed
Another little example of the above in case you dont understand.
Say in the dopdown list you choose "Buildings" and out of all the check box values you only choose Scotland, and North the record should still be returned if North is False and Scotland is True.
Here is my query:
SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.NorthWest, TblRegion.NorthEast, TblRegion.SouthEast, TblRegion.SouthWest,
TblRegion.Scotland, TblRegion.Wales, TblRegion.Midlands, TblRegion.UKNational, TblRegion.EuropOotherThanUK, TblComodity.ComName
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID INNER JOIN
TblComodity ON TblCompany.CompanyID = TblComodity.CompanyID AND TblComodity.ComName = @.pram5
WHERE (TblRegion.NorthWest = @.nw) OR
(TblRegion.NorthEast = @.NE) OR
(TblRegion.SouthEast = @.se) OR
(TblRegion.SouthWest = @.sw) OR
(TblRegion.Scotland = @.scot) OR
(TblRegion.Wales = @.wal) OR
(TblRegion.Midlands = @.mid) OR
(TblRegion.EuropOotherThanUK = @.EU) AND (TblRegion.UKNational = @.UKN)
INNER JOIN evaluates if only the condition on both the tables is matched. In your case, you need to use OUTER JOIN (LEFT OR RIGHT) so that the records from left/right table are fetched even if the condition in right/left table fails. In your query change the INNER JOIN to LEFT OUTER JOIN
Thanks
|||Thank you, I am new to this and use the query designer as I am learning. What you have said has taught me somthing new, We it be possible to modify my query to include your suggestion of the Left Outer Join?
Many thanks,
Adam.
|||I re-read your question and looks like you have nothing to do with the OUTER JOINS. Try this
SELECT TblCompany.CompanyName, TblComodity.CommodityName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE TblComodity.CommodityName = @.pram5 AND TblRegion.NorthWest = @.nw
In the above query i used only one region. If you want to add more regions use AND
Thanks
|||
Hi,
Thats almost what I want but I need OR's for example if you have a Commodity Name called buildins and you select TRUE values for North East and South and the particular commodity only has a TRUE value for South I still would like the record returned. It would work similar to say a holiday web site where you would choose a country and say select multiple regions and all records for regions would be returned. Do you understand?
|||Did you try using the query i provided with OR with which you can get the desired results.
Thanks
|||Your code gives me all results for some strange reason if I make pram5 = 'buildings' and @.nw = 'true'
returns
Company Commodity Northwest
SELECT TblCompany.CompanyName, TblComodity.ComName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE (TblComodity.ComName = @.pram5) OR
(TblRegion.NorthWest = @.nw)
samirayes:
Actually the aspnet_regsql could be better: if it retrieve database information from 'master..sysdatabases' not just 'sysdatabases', we do not have to change the default database for the login used in the wizard.