Solved

Need a help with SQL statement. Quickly...

Posted on 2002-05-22
6
240 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now