Home » RDBMS Server » Server Administration » Fancy SQL*PLUS
Fancy SQL*PLUS [message #370956] Mon, 13 March 2000 23:50 Go to next message
David Banas
Messages: 3
Registered: February 2000
Junior Member
Hi, We have tables where groups of rows, anywhere
from 1 to 10 approximately relate to one entity, ie
a customer call are in one table. Recapping, 5 rows
in the same table make up one 'order'. I need to
query/manipulate all these 5 rows together AND separate
these different groups of so many 'rows'/'calls' up
by different criteria. Any suggestions??

Thanks in advance.
Re: Fancy SQL*PLUS [message #370958 is a reply to message #370956] Tue, 14 March 2000 06:49 Go to previous message
Paul
Messages: 164
Registered: April 1999
Senior Member
David,
If these tables are properly designed, the master table (parent) will have a primary key column with a unique value for each row. This column will then appear as a foreign key in each of the records of the detail table (child) that are associated with this parent record. You can then select data from the parent record and all the child records associated with it with something like this
SELECT p.col1, p.col2, c.col1, c.col2, c.col3
FROM your_master_table p,
your_detail_table c
WHERE c.fk_col = p.pk_col;

you can add other criteria, from either table as in:

SELECT p.col1, p.col2, c.col1, c.col2, c.col3
FROM your_master_table p,
your_detail_table c
WHERE c.fk_col = p.pk_col
AND p.date_col = '10-MAR-00';

-- this gives all masters and associated details for a given date (assuming your master table contains an 'order date' column)

or:

SELECT p.col1, p.col2, c.col1, c.col2, c.col3
FROM your_master_table p,
your_detail_table c
WHERE c.fk_col = p.pk_col
AND c.problem like '%bugs%';

-- this will display the master record and the associated detail record(s) for any masters having one or more detail records containing the string 'bugs' anywhere in the 'problem' column.

Hope this helps,
Paul
Previous Topic: Re: Add on...DML
Next Topic: Re: Free Blocks in a single sql query, Suresh
Goto Forum:
  


Current Time: Thu Oct 22 05:02:56 CDT 2020