Create a single SQL View to return one column from 3 un-linked tables


Hi I am trying to create a single view that returns a single column from 3 different un-linked tables.

I can get it working with 2 tables using the following:

************************************************************
select A.SupplierName,B.ClientName,
from

    (SELECT SupplierName,row_number() over (order by SupplierName) as row_num
    FROM SupplierTable A)A
full join
    (SELECT ClientName,row_number() over (order by ClientName) as row_num
    FROM ClientTable B)B

on  A.row_num=B.row_num

ORDER BY A.SupplierName,B.ClientName

************************************************************

Here is my attempt at adding a third table

************************************************************
select A.SupplierName,B.ClientName, C.SiteName
from

    (SELECT SupplierName,row_number() over (order by SupplierName) as row_num
    FROM SupplierTable A)A
full join
    (SELECT ClientName,row_number() over (order by ClientName) as row_num
    FROM ClientTable B)B
full join
    (SELECT SiteName,row_number() over (order by SiteName) as row_num
    FROM SiteTable C)C

on  A.row_num=B.row_num
on  B.row_num=c.row_num

ORDER BY A.SupplierName,B.ClientName, C.SiteName


************************************************************

And the error I get is as follows:


************************************************************

The multi-part identifier "A.row_num" could not be bound.


************************************************************

It just so happens that the data in the columns I want to return is identical, if I can get this working what I really want to do is add a 4th table and column which is completely a different data type.

Any help would be great!

Thanks in advance.
saphireneilAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
SELECT COALESCE(A.row_num, B.row_num, C.row_num) AS row_num,
      A.SupplierName, B.ClientName, C.SiteName
FROM
      (
            SELECT SupplierName,row_number() over (order by SupplierName) as row_num
            FROM SupplierTable
      ) AS A
FULL OUTER JOIN
      (
            SELECT ClientName,row_number() over (order by ClientName) as row_num
            FROM ClientTable
      )AS B
      ON A.row_num = B.row_num
FULL OUTER JOIN
    (
            SELECT SiteName,row_number() over (order by SiteName) as row_num
            FROM SiteTable
      )AS C
      ON  B.row_num = C.row_num
        OR A.row_num = C.row_num
0
 
25112Commented:
try this:

select A.SupplierName,B.ClientName, C.SiteName
from

    (SELECT SupplierName,row_number() over (order by SupplierName) as row_num
    FROM SupplierTable A)A
full join
    (SELECT ClientName,row_number() over (order by ClientName) as row_num
    FROM ClientTable B)B


on  A.row_num=B.row_num

full join
    (SELECT SiteName,row_number() over (order by SiteName) as row_num
    FROM SiteTable C)C

on  B.row_num=c.row_num

ORDER BY A.SupplierName,B.ClientName, C.SiteName
0
 
25112Commented:
the syntax has to be
select * from
a join b
on a.id=b.id
join c
on
c.id = b.id....

the syntax you had was

select * from
a join b
join c
on
on a.id=b.id and
c.id = b.id

the solution is - after every JOIN, you have to define the JOIN conditions.. so you have 20 joins.. but you should place the 20 join conditions right after the joins, and not at the end..  please try.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the code looks correct, so I must presume your actual code is actually slightly different

what about this:
select A.SupplierName,B.ClientName, C.SiteName
from
    (SELECT SupplierName,row_number() over (order by SupplierName) as row_num
    FROM SupplierTable A)A
full outer join
    (SELECT ClientName,row_number() over (order by ClientName) as row_num
    FROM ClientTable B)B
on  A.row_num=B.row_num
full outer join
    (SELECT SiteName,row_number() over (order by SiteName) as row_num
    FROM SiteTable C)C
on  B.row_num=c.row_num

ORDER BY A.SupplierName,B.ClientName, C.SiteName

Open in new window

0
 
saphireneilAuthor Commented:
Thanks, spot on the mark.
0
 
saphireneilAuthor Commented:
Thanks Again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.