Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Insert...Select using multiple tables

    Folks;

    I have a database containing three tables: a user table with one or more attributes (e.g., normal, admin, VAR), a product table, and a junction table that connects the two. They look like the following:

    user {
    userid,
    userisvar,
    ...
    }

    product {
    prodid,
    prodname,
    ...
    }

    userprodmatrix {
    userindex,
    prodindex
    }

    Here's what I want to accomplish: I want to add a new product (either by name or id) to userprodmatrix for those users who currently have product X, where X is not the same as the new product. I have no idea how to use input...select to accomplish this (I'm very new to SQL). The correct SQL would add hundreds of rows to the userprodmatrix table.

    Any help provided would be most welcome.

    Thanks!

    David

  2. #2
    Join Date
    Aug 2003
    Posts
    32
    I tried the following on MS SQL, which worked, but it didn't work on MySQL... maybe it would give you something to start with? Hope this helps.

    Code:
    create database tmp;
    use tmp;
    create table usr (id int, name varchar(50));
    insert into usr values (1, 'sam');
    insert into usr values (2, 'bob');
    insert into usr values (3, 'jim');
    create table prd (id int, name varchar(50));
    insert into prd values (1, 'one');
    insert into prd values (2, 'two');
    insert into prd values (3, 'three');
    insert into prd values (4, 'four');
    create table usrprd (usrid int, prdid int);
    insert into usrprd values (1, 1);
    insert into usrprd values (1, 3);
    insert into usrprd values (2, 2);
    insert into usrprd values (3, 3);
    
    insert into 
      usrprd
    select
      usr.id as usrid,
      4 as prdid
    from
      usr,
      usrprd
    where
      usrprd.usrid = usr.id and
      usrprd.prdid = 3;
    
    select * from usrprd;
    Last edited by stacey_richards; 02-11-04 at 16:48.

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Actually, that did work on MySQL (v4.0.17)! My resulting usrprod table is as follows:

    +-------+-------+
    | usrid | prdid |
    +-------+-------+
    | 1 | 1 |
    | 1 | 3 |
    | 2 | 2 |
    | 3 | 3 |
    | 1 | 4 |
    | 3 | 4 |
    +-------+-------+

    Thanks VERY much, stacey_richards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •