SQL Server view

Not sure if this is "so-able."

I have three tables with sales information in them.

Each table has a "cust_no" field, year, sales.

How do I write a view that has
Customer          TY Sales      SalesYR-1   SalesYR-2
1                          200               100                  0
2                             0                   200              0
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
ee_rleeCommented:
try this
SELECT isnull(a.cust_no,isnull(b.cust_no,c.cust_no)) as custno, 
             isnull(a.sales,0) as salesa, 
             isnull(b.sales,0) as salesb, 
             isnull(c.sales,0) as salesc
FROM tablea a
FULL OUTER JOIN tableb b on a.cust_no = b.cust_no
FULL OUTER JOIN tablec c on (b.cust_no = c.cust_no AND b.cust_no is not null) OR 
                                               (a.cust_no = c.cust_no AND a.cust_no is not null)

Open in new window

0
 
gnarCommented:
What about just joining the tables on that cust_no field?

Something like:




SELECT a.cust_no, a.sales, b.sales, c.sales
FROM tablea a
JOIN tableb b on a.cust_no = b.cust_no
JOIN tablec c on b.cust_no = c.cust_no

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
gnar:
The cust_no doesn't exist in all tables.  I need have a column that has all the cust_no's whatever table they're in and then place a zero dollars in the sales part where they don't exist.
0
 
Larry Bristersr. DeveloperAuthor Commented:
WOW!  Excellent script.  Thanks and I'm on my way!
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.