Solved

Create SQL View from 2 tables

Posted on 2010-08-31
3
408 Views
Last Modified: 2013-12-01
I have two tables in my database for simplicity sake they are as follows:

ITEM_NO, SEQ, COST and SKU, SEQ, COST the relationship is in the ITEM_NO/SKU field. the first table called MASTERMAIN has all of the items in a product database with a sequence ID of 1 the second table SEQTAB has all subsequent item senquences and their respective costs.

An Item in MASTERMAIN could be
ITEM_NO,SEQ,COST
AB04A,1,1.50

and in SEQTAB
SKU,SEQ,COST
AB04A,2,1.00
AB04A,3,0.35
AB04A,4,0.80

What I need to do is create a view as follows:

SKU,SEQ,COST
AB04A,1,1.50
AB04A,2,1.00
AB04A,3,0.35
AB04A,4,0.80

This cannot be that difficult but I am hitting a wall with it.
I am sure it will be painfully obvious when a solution is presented.
Any help would be grweatly appreciated.

Dan
0
Comment
Question by:panhead802
  • 2
3 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33568570

select IteM_NO as SKU, SEQ, Cost from MasterMain
union all
select SKU, SEQ, Cost from SEQTab

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33569222
and the view should be like this
create view yourviewname 
as
select IteM_NO as SKU, SEQ, Cost from MasterMain
union all
select SKU, SEQ, Cost from SEQTab

Open in new window

0
 

Author Comment

by:panhead802
ID: 33569275
Can you say brain fart... Thanks for the quick response. I have been playing with Joins and nested queries all morning and completely forgot about Unions.

Like I said painfully obvious, when you stare at the saem record set for too long everything becomes blurry.

Thanks again

Dan
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 46
Need help on t-sql 2012 10 53
sql server computed columns 11 29
sql server insert 12 28
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
Viewers will learn how the fundamental information of how to create a table.

816 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

7 Experts available now in Live!

Get 1:1 Help Now