Another SQL question

[cards4]

I have another question. The results I got from the query from earlier were correct but now I want to left outer join those results to another table. Here's my query:

(
select a.eid, a.tid, a.tc, a.ts
from et a
join (select eid, count(distinct tc) tcc
from et
where ts='?' and tid='STATE' and (ytw > 0 and ytow > 0)
group by eid
having count(distinct tc) > 1) b
on a.eid = b.eid
) y
left outer join (select eid, eno, fn, ln, mn from ep) x
on y.eid=x.eid

When I run this query, it tells me incorrect syntax near 'y' and incorrect syntax near 'x'. I'm new to SQL and I'm not sure why its giving me this error. Please help.

 

[SQLBill]

(
select a.eid, a.tid, a.tc, a.ts
from et a
join (select eid, count(distinct tc) tcc
from et
where ts='?' and tid='STATE' and (ytw > 0 and ytow > 0)
group by eid
having count(distinct tc) > 1) b
on a.eid = b.eid
) y

You have this part identified as a derived table. But you don't query anything from it. For a better understanding, replace that section of code with 'FROM Y'. It becomes the code below....does that work? No, there's no SELECT.

FROM y
left outer join (select eid, eno, fn, ln, mn from ep) x
on y.eid=x.eid

-SQLBill

Posting advice: FAQ481-4875

 

[cards4]

I do not understand your last post.

 

[SQLBill]

Look at the first code block. That section begins with a parenthesis "(" and ends with ")" y

Anytime you put a 'full' query (SELECT and FROM) within parenthesis and follow it with an 'alias', that makes it a derived table. Look up derived table in the BOL.

You must have:
SELECT .....
FROM .......

for a query to work. Because you make the initial SELECT a derived table, your query starts with just a FROM.

This is your query as you wrote it:

(
select a.eid, a.tid, a.tc, a.ts
from et a
join (select eid, count(distinct tc) tcc
from et
where ts='?' and tid='STATE' and (ytw > 0 and ytow > 0)
group by eid
having count(distinct tc) > 1) b
on a.eid = b.eid
) y
left outer join (select eid, eno, fn, ln, mn from ep) x
on y.eid=x.eid

This is how SQL Server SEES it:

FROM y
left outer join (select eid, eno, fn, ln, mn from ep) x
on y.eid=x.eid

Try this....it might work:

SELECT a.eid, a.tid, a,tc, a.ts
FROM
(
select a.eid, a.tid, a.tc, a.ts
from et a
join (select eid, count(distinct tc) tcc
from et
where ts='?' and tid='STATE' and (ytw > 0 and ytow > 0)
group by eid
having count(distinct tc) > 1) b
on a.eid = b.eid
) y
left outer join (select eid, eno, fn, ln, mn from ep) x
on y.eid=x.eid

-SQLBill



Posting advice: FAQ481-4875

 

[cards4]

I ran the query and it gave me a: "The column prefix 'a' does not match with a table name or alias name used in the query." It directs to the first line of the query. I tried y.eid, y.tid, y.tc, y.ts and it produced results but it didn't left join with the other table.

 

[SQLBill]

Sorry, I should have changed them to Y. Which table didn't appear to get joined? (There's two joins, which didn't work?)

-SQLBill

Posting advice: FAQ481-4875

 

[cards4]

The last left outer join did not go through. I got results from the first join only.

 

[cards4]

Never mind. It works. I forgot to add the second table columns in the very first select statement.