Solved

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

Posted on 2014-02-20
6
197 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 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 46
Distinct values from two tables 14 20
scheduling a stored procedure in azue sql server 3 19
Mssql SQL query 14 28
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now