Solved

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

Posted on 2014-02-20
6
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 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