joining to a derived table 2

[chris123321]

Here is the query that I'm attempting to write:

SELECT NoteID,
  NoteName,
  SourceID,
  UseID
  (
    SELECT n.NoteID AS NoteID,
       n.NoteName AS NoteName,
       NULL AS SourceID,
       NULL AS UseID
    FROM tblNote n
      INNER JOIN tblProduct p
        ON n.ProductID_F = p.ProductID
    WHERE p.ProductID = @ProductID
   UNION
   SELECT NULL AS NoteID,
      NULL AS NoteName
      s.SourceID As SourceID,
      NULL AS UseID
   FROM tblSource s
     INNER JOIN tblProduct p
       ON s.ProductID_F = p.ProductID
   WHERE p.ProductID = @ProductID
   UNION
   SELECT NULL AS NoteID,
       NULL AS NoteName,
       NULL AS SourceID,
       u.UseID AS UseID
   FROM tblUse u
     INNER JOIN tblProduct p
       ON u.ProductID_F = p.ProductID
   WHERE p.ProductID = @ProductID) tblsuNote
  LEFT JOIN tblNote n
    ON tblsuNote.NoteID = n.NoteID
  ORDER BY n.Position, n.Order

Error is: ambigious column name NoteId, NoteName. I've been trying to narrow down which NoteId, NoteName the error by turning the NoteId to lower case, but even after turning all the NoteId(s) to lower case the error still is ambigious column name NoteId.

Any suggestions?

 

[Crowley16]

erm, that's not the correct syntax for joining a derived table, it's closer to a subquery, however subqueries can't be used that way...

try something like:

select
colNames...
from
tableA inner join
(
select cols
) as tableB on tableA.col = tableB.col

note, you have to alias your derived table...

--------------------
Procrastinate Now!

 

[gmmastros]

There are many problems with the query you posted. Generally speaking, though, you need to qualify which table (real or derived) from which to return the data from.

Ex:

[tt][blue]
Select [!]SomeAlias.[/!]NoteId,
[!]SomeAlias.[/!]NoteName,
SourceId,
UseId
From SomeTable
Inner Join
(
Select NoteId, NoteName
From SomeOtherTable
) As SomeAlias
On SomeTable.NoteId = SomeAlias.NoteId
[/blue][/tt]

Notice the derived table. It has an alias called 'SomeAlias'. When specifying the columns to return, you need to indicate which table the column will be returned from. In this example, I am specifying the 'SomeAlias' table, but could just have easily specified the 'SomeTable'. In this case, it doesn't matter because they are inner joined, and will therefor always be the same. Nonetheless... you still need to specify which one.

Hope this helps.





-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

You need to prefix the fields you are returning with the alias as well e.g.

SELECT n.NoteID,
  n.NoteName,
  n.SourceID,
  n.UseID

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[chris123321]

After I added aliases to the NoteID, NoteName, usename, and sourcename the query worked fine...Thanks guys