?
Solved

Need a help with SQL statement. Quickly...

Posted on 2002-05-22
6
Medium Priority
?
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 600 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 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