Asw
asked on
Master detail problem
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
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
Hello!
Can you please be more specific like all fields you need to be returned and from what table, so I can help you
Can you please be more specific like all fields you need to be returned and from what table, so I can help you
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
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
ASKER
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
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
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 ;-)
use this select for your master and you will have the effect you want
meikl ;-)
hi igor, freshman :-))
ASKER
Hi Meikl,
Can you do me an example based on my previous comment.
Thaks
Andy
Can you do me an example based on my previous comment.
Thaks
Andy
well,
i will post it this evening in about 3 hours
meikl ;-)
i will post it this evening in about 3 hours
meikl ;-)
ASKER
Hi Meikl,
Can you do me an example based on my previous comment.
Thaks
Andy
Can you do me an example based on my previous comment.
Thaks
Andy
ASKER
Thanks Meikl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Meikl the points are yours.
Thanks for you comments Igor, freshman3k.
Andy
Thanks for you comments Igor, freshman3k.
Andy
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.
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.
ASKER
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
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
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.
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.
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 ;-)
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 ;-)
ASKER
Thanks Meikl, Igor
that works excellent.
Can you recommend anywhere I can learn about sql statements
Andy
that works excellent.
Can you recommend anywhere I can learn about sql statements
Andy
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 ;-)
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 ;-)
ASKER
Thank meikl
ASKER
Thanks
Andy