CODEWITHSUNDEEP
sql-server-2012inner-join
Boosted Programmer
Boosted Programmer

Reputation: 33

Greater Than and Less Than Equal to in SQL Server inner join NEED

I am currently developing a system that allows you to loan money. The thing is in order to place a loan you must pass the required or must have greater amount in your deposit in your bank acct.

Here is the schema for the deposit

tbl_fxd_dep:

    fxid | amount
    1      10,000
    2      15,000.01
    3      20,000.01

Here is the schema for the employees amount deposited

tbl_employee:

    empid | amount
    1       15,100.01

as you can see there the amount of the employees deposit is neither equal to 15,000.01 and 20,000.01 but rather in between those amounts

Here is my sql statement

Select 
    empid, fname, mname, lname, st, prv, city, cnt, fxid 
from 
    emp as e
inner join 
    fd as f on e.amount >= f.amount
            and e.amount <= f.amount
where 
    uname = @user and pwd = @pwd

This is an inner join statement that I created the problem is that whenever I try to log in using the users acct and password it does not query the profile of the user I tried removing this part and e.amount <= f.amount the fxid that is pulls up is 1 rather than showing the fxid it should belong to in this case 2 by can somebody provide me a alternative solution to this problem

THANK YOU and appreciate any effort that you guys give...

Upvotes: 2

Views: 12182

Answers (1)

AHiggins
AHiggins

Reputation: 7219

You have a pretty severe logical flaw in your join criteria - namely, since only one row is being considered at any given time, the only value that can be both less than or equal to x and greater than or equal to x is by definition equal to x. That's what marc_s is trying to point out in the comments: the criteria you're using isn't doing what you think it is. You can run through this pretty quickly, since you have a small data set: just substitute the literal value 15000.01 for e.amount and look at the data.

What you appear to want is to get the highest fxid that a given employee qualifies for (side note: why employee and not customer?). You can do that with a query like the following:

SELECT
    empid
   ,fxid = MAX(fxid)
FROM
    emp AS e
    INNER JOIN fd AS f
        ON e.amount <= f.amount
WHERE
    uname = @user AND
    pwd = @pwd
GROUP BY 
    empid

SQL Server is going to take this query and

  1. Select the data for the employee
  2. Connect that data to all values it is eligible for (deposit limit is less than the deposit amount)
  3. Aggregate the data so it only returns one row for the employee, and the biggest fxid that the employee qualifies for.

Upvotes: 2

Related Questions