SQL Code Help... Making it harder than needs to be

brux2dc · May 29th, 2007, 06:20 PM

I have a table that has multiple rows with the same value in column 'x' with 'x' being the name of an item in this case. So 'x' might have 4 or 5 rows with 'y' and 'z' containing different data. Column 'z' is a true/false column and 'y' has different traits about 'x'. I want to have it so that when I select 'x' it only appears in the output when every 'x' row has a 'z' value of 'True'.

I know that the above is verbose. I'll paste what I have below with comments...

SELECT dbo.Drink.Title, dbo.Ingredient.Title AS Ingredient, dbo.Component.NumUnitsMin, dbo.Unit.UnitName, dbo.Ingredient.IsInMyBar
FROM dbo.Drink INNER JOIN
                      dbo.Component ON dbo.Drink.DrinkID = dbo.Component.DrinkID INNER JOIN
                      dbo.Ingredient ON dbo.Component.IngredID = dbo.Ingredient.IngredID INNER JOIN
                      dbo.Unit ON dbo.Component.UnitID = dbo.Unit.UnitID
WHERE IsInMyBar = 'True' //Trying to limit but oviously this is wrong. For every 'x' there should be no 'x' returned while 'z' = 'true'

Thanks, I've played for hours trying to write a proper where clause.

brux2dc:(

happygv · May 30th, 2007, 03:53 AM

You can always delete your own post that you feel is mis-posted.

_________________________
- Vijay G
Strive for Perfection

Jeff Moden · June 1st, 2007, 11:35 PM

Quote:

quote:Originally posted by happygv
You can always delete your own post that you feel is mis-posted.
________________________
- Vijay G
Strive for Perfection

Heh... great post... I'll bet the new guy is really impressed... much better than posting a solution

Brux...

Here's one way... you'll need to ****************-horn the "test" table into a CTE... I can't do that because I don't have 2005 to test with...

Code:

 SELECT t.* 
   FROM yourtable t,
        (
         SELECT X,COUNT(*) AS FullCount
           FROM yourtable
          GROUP BY X
        ) fc
        ,
        (
         SELECT X,COUNT(*) AS TrueCount
           FROM yourtable
          WHERE Z='T'
          GROUP BY X
        ) tc
  WHERE t.x = fc.x
    AND t.x = tc.x
    AND fc.FullCount = tc.TrueCount

Of course, you'll need to modify the code with the correct column names and table names...

--Jeff Moden

happygv · June 4th, 2007, 04:23 AM

Jeff,

I remember replying to his other post in SQL forum. Infact there he mentioned that he is not known how to move this post, thats why this reply. I think he will be really impressed as you said.

_________________________
- Vijay G
Strive for Perfection

Jeff Moden · June 4th, 2007, 07:08 AM

Ugh... I get it... thanks, Vijay...

--Jeff Moden

brux2dc · June 4th, 2007, 04:42 PM

Sorry I was out of town for the last few days. Thanks for the replies. Sorry about the cross-post. I'll give the syntax a try tonight when I settle in for the evening.

As far as being impressed... Anybody who can give advice, on things I don't know, impresses me. I came here to learn, and I did.

Thanks again,

Brux2dc