| Fancy SQL*PLUS [message #370956] |
Mon, 13 March 2000 23:50  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
