Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create SQL View from 2 tables

Posted on 2010-08-31
3
Medium Priority
?
414 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Microsoft will be releasing the Windows 10 Creators Update in just a matter of weeks. Are you prepared? Follow these steps to ensure everything goes smoothly and you don't lose valuable data on your PC.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

671 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