Complex Conditonal SQL Statement - how?

[jason.teen@gmail.com]

Hi,

I have a database as such:
-------------------------------------------------------------------------------------------------------------------
Brand | Model | UNT_RATE PACKAGE_IND Code
-------------------------------------------------------------------------------------------------------------------
ProductOne | X | 25.50 S
ABC123
ProductOne | Y | 82.10 L
DEFG789
ProductTwo | X | 12.00 S BBB667

ProductTwo | Y | 3.00 P
ASDF321
ProductTwo | Z | 8.20 S
ZZZ543


and now I want to say this in an SQL query:
"If for the same Brand, if there is a record with PACKAGE_IND = "P" or
PACKAGE_IND = "L" then DONT display any of the other records of that
same brand where Code is in this list ('ABC123', 'BBB667') and
PACKAGE_IND = "S"

So the output would be:
-------------------------------------------------------------------------------------------------------------------
Brand | Model | UNT_RATE PACKAGE_IND Code
-------------------------------------------------------------------------------------------------------------------
ProductOne | Y | 82.10 L
DEFG789
ProductTwo | Y | 3.00 P
ASDF321
ProductTwo | Z | 8.20 S
ZZZ543

So only two got eliminated:
ProductOne X - becuase ProductY package_ind is L, so productOne X got
cut cuase it was "S" and was in that list

Similarly, ProductTwo X - becuase ProductTwo-Y got "P" and so
ProductTwo X has S and is in list. Althought ProductTwo Z is in the
same condition, it wasn' t in the list, so its ignored.


I hope someone out there can help...
please please please!

[SurendranSNV via AccessMonster.com]

Re: Complex Conditonal SQL Statement - how?

Your question appears to be you want to get all records where 'Code' is not
equal to "ABC123" and "BBB67".
In that case the following SQL would produce the same result as shown in your
output.

SELECT tblName.*
FROM tblName
WHERE (((tblName.Code )<>"ABC123" And (tblName.Code)< >"BBB667"));

Am I correct ?

Surendran

jason.teen@gmai l.com wrote:

>Hi,
>
>I have a database as such:
>-------------------------------------------------------------------------------------------------------------------
>Brand | Model | UNT_RATE PACKAGE_IND Code
>-------------------------------------------------------------------------------------------------------------------
>ProductOne | X | 25.50 S
>ABC123
>ProductOne | Y | 82.10 L
>DEFG789
>ProductTwo | X | 12.00 S BBB667
>
>ProductTwo | Y | 3.00 P
>ASDF321
>ProductTwo | Z | 8.20 S
>ZZZ543
>
>and now I want to say this in an SQL query:
>"If for the same Brand, if there is a record with PACKAGE_IND = "P" or
>PACKAGE_IND = "L" then DONT display any of the other records of that
>same brand where Code is in this list ('ABC123', 'BBB667') and
>PACKAGE_IND = "S"
>
>So the output would be:
>-------------------------------------------------------------------------------------------------------------------
>Brand | Model | UNT_RATE PACKAGE_IND Code
>-------------------------------------------------------------------------------------------------------------------
>ProductOne | Y | 82.10 L
>DEFG789
>ProductTwo | Y | 3.00 P
>ASDF321
>ProductTwo | Z | 8.20 S
>ZZZ543
>
>So only two got eliminated:
>ProductOne X - becuase ProductY package_ind is L, so productOne X got
>cut cuase it was "S" and was in that list
>
>Similarly, ProductTwo X - becuase ProductTwo-Y got "P" and so
>ProductTwo X has S and is in list. Althought ProductTwo Z is in the
>same condition, it wasn' t in the list, so its ignored.
>
>I hope someone out there can help...
>please please please!

--
Message posted via http://www.accessmonster.com

[Terry Kreft]

Re: Complex Conditonal SQL Statement - how?

You don't say what the nameofyour table is so I've called it tblBrand in the
example below.

Something like:-

SELECT a.Brand, a.Model, a.UNT_RATE, a.PACKAGE_IND, a.Code
FROM tblBrand AS a LEFT JOIN (SELECT
Brand,
Model,
UNT_RATE,
PACKAGE_IND,
Code
FROM
tblBrand
WHERE
Brand IN(
SELECT Brand
FROM tblBrand
WHERE (((tblBrand.PAC KAGE_IND) In ('P','L')))
)
AND
PACKAGE_IND = 'S'
AND
Code IN ('ABC123', 'BBB667')
) AS b ON (a.Code = b.Code) AND (a.PACKAGE_IND = b.PACKAGE_IND) AND (a.Brand
= b.Brand)
WHERE (((b.Brand) Is Null));


--

Terry Kreft


<jason.teen@gma il.comwrote in message
news:1152508459 .090777.239800@ b28g2000cwb.goo glegroups.com.. .

Hi,
>
I have a database as such:
--------------------------------------------------------------------------

-----------------------------------------

Brand | Model | UNT_RATE PACKAGE_IND Code
--------------------------------------------------------------------------

-----------------------------------------

ProductOne | X | 25.50 S
ABC123
ProductOne | Y | 82.10 L
DEFG789
ProductTwo | X | 12.00 S BBB667
>
ProductTwo | Y | 3.00 P
ASDF321
ProductTwo | Z | 8.20 S
ZZZ543
>
>
and now I want to say this in an SQL query:
"If for the same Brand, if there is a record with PACKAGE_IND = "P" or
PACKAGE_IND = "L" then DONT display any of the other records of that
same brand where Code is in this list ('ABC123', 'BBB667') and
PACKAGE_IND = "S"
>
So the output would be:
--------------------------------------------------------------------------

-----------------------------------------

Brand | Model | UNT_RATE PACKAGE_IND Code
--------------------------------------------------------------------------

-----------------------------------------

ProductOne | Y | 82.10 L
DEFG789
ProductTwo | Y | 3.00 P
ASDF321
ProductTwo | Z | 8.20 S
ZZZ543
>
So only two got eliminated:
ProductOne X - becuase ProductY package_ind is L, so productOne X got
cut cuase it was "S" and was in that list
>
Similarly, ProductTwo X - becuase ProductTwo-Y got "P" and so
ProductTwo X has S and is in list. Althought ProductTwo Z is in the
same condition, it wasn' t in the list, so its ignored.
>
>
I hope someone out there can help...
please please please!
>