Solved

Master detail problem

Posted on 2002-06-10
19
196 Views
Last Modified: 2010-04-04
Hi Guyz,

I have a slight problem, I have a Master detail relationship with 2 databases.
1) Customer Database = Master.
2) Debts Database = Detail.

I would like to navigate though the database and if the record count in the debts database is 0 then I don't want to see the master record.

The problem is I have records being printed when no detail is available.

How can I get round this?

300 Points to the best example that works for me.

Thanks
Andy
0
Comment
Question by:Asw
  • 9
  • 6
  • 2
  • +2
19 Comments
 
LVL 1

Author Comment

by:Asw
ID: 7068624
Forgot to say I am using TQuery.
Thanks
Andy
0
 
LVL 2

Expert Comment

by:freshman3k
ID: 7068679
Hello!

Can you please be more specific like all fields you need to be returned and from what table, so I can help you



0
 
LVL 9

Expert Comment

by:ITugay
ID: 7069134
hi,

let suppose you have "Customer" table:
id_customer int not null primary key,
customer_name varchar(50)

use select statement:

and "debts" table:
fk_customer int not null references customer(id_customer),
debth_date timesatmap,
debth_info varchar

select id_customer, customer_name
from customer, debts
where id_customer = fk_customer

in this case no master records will be selected if record has not details


-------
Igor
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 1

Author Comment

by:Asw
ID: 7069697
Hi Igor,Freshman3k  

If you use the database wizard from delphi's menu , and create a form using the options master/detail form and
create a form using TQuery objects use DBdemos alias and master customer and detail orders, now run the program and scroll down the list you will come across a master record but no detail , now I don't want any master if no detail is available.

If you could show me how to do this with the above demo I would be able to adapt my program.

Thanks for your help.

Andy
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7070048
select * from mastertable where id in (select master_id from childtable)

use this select for your master and you will have the effect you want

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7070052
hi igor, freshman :-))
0
 
LVL 1

Author Comment

by:Asw
ID: 7070089
Hi Meikl,

Can you do me an example based on my previous comment.

Thaks

Andy
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7070102
well,
i will post it this evening in about 3 hours

meikl ;-)
0
 
LVL 1

Author Comment

by:Asw
ID: 7070124
Hi Meikl,

Can you do me an example based on my previous comment.

Thaks

Andy
0
 
LVL 1

Author Comment

by:Asw
ID: 7070127
Thanks Meikl
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 300 total points
ID: 7070481
well ok,

after working with the wizard,
the master-query shows

Select
  customer."CustNo",
  customer."Company",
  customer."Addr1",
  customer."Addr2",
  customer."City",
  customer."State",
  customer."Zip",
  customer."Country",
  customer."Phone",
  customer."FAX",
  customer."TaxRate",
  customer."Contact",
  customer."LastInvoiceDate"
From "customer.db"
As customer

then just append there

where  customer."CustNo"  in (select orders."CustNo" from "orders.db" As orders)

so that the select shows like

Select
  customer."CustNo",
  customer."Company",
  customer."Addr1",
  customer."Addr2",
  customer."City",
  customer."State",
  customer."Zip",
  customer."Country",
  customer."Phone",
  customer."FAX",
  customer."TaxRate",
  customer."Contact",
  customer."LastInvoiceDate"
From "customer.db"
As customer
where  customer."CustNo"  in (select orders."CustNo" from "orders.db" As orders)

thats all
btw. in my sample data only one customer has no details

i can also post the whole project,
if needed

meikl ;-)
0
 
LVL 1

Author Comment

by:Asw
ID: 7070611
Thanks Meikl the points are yours.

Thanks for you comments Igor, freshman3k.

Andy
0
 
LVL 4

Expert Comment

by:nestorua
ID: 7070716
HI,
If you want TO SHOW the master records which don't have the corresponding detail records you use OUTER joins.
If you DON'T want TO SHOW those records you use inner joins.
That's all.
Sincerely,
Nestorua.
0
 
LVL 1

Author Comment

by:Asw
ID: 7071296
Thanks nestorua.

Meikl can you help me a little more?

Your solution works fine if no detail record exists at all, but if the detail records are not available because you have selected  orders.Amountpaid < 70000 the solution does not work.

How can I make it work?

Select
  orders."OrderNo",
  orders."CustNo",
  orders."SaleDate",
  orders."ShipDate",
  orders."EmpNo",
  orders."ShipToContact",
  orders."ShipToAddr1",
  orders."ShipToAddr2",
  orders."ShipToCity",
  orders."ShipToState",
  orders."ShipToZip",
  orders."ShipToCountry",
  orders."ShipToPhone",
  orders."ShipVIA",
  orders."PO",
  orders."Terms",
  orders."PaymentMethod",
  orders."ItemsTotal",
  orders."TaxRate",
  orders."Freight",
  orders."AmountPaid"
From "orders.db"
As orders
Where
  "orders"."CustNo" =:"CustNo"
and  orders."AmountPaid" > 70000   <===  This line added
0
 
LVL 9

Expert Comment

by:ITugay
ID: 7072015
hi all,

hi meikl,


i think that statement bellow will do the same and may be faster (not sure):

select customer.* from customer, orders
where orders.custno = customer.custno


-----
Igor.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7072046
yep, igor, should be faster,

asw, you must move the where part into the masterquery

(just taking igors suggestion)
select customer.* from customer, orders
where orders.custno = customer.custno
and  orders."AmountPaid" > 70000  

or from the graded answer

Select
 customer."CustNo",
 customer."Company",
 customer."Addr1",
 customer."Addr2",
 customer."City",
 customer."State",
 customer."Zip",
 customer."Country",
 customer."Phone",
 customer."FAX",
 customer."TaxRate",
 customer."Contact",
 customer."LastInvoiceDate"
From "customer.db"
As customer
where  customer."CustNo"  in (select orders."CustNo" from "orders.db" As orders where orders."AmountPaid" > 70000)

hope this helps

meikl ;-)
0
 
LVL 1

Author Comment

by:Asw
ID: 7073226
Thanks Meikl, Igor
that works excellent.

Can you recommend anywhere I can learn about sql statements

Andy
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7074844
well andy,

go to
http://www.sqlcourse.com/

thats an interactive sql-tutorial and
shows the basics for sql-statements and
you can test yourself there

meikl ;-)
0
 
LVL 1

Author Comment

by:Asw
ID: 7074940
Thank meikl
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question