OraFAQ Forum: Training & Certification

Home » Other » Training & Certification » master detail query (orable 10g, microsoft 2k3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

master detail query [message #344102]

Thu, 28 August 2008 15:10

dstomcat
Messages: 5
Registered: June 2005

Junior Member

I have a program where an instruction letter is created for a customer. The master db record is created when the letter is created and the detail record is created when the letter is modified/changed, usually sometime later by another person.

the pk for both tables is a field called 'control' and is integer type.

I want to be able to display the fname, lname and create date from the master table and the message date and message from the detail table if any changes have been made to the letter.

I think I want to use the 'IN' operator.

Could I see some sample code of what this might look like.

thanks,
David

Report message to a moderator

Re: master detail query [message #344103 is a reply to message #344102]

Thu, 28 August 2008 15:14

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Without your tables it is hard to answer.
Post create table statements including constraints.
Post an example of what you want.

Regards
Michel

Report message to a moderator

Re: master detail query [message #344108 is a reply to message #344103]

Thu, 28 August 2008 15:36

dstomcat
Messages: 5
Registered: June 2005

Junior Member

CREATE TABLE MANA.ECTCINSTMEMODET (detail table)
CONTROL       NUMBER(8),
MESSAGE_DATE  DATE,
AGENT         VARCHAR2(15 BYTE),
MESSAGE       VARCHAR2(60 BYTE)

CREATE TABLE MANA.ECTCINSTMEMOMAS  (master table)
CONTROL         NUMBER(8),
FNAME           VARCHAR2(15 BYTE),
MNAME           VARCHAR2(1 BYTE),
LNAME           VARCHAR2(25 BYTE),
CUST_NUMBER     VARCHAR2(20 BYTE),
AGENT_CREATED   VARCHAR2(40 BYTE),
CREATEDATE      DATE,
AGENT_COMPLETE  VARCHAR2(40 BYTE),
COMPLETEDATE    DATE,
COMPLETED       VARCHAR2(1 BYTE),
MEMO_TYPE       VARCHAR2(2 BYTE)

Display:
FName, LName, CreateDate, Message_date, Message.

could not see a quick way to copy the constraints info in this posting.

thanks for the guidance

David

[EDITED by LF: added [code] tags]

[Updated on: Sun, 31 August 2008 05:27] by Moderator

Report message to a moderator

Re: master detail query [message #344128 is a reply to message #344102]

Thu, 28 August 2008 16:48

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Go to Border's Books and by SQL-FOR-DUMMIES. It should give you the information you need as this question sounds like you need to learn some basic SQL.

To directly answer you intial question, yes, IN is one way. Write some code as see. Then show us your code and results. We will help.

Kevin

Report message to a moderator

Re: master detail query [message #344166 is a reply to message #344108]

Fri, 29 August 2008 00:16

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

could not see a quick way to copy the constraints info in this posting.

You can take your time to post all the information, we are not in hurry.
Also take time time to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: master detail query [message #344578 is a reply to message #344102]

Sat, 30 August 2008 13:23

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You will need to join the two tables. You can find syntax, explanation, and examples for joins for your Oracle version in the following section of the SQL Reference of the online docuementation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3157

The SQL reference is a good place to start reading to learn some basic SQL syntax.

Report message to a moderator

Previous Topic:	procedured that contains cursors
Next Topic:	please help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 27 21:29:15 CST 2024