Solved

Need a help with SQL statement. Quickly...

Posted on 2002-05-22
6
244 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 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
Industry Leaders: 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

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!

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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