Solved

Need a help with SQL statement. Quickly...

Posted on 2002-05-22
6
241 Views
Last Modified: 2010-04-04
Dear experts!

I need a help with generating an SQL statement. Actually, it's a local SQL (Paradox 7.0 tables). My program is a kind of book library database manager. For the sake of simplicity let's consider it manages only these two tables:


Table "Books" has the following fields:

1) BookNo (autoinc, key)
2) Title (string, title of book)
3) Author (string, author of book)
4) Category (string, i.e. scifi, technical,...)


Table "Lendings" consists of these fields:

1) LendingNo (autoinc, key)
2) BookNo (integer, points to the key of Books table)
3) LendingDate (date, when reader borrows the specified book)
4) ReturnDate (date, when reader must return the specified book)


Books table holds information about particular books in the library. Lendings table holds information about history of all lending transactions - i.e. when reader asks to borrow some book.

What I want to do? I need to know how many lendings (records) match all of these criteria:

1) LendingDate is between :Startdate and :EndDate.
2) Book which is lent as pointed to by BookNo field of Lendings table, must be of specific :Category.

Please generate the necessary SQL statements with a *PARAMS* for me. I'm not very familiar with those SQL secrets...


Thanks in advance, Ivo.

P.S. Please be quick.
0
Comment
Question by:ivobauer
  • 3
  • 3
6 Comments
 

Expert Comment

by:lsae
ID: 7028506
Query 1

Select *
from Lendings L
join Books B
on L.bookno=B.bookno
where lendingdate between :begindate and :enddate

Remark : use query1.parambyname('begindate').asdatetime=date      (or any value converted to TDateTime type)  to fill the params, this to avoid incompatibilities in datatypes


Query2

Select *
from Lendings L
join Books B
on L.bookno=B.bookno
where not L.lendingdate is null and L.Returndate is null and B.category= :cat

Greetings,


Lsae
0
 
LVL 2

Author Comment

by:ivobauer
ID: 7028562
Hi Lsae,

thanks a lot for a quick reply, but why do you propose two queries? One query is not enough? I need to know how many records from Lendings table has LendingDate between :StartDate and :EndDate along with BookNo -> Book's category must be exact match (not case sensitive) of a specific :Category.

Hope you understand, Ivo.
0
 

Accepted Solution

by:
lsae earned 150 total points
ID: 7028742
OK
 I combined them and provided case insensitivity...
I hope this helps.

Select *
from Lendings L
join Books B
on L.bookno=B.bookno
where lendingdate between :begindate and :enddate and
not L.lendingdate is null and L.Returndate is null and upper(B.category)=upper( :category)


Greetings,


Lsae
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 2

Author Comment

by:ivobauer
ID: 7029221
Perfect! This is really what I need. Acutally I've stripped the tests date fields against nil since they are required (I did not mention it).

Thank you very much and enjoy your points.

Ivo


P.S. Do you know any good online sources where to learn something about (local) SQL - any links or tutorials?
0
 

Expert Comment

by:lsae
ID: 7030773
Thanks for the points.

You can find help on local SQL in the delphi helpfiles. I found it very helpfull myself...


Greetings,

Lsae
0
 
LVL 2

Author Comment

by:ivobauer
ID: 7033256
Fine, I'll check it for sure...
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Simple Delphi Question 9 90
Delphi 2 60
Base1 Encode/Decode 3 77
Breakpoint doesn't stop in my variable 3 28
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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