[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • 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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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