Let me first describe the tables I'm working with:

table1 with these columns:
user_id cat_id more_data even_more_data

table
cat_id

table1 may have one row for every cat_id in table2 which may be as many
as 20

I want to make sure there is a row in table1 for every cat_id, such that
every user_id has 20 rows in table.

I'm trying to use an insert with a select to accomplish this. Is this
even possible? Here is my query all comments appreciated.

INSERT INTO table1
(cat_id)
SELECT cat_id
FROM table2
WHERE not exists
(select cat_id from table1
where table1.user_id = 9999
)
where table1.user_id = 9999

The way I read this like this:
insert into table1's cat_id column all cat_id's that exist in table2 but
not in table2 for a given user_id. But this query fails with a not
properly ended error.

Oracle 9i

LorenzoPT <lorenzo (AT) diespammerhurmans (DOT) com> writes:

This smells a bit like a homework assignment!

I see a couple of problems

Firstly, I would check your syntax.

Also, think about what will be inserted. Nothing in your insert
specifies a user_id - if this is your primary key, it won't work. If its
not, you will end up with a lot of rows with 'null' for user_id.

Secondly, this is a very inefficient solution. Using a sub-query in this
way means that you are doing that query once for every row in table
1. If that is a large table, you are going to be doing a lot of
queries. (I'm a bit confused with the where clause - your specification
doesn't restrict your processing just to user_id 9999.

You would be better off letting Oracle do the grunt work. Look at outer
joins . If thats too confusing, check out the set operators.

As a general rule, I think you should only resort to sub selects in your
query when no other solution presents itself and when you do, be very
wary of sub queries that use exists/not exists. sometimes it is the best
solution, but all too often it is the first choice because at some
levels its conceptually easier.

In a standard join, you only get the records that actually joined on the
specified join columns (i.e. value was in both tables). The outer join
will allow you to include the rows that didn't match and will have a
null in the column.

Tim

--
tcross (at) rapttech dot com dot au

LorenzoPT <lorenzo (AT) diespammerhurmans (DOT) com> writes:

This smells a bit like a homework assignment!

I see a couple of problems

Firstly, I would check your syntax.

Also, think about what will be inserted. Nothing in your insert
specifies a user_id - if this is your primary key, it won't work. If its
not, you will end up with a lot of rows with 'null' for user_id.

Secondly, this is a very inefficient solution. Using a sub-query in this
way means that you are doing that query once for every row in table
1. If that is a large table, you are going to be doing a lot of
queries. (I'm a bit confused with the where clause - your specification
doesn't restrict your processing just to user_id 9999.

You would be better off letting Oracle do the grunt work. Look at outer
joins . If thats too confusing, check out the set operators.

As a general rule, I think you should only resort to sub selects in your
query when no other solution presents itself and when you do, be very
wary of sub queries that use exists/not exists. sometimes it is the best
solution, but all too often it is the first choice because at some
levels its conceptually easier.

In a standard join, you only get the records that actually joined on the
specified join columns (i.e. value was in both tables). The outer join
will allow you to include the rows that didn't match and will have a
null in the column.

Tim

--
tcross (at) rapttech dot com dot au

LorenzoPT <lorenzo (AT) diespammerhurmans (DOT) com> writes:

This smells a bit like a homework assignment!

I see a couple of problems

Firstly, I would check your syntax.

Also, think about what will be inserted. Nothing in your insert
specifies a user_id - if this is your primary key, it won't work. If its
not, you will end up with a lot of rows with 'null' for user_id.

Secondly, this is a very inefficient solution. Using a sub-query in this
way means that you are doing that query once for every row in table
1. If that is a large table, you are going to be doing a lot of
queries. (I'm a bit confused with the where clause - your specification
doesn't restrict your processing just to user_id 9999.

You would be better off letting Oracle do the grunt work. Look at outer
joins . If thats too confusing, check out the set operators.

As a general rule, I think you should only resort to sub selects in your
query when no other solution presents itself and when you do, be very
wary of sub queries that use exists/not exists. sometimes it is the best
solution, but all too often it is the first choice because at some
levels its conceptually easier.

In a standard join, you only get the records that actually joined on the
specified join columns (i.e. value was in both tables). The outer join
will allow you to include the rows that didn't match and will have a
null in the column.

Tim

--
tcross (at) rapttech dot com dot au

LorenzoPT <lorenzo (AT) diespammerhurmans (DOT) com> writes:

This smells a bit like a homework assignment!

I see a couple of problems

Firstly, I would check your syntax.

Also, think about what will be inserted. Nothing in your insert
specifies a user_id - if this is your primary key, it won't work. If its
not, you will end up with a lot of rows with 'null' for user_id.

Secondly, this is a very inefficient solution. Using a sub-query in this
way means that you are doing that query once for every row in table
1. If that is a large table, you are going to be doing a lot of
queries. (I'm a bit confused with the where clause - your specification
doesn't restrict your processing just to user_id 9999.

You would be better off letting Oracle do the grunt work. Look at outer
joins . If thats too confusing, check out the set operators.

As a general rule, I think you should only resort to sub selects in your
query when no other solution presents itself and when you do, be very
wary of sub queries that use exists/not exists. sometimes it is the best
solution, but all too often it is the first choice because at some
levels its conceptually easier.

In a standard join, you only get the records that actually joined on the
specified join columns (i.e. value was in both tables). The outer join
will allow you to include the rows that didn't match and will have a
null in the column.

Tim

--
tcross (at) rapttech dot com dot au