Home » Other » Training & Certification » master detail query (orable 10g, microsoft 2k3)
master detail query [message #344102] Thu, 28 August 2008 15:10 Go to next message
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
Re: master detail query [message #344103 is a reply to message #344102] Thu, 28 August 2008 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: master detail query [message #344108 is a reply to message #344103] Thu, 28 August 2008 15:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: master detail query [message #344166 is a reply to message #344108] Fri, 29 August 2008 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: master detail query [message #344578 is a reply to message #344102] Sat, 30 August 2008 13:23 Go to previous message
Barbara Boehmer
Messages: 8635
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.

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


Current Time: Thu Dec 08 02:37:40 CST 2016

Total time taken to generate the page: 0.11842 seconds