Well, Gordon, looks like you missed the thread. ;-)
What's happening is that DJ wants to add records to a table only if
another table has a certain record in it. I have been trying to convince
him to set up a FOREIGN KEY between the two. Since he didn't give any
real table name I will have to make up a situation to illustrate his
problem: Your boss sees productivity numbers going into the tank and asks
you to produce a report of how much time certain employees spend on the
phone. You can get from the phone system a log of every call made into or
out of your company that shows you what extension was active, when it went
hot, when it hung up, the outside number or extension of the other end of
the call, and which end initiated the call. Being a good DBA, you break
out your database skills and start coding.
For this example, lets create two tables, WatchList and PhoneLog. The
WatchList table provides an id value for everyone you are interested in
tracking calls for. You get call records for everyone in your company but
you only want to write into PhoneLog those phone calls either to or from
those specific extensions on the WatchList.
You want to make sure that if you add a record to PhoneLog, you have a
WatchList record already defined otherwise the watchlist_id field would
have to be NULL doing you absolutely no good to getting to the bottom of
the issue.
CREATE TABLE WatchList (
id int auto_increment
, name varchar(20)
, ext int
,Primary Key(id)
);
CREATE TABLE PhoneLog (
id int auto_increment
, watchlist_id int
, direction varchar(4)
, number varchar(25)
, startdatetime datetime
, enddatetime datetime
, PRIMARY KEY(id)
);
Some partial data could look like:
WatchList (id, name, ext)
-------------------------
1, Shawn, 101
2, Gordon , 102
3, DJ, 103
PhoneLog (id, watchlist_id, direction, number, startdatetime,
stopdatetime)
--------------------------------------
1, 2, from, 555-0404, ...
1,2, to, 555-9897, ...
What DJ would like to do is to detect if extension 101 (Shawn) has a
record in WatchList so that he could know whether or not to add the phone
call event to the PhoneLog table. Preferably he wants to do this in a
single query but unless he sets up a Foreign Key from PhoneLog to
WatchList he will have to make that determination by using a second query.
His original question was asking if he could use a subquery to avoid the
second check. I said probably not.
Does that help you understand what you missed?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Gordon Bruce" <[EMAIL PROTECTED]> wrote on 09/23/2005 03:26:05 PM:
> What am I missing
>
> INSERT INTO table1 (column names.....)
> SELECT VALUES......
> FROM table2
> WHERE primary id = insert value
>
> You will have to put in your real table name and column names.
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Friday, September 23, 2005 11:59 AM
> To: DJ
> Cc: mysql@lists.mysql.com
> Subject: Re: insert subquery
>
> DJ <[EMAIL PROTECTED]> wrote on 09/23/2005 12:49:35 PM:
>
> > [EMAIL PROTECTED] wrote:
> >
> > >DJ wrote on 09/23/2005 12:22:58 PM:
> > >
> > >
> > >>i want to insert a row into table1 only if the value being inserted
> on
>
> > >>table1 exists on table2 primary id.
> > >>
> > >>can i do this with subquery?
> > >>
> > >>thanx.
> > >>
> > >>
> > >>
> > >
> > >Depending on what version MySQL you are using, probably not. A very
> robust
> > >method of doing what you propose is to allow MySQL to do it for you
> by
> > >establishing a Foreign Key from table1 to table2. One drawback is
> that
> > >both tables need to be InnoDB (which you may not want to support).
> > >
> > >What version are you using and what is the possibility of using
> InnoDB
> > >with your appliation?
> > >
> > >Shawn Green
> > >Database Administrator
> > >Unimin Corporation - Spruce Pine
> > >
> > >PS: always CC the list on all responses (unless you intentionally
> mean
> to
> > >take the conversation off-list)
> > >
> > >
> > i am using 4.1.x
> > hmm.. maybe it's easier if i just check the id is in table2 before
> > inserting into table1.
> > not really a big deal just looking to create shortcuts without running
>
> > multiple queries..
> >
> >
>
> With a foreign key defined, you only need to run one query. That's why I
>
> mentioned it. ;-) Your way works, too.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
