Creating views in SQL and inserting data...

dgolay

First off, thanks ahead of time for any help that you may be able to give me on this problem. I am trying to create a view that consists of a single piece of data from about 12 different tables. I have figured out how to create the view and include all the columns that I need, but I cannot get the correct amount of data to insert into these tables. I have basically taken a ERD and created a view using the "CREATE OR UPDATE VIEW..." command. Then, in my "SELECT" clause, I select the columns that I want, and in my "FROM" clause, I insert all the tables that I am pulling from. NOw, in my "WHERE" clause, I am trying to basically set up all the relationships from the ERD. This is failing me, since I am returning with 0 records when I am done. I feel that I need to use and 'either/or' type of command, but I am unsure if there is on in SQL. My "WHERE" criteria does not need to happen all at the same time, so the "AND" command is not working right.

I have also tried this same method, just creating a regular table, with the same columns and stuff. Then, I try to insert each "WHERE" clause seperately, in hopes of getting all the data in there. WHen I do that, for each "WHERE" clause, the data duplicates itself. Meaning, the clauses that correspond to each other are not being recognized, so I am getting some duplicate ids for records that are really the same thing. I hope this makes sense...can you help me?

nemonoman

duplicated below

nemonoman

To make a VIEW work, first you need to get a working select. You haven't mastered this step, but you are likely close. The rest follows very quickly.

You are joining 12 tables you say.

The typical join is :
WHERE

tableA.id=tableB.foriegnkeyfromtableA
AND
tableB.id=tableC.foriegnkeyfromtableB

etc.

suppose you're going great guns until you get to tableJ, which by chance has no rows = tableK

If any part of the join has no rows, the result is an empty set.

What you may want is an OUTER JOIN or LEFT JOIN, that is, one that returns rows REGARDLESS of whether an equivalence is found.

I don't know the DB you are using -- MySQL doesn't have views, or at least the version I'm using doesn't. So I can't give you specifics for OUTER or LEFT JOIN statement. I'm just telling you where to look in the manual.

For example:

Person
id name
1 Sam
2 Joe
3 Ted

Phone
personid number
1 1234567
2 999999

SELECT person.name, phone.number FROM person, phone
WHERE person.id=phone.personid

Would return

Joe 1234567
Sam 999999

Note that Ted doesn't have a phone, so in the join, no row is returned for Ted.

I THINK the old Oracle outer join symbol was a *=

SELECT person.name, phone.number FROM person, phone
WHERE person.id*=phone.personid

means "include all person ids and return a NULL If they have no equivalent phone entry"

Returns:

Joe 1234567
Sam 999999
Ted

Get the idea?

From the SELECT, it's one step to a view

2 steps to
CREATE TABLE
INSERT INTO Mynewtable SELECT

which is all a view really is.

dgolay

Nemonoman...

Thanks so much for your help..that is exactly what I was looking for and exacly what my issue is. I will take a stab at it on Tuesday, with the Outer joins in place. I appreciate it.

Dgolay🙂