Help with AND and OR query

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

sampleCleaningTruePLCBuildingsFalseVictoriaBuildingsTrueVictoriaCateringTrueVictoriaComputingTrueVictoriaBuildingsTrueyiyuBuildingsFalse

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)

sql

Help with "like" or "wildcard" during a parameter entry.

Disclaimer - I'm a novice when it comes to Crystal Reports as well as VB, and I am using Crystal Reports XI.

My trouble - I have a report that uses a parameter field to show a history of a whatever number is entered into the parameter when the report is accessed. I would like to add a "like" or "wildcard" to this parameter filed.

Example:

Parameter entered = ED35A100

The results are correct I get a history of the number ED35A100

However, I would like to enter the parameter = ED35*

and get results like ED35A100, ED35C100, ED35A200, etc.

Any help on how to accomplish this would be greatly appreciated.maybe this will work...lookslike({Fieldname},"*{?Parameter}*") then go to Report...Select Expert then choose the Fieldname then in the drop down key choose Is True

Help w/syntax select in a while loop

I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:

'AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop'

Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:

Also I'm trying really hard not to use temp tables in this example

Result from select statement below

sh_serial
----
53565
53597

sh_serial
----
53565
53597

sh_serial
----

sh_serial
----
53588
53597

Desired results:

sh_serial
----
53588
53597
53565

Syntax:

declare @.start int
select @.start = 580
declare @.stop int
select @.stop = 900

while @.start <= @.stop
begin
select sh_serial,
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and (sh_serial <> 600000)
and sh_serial in (53565,53588,53597)
and sh_serial in

(select distinct sh_serial
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and sh_serial in (53565,53588,53597)
AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop
group by sh_serial
having (sum(schi_qty) + 1 < 4 ))

select @.start = @.start + 1
end

I'd appreciate any help. Thanks! :oInsert into a table variable and select distinct from that when you leave the loop?|||cause of business logic I cannot use temp tables or tables...

is there a way that I can put this into some sort of a derived table and then do the select distinct?|||I was gonna rewrite it...but I got scared...

SQL 2000?

DECLARE @.start int, @.stop int
SELECT @.start = 580, @.stop = 900

DECLARE @.x TABLE(shSerial int)

WHILE @.start <= @.stop
BEGIN

INSERT INTO @.x(shSerial)
SELECT sh_serial
FROM casemas, schilin
WHERE schi_shser = sh_serial
AND schi_itemno = '004852'
AND sh_serial <> 600000
AND sh_serial in (53565,53588,53597)
AND sh_serial in ( SELECT DISTINCT sh_serial
FROM casemas, schilin
WHERE (schi_shser = sh_serial)
AND (schi_itemno = '004852')
AND sh_serial in (53565,53588,53597)
AND @.start NOT IN ( SELECT sh_istart
FROM casemas
WHERE sh_istart in (SELECT sh_istop
FROM casemas
WHERE sh_serial in (53565,53588,53597)))
AND @.start between sh_istart and sh_istop
GROUP BY sh_serial
HAVING (sum(schi_qty) + 1 < 4 ))

SELECT @.start = @.start + 1
END

SELECT DISTINCT shSerial FROM @.x|||Thanks Brett,

and by all means feel free to change anything...I do hate the fact that I have to use this statement all the time

'AND sh_serial in (53565,53588,53597)'

and if anyone else has any ideas on how i can tweak this horrid statement...I'd appreciate it! Thanks! :)|||Store those values in a temp table (@. or #) and do WHERE EXISTS (...).

Help understanding stored procs

I am having trouble understanding how to correctly use stored procs. Here is what I think the process is. Correct me where I am wrong.

I am developing a read-only program with VB 2005 as a front end and SQL Server back end. The user will enter an ID# for search criteria which will be passed as a parameter to a stored proc. I want to create a stored proc that alters a view by changing the ID# the view filters on. A dataset will be created in the front end from that view.

So in SSMS, I create a new proc like this:

CREATE PROC {blah, blah}

@.IDnum

AS

BEGIN

ALTER VIEW {blah, blah}

AS

SELECT {blah, blah}

FROM {blah}

WHERE blah.ID = @.IDnum

END

I would run the code to create the proc, then modify it to an ALTER PROC. I've tried this without success. What am I missing. Yes, I am new to this.

Thanks for the help

Hi,

why do you want to alter a view, rather than using a filter on the view (although the view isn′t that column rich :-) )

So you could create the View as

CREATE VIEW SomeView
AS

SELECT col1, col2 From SomeTable

and then select using

SELECT * FROM SomeView Where Col1 = 'SomeId'

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

|||

Thanks for the reply. I am entering the world of SQL Server from an Access background. In Access I often change the filtering of a query by changing the sql with code or by referencing a field on a form from the criteria field of a query.

That is the thinking with which I have approached views. The purpose of my post is to check my thinking against the experienced community and hopefully get better ideas and understanding. Your reply helps. Thanks again.

|||You′re welcome :-)