Solved

Create SQL View from 2 tables

Posted on 2010-08-31
3
406 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

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

9 Experts available now in Live!

Get 1:1 Help Now