> The following code is in a stored procedure. Can
> somebody please just confirm if this is a valid SQL
> Statement. I appreciate this is not a SQL Forum. I'm a
> ColdFusion developer and am trying to query the db.
>
> SET @SQLStatement = @SQLStatement + 'SELECT * From
> Employee where Employee.asstetID IN (select distinct
> Employee.callNo from Employee where Employee.asst_id
> IN (select Assets.asst_id from Assets where bar_code =
> ' + char(39) + @asset + char(39) + '))'
I don't think it is valid because the use of "Employee" in the
predicate of the subquery is ambiguous. Further the DISTINCT is
superfluous. Try something like:
SELECT *
FROM Employee E1
WHERE E1.asstetID IN (
SELECT E2.callNo
FROM Employee E2
WHERE E2.asst_id IN (
SELECT Assets.asst_id
FROM Assets
WHERE bar_code =' + char(39) + @asset + char(39) + '
)
)
I have no idea about all the + and char() functions because that
obviously is not standard SQL and you didn't tell which DBMS you
are using. But I would flatten out the subqueries and rewrite the
whole thing as:
SELECT E1.*
FROM Employee E1 INNER JOIN Employee E2
ON E1.AsstetID = E2.callNo INNER JOIN Assets A
ON E2.asst_id = A.asst_id
WHERE A.bar_code =' + char(39) + @asset + char(39) + '
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
