Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

paradox query problem

Posted on 2008-10-22
6
Medium Priority
?
269 Views
Last Modified: 2013-11-23
Hi
I am using Paradox tables.
Can somebody advise me on how to create a query in delphi using 2 tables
I have a header table and detail table ( 1 to N relationship)
The key field is Job_no which is unique in the header
I want to create a query which shows me all the records that have no header records in the details table
I what to avoid using a do while loop
Regards



0
Comment
Question by:SSSIAN
  • 4
6 Comments
 
LVL 6

Expert Comment

by:FactorB
ID: 22779724
You need a JOIN query, that will bring together one to many relational tables

Select header.*,detail.* from header inner join detail on header.job_no = detail.job_no;
0
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 300 total points
ID: 22783237
no you need a left join, not an inner

select d.*
from detail d
  left join header h on h.job_no = d.job_no
order by d.job_no

this will show you all the records in detail without header
and order them

select d.job_no, count(*) as amount_no_header
from detail d
  left join header h on h.job_no = d.job_no
group by d.job_no
order by d.job_no

will show how many records for each job_no that have no header

to delete all records in detail without header:
delete from details
where not job_no in (select job_no from header)

to see all headers without details
select h.*
from header h
  left join details d on h.job_no = d.job_no
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 22784254
didn't the solution work ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:SSSIAN
ID: 22784429
Sorry Geert_Gruwez:
I am new to this, I thought I accepted the solution and gave you the points
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 22784468
if the solution works, you should give an A
if it only works half and you have to edit the code and do research give it a B
if it doesn't work at all give comments and let the experts handle the comments

if you are totally dissatified with the experts comments and all experts have commented
give it a C

or read the help section on Asking Questions and grading on the EE
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 22788518
did the solution work ?
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

580 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