| Creating Views [message #372616] |
Wed, 28 February 2001 11:31  |
Mike Oakes
Messages: 25
Registered: December 2000
|
Junior Member |
|
|
hello,
I want to create a simple view based on the following information but am having problems. Any help would be greatly appreciated
I have 2 tables called TableA and TableB. I need to pull data from each into a view.
Example,
I have TableA with columns ID,Name,Date and TableB has columns ID,Name,Date1
I want to pull all records from both tables where the ID = a specific value.
For example in TableA there are 2 records with ID=1 and in TableB there is 1 record with ID=1.
Example of the data
TABLEA
ID NAME DATE
1 MIKE 2/2/01
1 MIKE 2/5/01
TABLEB
ID NAME DATE1
1 MIKE 1/1/01
In essence I want the view to create 3 records, listing the values, but what I am finding is that my view is only returning 2 records which look like
ID NAME DATE DATE1
1 Mike 2/2/01 01/01/01
1 Mike 2/5/01 01/01/01
The problem is that TABLEB has the DATE1 value repeated for every instance of TABLE1. So what would happen is that if there were 10 records in TABLEA and 1 record in tableb, I would have 10 total records with the DATE1 repeated 10 times.
My goal is to identify what records came from TABLEA and what records came from TABLEB. With the previous output I can't identify how many records came from TABLEB because the date1 gets filled in for every instance of TABLEA.
My create view statement looks like.
create view vtest (ID,NAME,DATE,DATE1) as
(SELECT TABLEA.ID,TABLEA.NAME,TABLEA.DATE,TABLEB.DATE1 from TABLEA,TABLEB where
TABLEA.ID = TABLEB.ID);
Is there a way to get back 3 records that look like
ID NAME DATE DATE1
1 MIKE | 2/2/01 |
1 MIKE | 2/5/01 |
1 MIKE | Null |01/01/01
or a way to identify the values and counts of how many records came from each table.
Thanks for your help,
Mike Oakes.
|
|
|
|
|
|
| Re: Creating Views [message #372618 is a reply to message #372617] |
Wed, 28 February 2001 13:01  |
Mike Oakes
Messages: 25
Registered: December 2000
|
Junior Member |
|
|
thanks bobby. I am pretty new to sql. How exactly would I use the outer join. Could you give me an example in this case.
thanks for your help
|
|
|
|
| Re: Creating Views [message #372619 is a reply to message #372616] |
Wed, 28 February 2001 14:17 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
create view vtest (ID,NAME,DATE,DATE1) as
(SELECT TABLEA.ID, TABLEA.NAME, TABLEA.DATE, Null AS DATE1
from TABLEA
UNION
SELECT TABLEB.ID, TABLEB.NAME, Null, TABLEB.DATE1
from TABLEB);
|
|
|
|
|
|
| Re: Creating Views [message #372634 is a reply to message #372619] |
Thu, 01 March 2001 07:58 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
(unless there are dup rows within table1 or dup rows within table2)
for this view Union All will not be needed to return all rows from the underlying tables because:
tableA Date value exists and Date1 is always null
tableB Date is always null and Date1 value exists
therefore tableA and tableB records will always be distinct and all rows returned.
I had not thought about the execution time being faster with union all. Thanks for pointing that out.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
