[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-02-20
6
Medium Priority
?
208 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

656 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