CODEWITHSUNDEEP
sqlteradata
o037
o037

Reputation: 137

Column foo not found tablename - Teradata

I'm using Teradata Express Studio with this query:

SELECT column1
FROM table1
INNER JOIN table2
ON table1.column2 = table2.column3
WHERE table2.column4 IN "foo";

I'm getting the following error:

Executed as Single statement.  Failed [5628 : HY000] Column foo not found in table2 or table1.

I am trying to search for an entry where column4's value is foo, but it seems to think that foo is the column name, can anyone assist me with this?

Upvotes: 2

Views: 2700

Answers (3)

calm
calm

Reputation: 400

The operator IN works comparing a value with an array of values it would be something like: 

WHERE table2.column4 IN ('foo')

But an array for just one value might be too much, so I think the best approach would be to use = operator

SELECT column1
FROM table1
WHERE table1.column2 IN 
     (SELECT table2.column3
      FROM table2 
      WHERE table2.column4 = 'foo');

Upvotes: 0

Eric
Eric

Reputation: 3257

You in bracket in IN

SELECT column1
FROM table1
INNER JOIN table2
ON table1.column2 = table2.column3
WHERE table2.column4 IN ("foo");

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Strings are delimited by single quotes in SQL and you want = rather than IN:

SELECT column1
FROM table1 INNER JOIN
     table2
     ON table1.column2 = table2.column3
WHERE table2.column4 = 'foo';

I might also suggest writing this as:

SELECT t1.column1
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.column2 = t2.column3 AND t2.column4 = 'foo');

This version will not return duplicates, if multiple rows match in table2. That is usually desirable.

Upvotes: 6

Related Questions