RE: Question about sql

SQL> select * from test1;

COMPANY_ID

        16
        45
        50

SQL> select * from test2
  2 ;

COMPANY_ID LEV_ID VARE_ID
---------- ---------- ----------

        16         45         56
        34         10         20
        67         10         20
        45         15         30
        50         12         12

SQL> create table test3 as
  2       (select test2.company_id,lev_id,vare_id
  3         from test1, test2
  4        where test1.company_id = test2.company_id);

Table created.

SQL> select * from test3;

COMPANY_ID LEV_ID VARE_ID
---------- ---------- ----------

        16         45         56
        45         15         30
        50         12         12

HTH Mark

-----Original Message-----
roland.skoldblom_at_ica.se
Sent: 16 October 2003 15:20
To: Multiple recipients of list ORACLE-L

Hallo,

Anyone whom could gíve me some good help on this.

I have a table with one field, called Company_id Like this

       Company_id

      16
      45
      50

In table 2 I have this field and values:

Company_id Lev_id Vare_id

              16  45          56
              34  10          20
              67  10          20
              45  15          30
              50  12          12

I would like to from table 2 do a select and find the Company_ids from table1 and find out the responding lev_id and vare_id in table2and then insert them into a new table(table3) so it would look like this:

Company_id        Lev_id            Vare_id
16                45          56

Anyone whom could give me some help on this how to write the sql- query.

I have tried with this sql query. What is wrong?

insert into table3(company_id,lev_id,vare_id)
    select company_id,lev_id,vare_id from table2 where vare_id = 56
    (where exists select  company_id  from table1)

Thanks in advance

Roland

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: roland.skoldblom_at_ica.se

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).