Two subqueries and a Union? 1

[Amesville]

Hi Folks

Ran into this problem working on an application I'm writing, not sure how to get around it...

SELECT CatalogID FROM Catalog cat
LEFT JOIN Town tn ON cat.RouteIntOnAt = tn.TownCallSign
WHERE cat.PrintLoadSide1 = 'Y'
AND tn.townStagedArea = @Par1 --Name of Staging area
AND Cat.SessionSelect = ''
AND Cat.CatComm IN (SELECT LrCommodity FROM LoadRestrict WHERE lrTrainType = @Par2)
UNION
SELECT CatalogID FROM Catalog
WHERE PrintLoadSide1 = 'N'
AND RouteVerso <> 'Start at Industry'
and SessionSelect = ''
AND Cat.CatComm IN (SELECT LrCommodity FROM LoadRestrict WHERE lrTrainType = @Par2);

I'm getting an error on the bold part above; apparently it doesn't like me using Cat.CatComm twice. But I'm fairly sure it has something to do with the subquery being repeated. Any suggestions on how I can make this work? Thanks!

Craig

 

[Amesville]

Oh Duh, never mind I just saw my stupid error. Sorry!

 

[Olaf Doschke]

Yes, too few cats. Look out for an old lady in your neighborhood, they often have too much of 'em.

Sorry, this was too tempting.

Bye, Olaf.

 

[Olaf Doschke]

Looking closer at this, you could also put all this in one WHERE without a union anyway, some conditions are valid for both queries, some other can be combined via OR:

SELECT CatalogID FROM Catalog cat
LEFT JOIN Town tn ON cat.RouteIntOnAt = tn.TownCallSign
WHERE Cat.SessionSelect = ''
  AND Cat.CatComm IN (SELECT LrCommodity FROM LoadRestrict WHERE lrTrainType = @Par2)
  AND (
       (cat.PrintLoadSide1 = 'Y' AND tn.townStagedArea = @Par1)
       OR
       (cat.PrintLoadSide1 = 'N' AND cat.RouteVerso <> 'Start at Industry')  
      );

Would that work, too?

Bye, Olaf.

 

[Amesville]

Hi Olaf

Yes, I think that will work better... Thanks!

Craig