How not to display a row depending on other rows.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How not to display a row depending on other rows.

Author

Topic

Nosediver
Starting Member

7 Posts

Posted - 2007-10-08 : 04:19:55

Hello,
I am very new to SQL and ran into a problem using Access. I hope you can help me here.
The question is the following:
I have to tables:
Table 4x4: CommodityCode(string)/NeedofBU(high,low)
Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)

What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating.
How would you do that?
What I have so far is:

SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer
FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode
WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));

But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.

I would need somting like:
if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy

I really appreciate your help

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 04:38:13

Somthing like this I expect:


SELECT ...
FROM [4x4] AS F
WHERE     EXISTS
         (
              SELECT * 
              FROM ASDBComplete AS C 
              WHERE C.CommodityCode = F.CommodityCode 
                    AND C.Rating = 'red'
          )
      AND NOT EXISTS
          (
              SELECT * 
              FROM ASDBComplete AS C 
              WHERE C.CommodityCode = F.CommodityCode 
                    AND C.Rating NOT IN ('green', 'yellow')
          )
      AND NeedofBU='high'

Kristen

Nosediver
Starting Member

7 Posts

Posted - 2007-10-08 : 06:02:00

Hi Thanks for the answer. This seems to be the way. I tried to implement it. But I keep on getting an uspecific error message for the first 'Where Exists....' section. I tried some things but dont know where the problem lies.
I copied the code further down implementing the filenames I am using.
Actually I am not shure if I still need the "JOIN" line and dont know where to put the 'AS' correctly.
I hope its something obvious.

Lars

SELECT [4x4].[4DigitGCC], [4x4].[GLOBAL COMMODITY CODE DESCRIPTION (ENGLISH)], [4x4].SupplierCapabilitySEA, [4x4].[GLOBAL COMMODITY CODE DESCRIPTION (ENGLISH)], ASDBComplete.Name, ASDBComplete.Rating, ASDBComplete.Criteria
FROM [4x4] LEFT JOIN ASDBComplete ON [4x4].[4DigitGCC] = ASDBComplete.GCC AS F
WHERE EXISTS
(
SELECT *
FROM ASDBComplete AS C
WHERE C.GCC = F.4DigitGCC
AND C.Rating = 'red'
)
AND NOT EXISTS
(
SELECT *
FROM ASDBComplete AS C
WHERE C.GCC = F.4DigitGCC
AND C.Rating NOT IN ('green', 'yellow')
)
AND SupplierCapabilitySEA='high';]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 06:03:43

Which version and edition of MICROSOFT SQL SERVER are you using?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 06:07:11

[code]SELECT f.[4DigitGCC],
f.[GLOBAL COMMODITY CODE DESCRIPTION (ENGLISH)],
f.SupplierCapabilitySEA,
f.[GLOBAL COMMODITY CODE DESCRIPTION (ENGLISH)],
x.Name,
x.Rating,
x.Criteria
FROM [4x4] AS f
INNER JOIN ASDBComplete AS x ON x.GCC = f.[4DigitGCC]
LEFT JOIN ASDBComplete AS y ON y.GCC = f.[4DigitGCC]
AND y.Rating IN ('green', 'yellow')
WHERE x.Rating = 'red'
AND y.GCC IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

Nosediver
Starting Member

7 Posts

Posted - 2007-10-08 : 06:10:47

Hi,
I am not using SQL Server. I am just trying to get something done using MS Access. I think it is called JET SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-08 : 06:12:23

quote:
Originally posted by Nosediver

Hi,
I am not using SQL Server. I am just trying to get something done using MS Access. I think it is called JET SQL

Then you should post over at http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 08:08:23

Peso's JOINs approach may work better in Access, but will give multiple rows in the resultset if the JOINed data has multiple matched rows.

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 08:20:19

Now continued over at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90640

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources