Difficulties in Wirting A SQL Query

[victoria_1018]

Hi All,
I have some difficulties in writing a SQL Query for my ASP Query Page

This query will select information from two tables, namely WorksOrder and WorksOrderReport.

I want my result to display record in WorksOrder and records in WorksOrderReport provided StatusOfWork for one of the records in WorksOrderReport = ¡®Completed¡¯, otherwise, do not display any records from both tables.

Currently, I am usingINNER JOIN to write the SQL Query but it did not display the result I want.

My SQL Statement:

SELECT * FROM WorksOrder w WHERE w.WorksOrderNo = ¡®¡±&SearchWorksOrderNo&¡±%¡¯ INNER JOIN SELECT * FROM WorksOrderReport wor WHERE w.WorksOrderNo = wor.WorksOrderNo AND wor.StatusOfWork = ¡®Pending¡¯ AND StatusOfWork = ¡®RMA¡¯ AND StatusOfWork = ¡®Completed¡¯;
Does anyone know how to write it?

Regards
Victoria

[Kiwi]

It might be easier to do this with a simple where clause. It's semantically identical, but quite a bit shorter (and more efficient, in this case).

It also makes things a little nicer to use the AS to assign your table aliases (not essential, but easier to understand when you're having trouble).

SELECT * FROM WorksOrder AS w, WorksOrderReport AS wor
WHERE w.WorksOrderNo = "0001"
AND w.WorksOrderNo = wor.WorksOrderNo
AND wor.StatusOfWork = "Pending"
AND StatusOfWork = "RMA"
AND StatusOfWork = "Completed";