SQL Server Joining 3 Tables

Question

Please help me, below is my sql query. When I execute it, the result is nothing.

SELECT tblclientinfo.acctnum, 
       tblclientinfo.fname, 
       tblclientinfo.fname, 
       tblreservation.unitnum, 
       tblbillingsched.billnum, 
       tblbillingsched.duedate, 
       tblbillingsched.monthlyamort, 
       tblbillingsched.totalbalance 
FROM   tblclientinfo 
       JOIN tblreservation 
         ON tblclientinfo.acctnum = tblreservation.reservationnum 
       JOIN tblbillingsched 
         ON tblreservation.reservationnum = tblbillingsched.accountnum 
WHERE  tblbillingsched.accountnum = 'C0000000021' 
       AND tblbillingsched.duedate BETWEEN '1/1/2014' AND '1/30/2014'

Here are my tables:

tblClientInfo, pk = acctNum
tblReservation, pk = ReservationNum, fk = accountNum
tblBillingSched, pk = ID,  fk = accountNum

Thanks guys for your prompt reply but the result is still empty. I double check every table and all of them have data. I think my pk and fk is the problem but I don't know how to fix it. Please see my table relationship picture. [link](http://farm6.staticflickr.com/5520/9950519065_06207ec31e_z.jpg) — Sishan, Sep 26 '13 at 14:03

Kermit · Accepted Answer · 2013-09-26T13:50:00.757

1

I think you're looking for...

SELECT tblclientinfo.acctnum, 
       tblclientinfo.fname, 
       tblclientinfo.fname, 
       tblreservation.unitnum, 
       tblbillingsched.billnum, 
       tblbillingsched.duedate, 
       tblbillingsched.monthlyamort, 
       tblbillingsched.totalbalance 
FROM   tblclientinfo 
       JOIN tblreservation 
         ON tblclientinfo.acctnum = tblreservation.accountnum 
       JOIN tblbillingsched 
         ON tblclientinfo.acctnum = tblbillingsched.accountnum 
WHERE  tblbillingsched.accountnum = 'C0000000021' 
       AND tblbillingsched.duedate BETWEEN '1/1/2014' AND '1/30/2014'

See a demo

edited Sep 26 '13 at 13:50

answered Sep 26 '13 at 13:36

Kermit

33,827
13
85
121

@harifx, my tblbillingsched.duedate data type is date only. – Sishan Sep 26 '13 at 14:10
This is your 999th answer :D The next answer that you post will be your 1000th – tckmn Sep 30 '13 at 12:56
@Doorknob [Here's #1000](http://stackoverflow.com/questions/19095318/row-number-over-where-rownumber-between) – Kermit Sep 30 '13 at 13:51

score 0 · Answer 2 · edited Sep 26 '13 at 13:50

Your first join is wrong. In the table tblReservation the foreignkey is accountNum an you make the join with the field reservationnum. The code should be this:

SELECT tblclientinfo.acctnum, 
    `tblclientinfo.fname, 
     tblclientinfo.fname, 
     tblreservation.unitnum, 
     tblbillingsched.billnum, 
     tblbillingsched.duedate, 
     tblbillingsched.monthlyamort, 
     tblbillingsched.totalbalance 
FROM tblclientinfo 
     JOIN tblreservation 
     ON tblclientinfo.acctnum = tblreservation.accountNum 
     JOIN tblbillingsched 
     ON tblreservation.reservationnum = tblbillingsched.accountnum 
WHERE  tblbillingsched.accountnum = 'C0000000021' 
       AND tblbillingsched.duedate BETWEEN '1/1/2014' AND '1/30/2014'

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

Is DueDate a Date time field Try the following and see if it works:

CAST(tblbillingsched.duedate AS DATE) BETWEEN '1/1/2014' AND '1/30/2014'

Check out this post for a more detailed discussion:

how to query Datetime object by date?

edited May 23 '17 at 11:50

Community

1
1

answered Sep 26 '13 at 14:01

harifx

60
7

score 0 · Answer 4 · answered Sep 26 '13 at 14:42

(off topic and probably not the answer but I thought worth noting)

The safer form of this:

tblbillingsched.duedate BETWEEN '1/1/2014' AND '1/30/2014'

is this:

tblbillingsched.duedate >= '20140101' AND 
tblbillingsched.duedate <'20140130'

Stating a date like this '20140130' is unmistakable.

SQL Server Joining 3 Tables

4 Answers4