?
Solved

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

Posted on 2014-02-20
6
Medium Priority
?
209 Views
Last Modified: 2014-02-20

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.
0
Comment
Question by:saphireneil
6 Comments
 
LVL 5

Expert Comment

by:25112
ID: 39874369
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39874374
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
 
LVL 5

Expert Comment

by:25112
ID: 39874377
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39874383
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
 

Author Closing Comment

by:saphireneil
ID: 39874743
Thanks, spot on the mark.
0
 

Author Comment

by:saphireneil
ID: 39874785
Thanks Again
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question