Complex Conditonal SQL Statement - how?

[jason.teen]

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]

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

ja********@gmail.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]

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.PACKAGE_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
<ja********@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.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!