Solved

SQL query returns too many rows

Posted on 2000-03-26
17
433 Views
Last Modified: 2008-02-01
Hi!

I have three tables: orders, salesmen and customers.

In orders table are SalesmanGroup and SalesmanId,
CustomerGroup and CustomerId fields but not
SalesmanName or CustomerName.

I wish to create a query that returns all fields from
orders but also SalesmanName and CustomerName, which
reside in the salesmen and customers tables.

This query returns three rows for each row in orders.
Not good. What's wrong? How do I fix it?


SELECT
            orders.*,
            salesmen.name AS salesmanname
            customers.name AS customername
FROM
            orders,
            salesmen,
            customers
WHERE
            orders.date >= '1999-01-01' AND
            orders.date <= '1999-01-07' AND
            orders.category = 3 AND
            salesmen.salesgroup = orders.salesgroup AND
            salesmen.salesmanid = orders.salesmanid AND
            customer.customersgroup = orders.customersgroup AND
            customer.customerid = orders.customerid
ORDER BY
            date,
            time


Thank you!

Ture Magnusson
ture@turedata.se
Karlstad, Sweden
0
Comment
Question by:ture
  • 4
  • 3
  • 2
  • +7
17 Comments
 
LVL 1

Expert Comment

by:ndb
Comment Utility
Two possible solutions:
You might have to use SELECT DISTINCT
Or you have to make a Left Outer join.
But the syntax depends of the database you r using.
0
 
LVL 9

Expert Comment

by:david_levine
Comment Utility
Not sure why you are getting 3 rows back. Looks like everything is fully qualified.

Just wanted to note that if orders.date is a datetime column, then you probably want to specify:
orders.date > '1999-01-01' and
orders.date < '1999-01-08' and...

A datetime field always has a time associated to it so the above will give all orders for dates between 1/1/99 and 1/7/99 inclusive.

David
0
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
I have had this problem as well when I begin to select multiple columns from multiple tables.  The workaround I perform is create a view for each case.  For example:
create view table1
as select orders.* from ......

create view table2
as select salesmen.name from .....

create view table3
as select customers.name from .....

If you can break down the queries into smaller more manageable information you can usually get what you need easier.  Just an idea.
0
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
Also as ndb was saying, by the looks of your case, it seems you should be able to perform a join to create a single table from which to perform your query by performing a join between orders and salesmen:
(orders join salesman on salesmanid = id) join customers on customerid = id
0
 
LVL 3

Expert Comment

by:ghimireniraj
Comment Utility
>>>all rows from orders

Create LEFT OUTER JOIN     with the left table as order and

use DISTINCT   well this is already said by NDB   i think this is what youyr question asks
0
 
LVL 22

Author Comment

by:ture
Comment Utility
Adjusted points from 100 to 200
0
 
LVL 22

Author Comment

by:ture
Comment Utility
I don't get it. I'm kind of an SQL beginner, but I thought this would be an easy one... Please help me again.

How do I use LEFT OUTER JOIN (or whatever) to join the orders table to the salesmen table on BOTH SalesGroup AND SalesmanId and to the customers table on BOTH CustomerGroup AND CustomerId.

There are several salesmen with same SalesmanId, but they are in different SalesGroups.

There can be several customers with same customerid, but they are in different customergroups.

Please be as specific as possible in your answer.

/Ture
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
In your LEFT OUTER JOIN.. add a DISTINCT keyword to get the first matching Salesman number only. The ideal situation would be to pass the Group numbers as criterea.. then you could get the exact record you want.. <smile>.

From MSDN:

--------------------------------------
Using Outer Joins

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join

Microsoft® SQL Server™ uses these SQL-92 keywords for outer joins specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN
RIGHT OUTER JOIN or RIGHT JOIN
FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins.

Using Left Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson).

To include all authors in the results, regardless of whether a publisher is located in the same city, use an SQL-92 left outer join. Here is the query and results of the Transact-SQL left outer join:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a LEFT OUTER JOIN publishers p

    ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC


Here is the result set:

au_fname             au_lname                       pub_name                                

-------------------- ------------------------------ -----------------

Reginald             Blotchet-Halls                 NULL

Michel               DeFrance                       NULL

Innes                del Castillo                   NULL

Ann                  Dull                           NULL

Marjorie             Green                          NULL

Morningstar          Greene                         NULL

Burt                 Gringlesby                     NULL

Sheryl               Hunter                         NULL

Livia                Karsen                         NULL

Charlene             Locksley                       NULL

Stearns              MacFeather                     NULL

Heather              McBadden                       NULL

Michael              O'Leary                        NULL

Sylvia               Panteley                       NULL

Albert               Ringer                         NULL

Anne                 Ringer                         NULL

Meander              Smith                          NULL

Dean                 Straight                       NULL

Dirk                 Stringer                       NULL

Johnson              White                          NULL

Akiko                Yokomoto                       NULL

Abraham              Bennet                         Algodata Infosystems

Cheryl               Carson                         Algodata Infosystems

(23 row(s) affected)

The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column.

Using Right Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities where a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

To include all publishers in the results, regardless of whether a city has a publisher located in the same city, use an SQL-92 right outer join. Here is the Transact-SQL query and results of the right outer join:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors AS a RIGHT OUTER JOIN publishers AS p
    ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

 

Here is the result set:

au_fname             au_lname                 pub_name          

-------------------- ------------------------ ----------

Abraham              Bennet                   Algodata Infosystems

Cheryl               Carson                   Algodata Infosystems

NULL                 NULL                     Binnet & Hardley

NULL                 NULL                     Five Lakes Publishing

NULL                 NULL                     GGG&G

NULL                 NULL                     Lucerne Publishing

NULL                 NULL                     New Moon Books

NULL                 NULL                     Ramona Publishers

NULL                 NULL                     Scootney Books

(9 row(s) affected)

An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold less than 50 copies:

USE pubs

SELECT s.stor_id, s.qty, t.title

FROM sales s RIGHT OUTER JOIN titles t

    ON s.title_id = t.title_id

    AND s.qty > 50

ORDER BY s.stor_id ASC
 

Here is the result set:

stor_id qty    title                                                                          
------- ------ ---------------------------------------------------------

(null) (null) But Is It User Friendly?                                                        

(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior

                Variations                  

(null) (null) Cooking with Computers: Surreptitious Balance Sheets                            

(null) (null) Emotional Security: A New Algorithm                                              

(null) (null) Fifty Years in Buckingham Palace Kitchens                                        

7066   75     Is Anger the Enemy?                                                              

(null) (null) Life Without Fear                                                                

(null) (null) Net Etiquette                                                                    

(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the

                Mediterranean                  

(null) (null) Prolonged Data Deprivation: Four Case Studies                                    

(null) (null) Secrets of Silicon Valley                                                        

(null) (null) Silicon Valley Gastronomic Treats                                                

(null) (null) Straight Talk About Computers                                                    

(null) (null) Sushi, Anyone?                                                                  

(null) (null) The Busy Executive's Database Guide                                              

(null) (null) The Gourmet Microwave                                                            

(null) (null) The Psychology of Computer Cooking                                              

(null) (null) You Can Combat Computer Stress!                                                  

 

(18 row(s) affected)

 

For more information about predicates, see WHERE.

Using Full Outer Joins
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft® SQL Server™ provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.

To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. Here is the query and results of the Transact-SQL full outer join:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a FULL OUTER JOIN publishers p
    ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

 

Here is the result set:

au_fname             au_lname                     pub_name      

-------------------- ---------------------------- --------------------

Reginald             Blotchet-Halls               NULL

Michel               DeFrance                     NULL

Innes                del Castillo                 NULL

Ann                  Dull                         NULL

Marjorie             Green                        NULL

Morningstar          Greene                       NULL

Burt                 Gringlesby                   NULL

Sheryl               Hunter                       NULL

Livia                Karsen                       NULL

Charlene             Locksley                     NULL

Stearns              MacFeather                   NULL

Heather              McBadden                     NULL

Michael              O'Leary                      NULL

Sylvia               Panteley                     NULL

Albert               Ringer                       NULL

Anne                 Ringer                       NULL

Meander              Smith                        NULL

Dean                 Straight                     NULL

Dirk                 Stringer                     NULL

Johnson              White                        NULL

Akiko                Yokomoto                     NULL

Abraham              Bennet                       Algodata Infosystems

Cheryl               Carson                       Algodata Infosystems

NULL                 NULL                         Binnet & Hardley

NULL                 NULL                         Five Lakes Publishing

NULL                 NULL                         GGG&G

NULL                 NULL                         Lucerne Publishing

NULL                 NULL                         New Moon Books

NULL                 NULL                         Ramona Publishers

NULL                 NULL                         Scootney Books

(30 row(s) affected)
 
(c) 1988-98 Microsoft Corporation. All Rights Reserved.

--------------------------------------
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 4

Expert Comment

by:wileecoy
Comment Utility
ture, here is a shorter answer.  May seem too short - let me know if you have any questions.

I simulated what you were trying to do by creating my own database tables in Access.  The following is what I came up with that appeared to work.



SELECT DISTINCTROW
            orders.*,
            salesmen.name AS salesmanname
            customers.name AS customername
FROM
            salesmen,
WHERE
            orders.date >= '1999-01-01' AND
            orders.date <= '1999-01-07' AND
            orders.category = 3 AND
            salesmen.salesgroup = orders.salesgroup
            customer.customersgroup = orders.customersgroup

INNER JOIN(Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID) ON Salesman.SalesmanID = Orders.SalesmanID

ORDER BY
            date,
            time



I incorporated both the "DistinctRow" and the "Inner Join" that others above suggested.

Please let me know if this works with your specific database.
0
 
LVL 6

Accepted Solution

by:
crsankar earned 200 total points
Comment Utility
I see absolutely no reason why you should be using an outer join in this case. The problem is not because you are not using an Outer Join. That is for sure. The query that you have given here is perfect. It has to work. I strongly believe that you need to check the data in you tables further.

GOOD LUCK!
0
 
LVL 4

Expert Comment

by:wileecoy
Comment Utility
Just an additional note that may help.

One way that I test most of my more complex SQL statemtents is to post the statement into an Access query and see if it brings up the expected results.

I also create my SQL statements by creating the desired results through an Access query and then changing the view to show the SQL statement and simply cut and paste it into my VB application.

Hope this helps.  Again, let me know if you have any questions or the above suggestions isn't what you were looking for.

Thanks.
0
 

Expert Comment

by:rbsubra
Comment Utility
hi,

which database ur working into, this sql should works with MS SQL SERVER

select orders.*,
       salesman.name as 'salesmanname',
       customers.name as 'customername'
FROM
       orders
left join
      salesman
on
  ( salesman.salesgroup = orders.salesgroup  and
    salesman.salesmanid = orders.salesmanid
   )
left join
     customers
on
  ( customer.customersgroup = orders.customeresgroup  and
    salesman.customerid = orders.customerid
   )
where
       orders.date >= '1999-01-01' AND
       orders.date <= '1999-01-07' AND
      orders.category = 3




0
 
LVL 4

Expert Comment

by:wileecoy
Comment Utility
crsankar,

You are exactly correct when you say that there is no reason for an OUTER JOIN - it is an INNER JOIN.

Thanks for helping me clarify, as I mentioned that I incorporated what the others had suggested.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I think that for every order there is exactly one salesmen and exactly one customer. If this is the case, then the following must work (no left/outer joins are required).
SELECT
            o.*,
            s.name AS salesmanname
            c.name AS customername
FROM
            orders  o
JOIN        salesmen s
on          s.salesgroup = o.salesgroup
AND         s.salesmanid = o.salesmanid JOIN        customers c
ON          c.customersgroup = o.customersgroup
AND         c.customerid = o.customerid
WHERE
            DateDiff ( 'd', o.date ,  '1999-01-01' ) >= 0
AND         DateDiff ( 'd', o.date , '1999-01-07' ) <= 0
AND         o.category = 3
ORDER BY
            o.date,
            o.time

If in your tables however you have multiple salesmen or customers with the same id's, then the behaviour of your query is not a problem of your query, but of your data.

Ah yes, i use DateDiff rather than <=> comparisons, as normally this never raises problems (If you are sure, you may stay with your solution, no problem)
0
 
LVL 22

Author Comment

by:ture
Comment Utility
Hi all!

Thank you for all your posts.

It's always hard to know who to award, but this time it will be crsankar who gets the points. His simple answer was correct.

His writing "The query that you have given here is perfect. It has to work." makes me feel very good as a newbie SQLer.

His recommendation "I strongly believe that you need to check the data in you tables further." made me look at my data once more.

The real problem was:
I had another field (district) in both the 'Orders' table and 'SalesMen' and 'Customers' table.

The same SalesManId and SalesGroup can appear in several districts. The same is true for customers.

I changed my query to this, and it works! I'm not sure if DISTINCT is really necessary, but it doesn't seem to hurt:

SELECT DISTINCT
            orders.*,
            salesmen.name AS salesmanname
            customers.name AS customername
FROM
            orders,
            salesmen,
            customers
WHERE
            orders.date >= '1999-01-01' AND
            orders.date <= '1999-01-07' AND
            orders.category = 3 AND
            salesmen.district = orders.district AND
            salesmen.salesgroup = orders.salesgroup AND
            salesmen.salesmanid = orders.salesmanid AND
            customer.district = orders.district AND
            customer.customersgroup = orders.customersgroup AND
            customer.customerid = orders.customerid
ORDER BY
            date,
            time

Thank you all again!

/Ture
0
 
LVL 22

Author Comment

by:ture
Comment Utility
Thank you, crsankar!

/Ture
0
 
LVL 6

Expert Comment

by:crsankar
Comment Utility
Hi,

Happy to know that you have solved the problem. Please note that there is no need to use a distinct in this case and will be an unnecessarry burden on the system.

Rgds,
crsankar
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now