Solved

Need a help with SQL statement. Quickly...

Posted on 2002-05-22
6
242 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
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!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi 2 77
Breakpoint doesn't stop in my variable 3 36
How to make Sign in, using Clientdataset? 1 33
delphi popmenu non latine charcters 3 31
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 The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

763 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