DISTINCT values, two tables with overlapping values

[tictaclam]

hi,
i have two tables
Program Information:
ID Group ...
AAA 1
AA2 2
AA1 1

Environment:
ID DevEnvDescription TargetEnvDescription ...
AAA Some text here More text here
AA2 text in here some text
AA1 description description

The ID's in the two tables are the same. I want to find a distinct ID that belongs to group 2 and contains a certain word in DevEnvDescription or TargtEnvDescription. For this example say the word is "text". So i tried a query like this:

 SELECT DISTINCT ProgramID FROM [Environment] AS E, [Program Information] AS P WHERE E.ProgramID = P.ProgramID AND( DevEnvDescription LIKE '%text%' OR TargetEnvDescription LIKE '%text%' ) AND Group = 1

This gives me errors with the first ProgramID how can i fix this? Thanks

 

[JayKusch]

Maybe ...

SELECT DISTINCT e.ProgramID 
FROM Environment as e,
JOIN [Program Information] as p
ON (e.ProgramID = p.ProgramID)
WHERE E.ProgramID = P.ProgramID 
AND( DevEnvDescription LIKE '%text%' OR  TargetEnvDescription LIKE '%text%' ) 
AND Group = 1

Thanks

J. Kusch

 

[tictaclam]

That isn't working for me either. I am getting an error saying Syntax error in From clause.
I played around with it to see if i could modify to get it to work but it didn't seem to like it no matter what i did

 

[JayKusch]

Sorry ... kill the comma in the FROM statement ...

SELECT DISTINCT e.ProgramID 
FROM Environment as e
JOIN [Program Information] as p
ON (e.ProgramID = p.ProgramID)
WHERE E.ProgramID = P.ProgramID 
AND( DevEnvDescription LIKE '%text%' OR  TargetEnvDescription LIKE '%text%' ) 
AND Group = 1

Thanks

J. Kusch

 

[tictaclam]

Thanks but i still couldn't get that to work. I was however able to get it to work with

 SELECT DISTINCT E.ProgramID 
FROM [Environment] AS E, [Program Information] AS P 
WHERE E.ProgramID = P.ProgramID 
AND ( DevEnvDescription LIKE '%test%' OR TargetEnvDescription LIKE '%test%' ) 
AND Group = 2

thanks

 

[JayKusch]

Your code is not ANSI Standard but it works and thats what counts. I see where mine went astray - LOL

SELECT DISTINCT e.ProgramID 
FROM Environment as e
JOIN [Program Information] as p
ON (e.ProgramID = p.ProgramID)
WHERE( DevEnvDescription LIKE '%text%' OR  TargetEnvDescription LIKE '%text%' ) 
AND Group = 1

Thanks

J. Kusch