Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2007-04-25 : 12:07:54
|
| I have 5 tables I need to JOIN together to create my query for displaying counts of sales orders. The 5th & final table however("shipping" table), may potentially contain multiple rows for a single order and this throws off my count. So I'm trying to find out how I can disregard these additional rows.So my query currently looks something like this. The problem I'm having is joining the "Shipping" table. This is the table that may have multiple rows:-----------------------------------------------SELECT c.category_ID as category, c.category_description AS description, COUNT(o.orders_Id) as orderCountFROM orders o INNER JOIN categories c ON c.category_SeqId = o.category_Seqid INNER JOIN table2 t2 on t2.orders_seqId = o.orders_SeqId INNER JOIN table3 t3 ON t3.ords_SeqId = t2.ords_SeqId INNER JOIN shipping s ON s.table3_SeqId = t3.table3_SeqId WHERE s.shipping_DateShipped BETWEEN @startDate AND @endDate AND s.shipping_VoidDate IS NULLGROUP BY c.category_ID, c.category_description-----------------------------------------------------------------Any idea on how to remove these additional rows in the "Shipping" table from my total count? Thanks.-Goalie35 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-25 : 12:25:10
|
| You cannot directly join two transactional tables without first summarizing at least one of them in a derived table.how does Shipping relate to the other tables in your SQL Statement? For example, by OrderID ? if so, you need to summarize Shipping by OrderID first, and then join those results to the rest of your SQL statement.If you want or need more specific help, you need to provide us with your table structure, sample data, and expected results. (as CREATE TABLE and INSERT statements, please, so that we can quickly and accurately assist you).More on derived tables here: http://www.sqlteam.com/item.asp?ItemID=6692by the way --- table3_seqID is perhaps the worst column name I've ever seen!- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
|
|
|
|
|
getting DISTINCT rows from JOIN?