CODEWITHSUNDEEP
sql-server
Roshan
Roshan

Reputation: 297

SQL Server query returns rows more than expected

I have a sql server query that returns rows more than I expected:

select 
   b.isbn, l.lend_no, s.first_name
from 
   dbo.books b, dbo.lending l, dbo.students s 
where 
   (l.act between '4/16/2013' and '4/16/2013') 
   and (l.stat ='close')`

I want to do is get the isbn, lend_no and student name that book returned date is between given dates and lend status is closed , my lending table has only 2 lending that returned on given date but query give me 304 rows

Upvotes: 1

Views: 586

Answers (3)

Mike D.
Mike D.

Reputation: 4104

Your FROM clause isn't doing what you expect it to. By specifying the tables that way you are doing a full join which is giving you a cartesian product. You need to be using the proper table join syntax.

This is a great explanation of table joins: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Upvotes: 0

marc_s
marc_s

Reputation: 754953

You're not definining any join conditions between the tables, so you'll get a cartesian product.

Try something like this instead:

SELECT
   b.isbn, l.lend_no, s.first_name
FROM
   dbo.books b
INNER JOIN
   dbo.lending l ON l.Book_id = b.Book_id   -- just guessing here
INNER JOIN
   dbo.students s ON l.student_id = s.student_id   -- just guessing here
WHERE
   l.act BETWEEN '20130416' AND '20130416' 
   AND l.stat = 'close'

Define the join conditions as needed - I don't know your tables, you'll have to find out what columns link the two tables respectively.

I also used the proper ANSI JOIN syntax - don't just list a bunch of tables separated by a comma, that's been kicked out of the SQL standards over 20 years ago (SQL 1992).

Also: I would always use the ISO-8601 date format YYYYMMDD to be safe - this is the only format that works on all versions of SQL Server and with all language, regional and dateformat settings.

Upvotes: 2

John Woo
John Woo

Reputation: 263803

Your current query gets the cartesian product from the three tables causing to retrieve unexpected result. You need to define the relationship or how the tables should be join, example

select b.isbn, l.lend_no, s.first_name
from    dbo.books b
        INNER JOIN dbo.lending l
            ON c.Colname = l.ColName -- << define condition here
        INNER JOIN dbo.students s 
            ON ......                -- << define condition here
where   l.act between '4/16/2013' and '4/16/2013' and 
        l.stat ='close'

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 3

Related Questions