Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

Create SQL View from 2 tables

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
panhead802
Asked:
panhead802
  • 2
1 Solution
 
ralmadaCommented:

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

Open in new window

0
 
ralmadaCommented:
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
 
panhead802Author Commented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now