3

I'm sure they're a simple answer to my problem.

I have 2 tables -- an order header table (one row for each order, called "Order") and an order detail table (1 or more rows per each order header, called "Order_Line"). I'm using Sql Server2005 and their SSRS reporting tool. I only want to get ONE row back but I'm getting however many rows there are in the detail table for each order. I need to join to the detail table in order to get the values I want to show with the other order header level items.

I tried select distinct but got the same results. My query is:

select o.order_num, o.cust_num, c.cust_desc, o.shipto_num, o.st_name, o.st_address1, o.ent_date, 
o.ord_date, o.req_date, ol.order_line, ol.seq_num, u.user_desc, ol.orig_inv_num, ol.prod_num,
p.prod_desc1, ol.prod_desc1, ol.prod_desc2, ol.grs_price, ol.disc_pct, ol.net_price, ol.ord_qty,
ol.opn_qty, ol.pck_qty, ol.bo_qty, ol.shp_qty, rs.rsn_code_desc, ol.supp_cde, ol.line_ctr,
v.vend_desc, ca.pline_desc, ca.major_grp, ol.major_grp, w.whse_desc, o.wb,s.ship_via_desc,       
sa.slsm_desc, t.ar_term_desc,
o.shp_date, o.sys_num, o.inv_Flag, o.inv_num, o.tot_ord_$,o.inv_date, o.ord_src, r.route_desc,
o.cred_type, o.cred_num, o.total_sale
from [order] o
left outer join order_line ol on o.order_num = ol.order_num
left outer join customer c on o.cust_num = c.cust_no
left outer join ship_via s on o.ship_via_num = s.ship_via_id
left outer join whse_addr w on o.shp_whse = w.whse_num
left outer join salesman sa on o.slsm_num = sa.slsm_num
left outer join terms t on o.ar_term_num = t.ar_term_num
left outer join [route] r on o.route_num = r.route_num
left outer join product p on ol.prod_num = p.prod_num
left outer join prc_ov_rsn rs on ol.prc_ov_rsn_code = rs.rsn_cod
left outer join vend v on ol.prim_vend_num = v.vend_num
left outer join [user_id] u on ol.[user_id] = u.[user_num] 
left outer join category ca on ol.pline_Num = ca.pline_Id
where o.cust_num = '606797'
order by o.cust_num, o.shipto_num, o.order_num

How can I just have the query return only one row regardless of how many Order_Line records there are per order?

Kim Jones
  • 123
  • 1
  • 6
  • 15
  • 2
    if you have 2 orders, you query returns 2 rows, it is ok, then you want to display only one, which one you're planning to display? – Iłya Bursov Oct 15 '13 at 22:22
  • what data you need from orderline? Why you need to join on that? – Anup Agrawal Oct 15 '13 at 22:26
  • say I have just one order, that has 5 details associated with it. My query returns 5 records and I only want one single record returned. I'm only want to show header information but all the info I want is not contained in the order header, some is at the line item level, which is why I need to link to it. – Kim Jones Oct 15 '13 at 22:28
  • I need items such as the product line description, and the vendor name -- both are stored at the on the order detail and not the header. – Kim Jones Oct 15 '13 at 22:30
  • You need to change your DB Structure. For now try something like this remove all the unnecessary orderline columns from select clause: `SELECT DISTINCT o.order_num, o.cust_num, c.cust_desc, o.shipto_num, o.st_name, o.st_address1, o.ent_date, o.ord_date, o.req_date, ol.prod_desc1, ol.prod_desc2, o.shp_date, o.sys_num, o.inv_Flag, o.inv_num, o.tot_ord_$,o.inv_date, o.ord_src, o.cred_type, o.cred_num, o.total_sale FROM ` – Anup Agrawal Oct 15 '13 at 22:40
  • Is there any other tables besides orderline you joined have 1 to many relationship? – Anup Agrawal Oct 15 '13 at 22:43
  • Anup, I tried select distinct and still got 1 row for every detail row on an order. – Kim Jones Oct 15 '13 at 22:46
  • Anup, all the other tables should have just unique rows. – Kim Jones Oct 15 '13 at 22:49
  • Not clear. are you saying you want Order No, Line1LineNo Line1description, .. , Line2LineNo, Line2Description, ... ,Line1567LineNo, Line1567Description,... etc? – Tony Hopkinson Oct 15 '13 at 22:58
  • what I want is just one single returned row per order, but I'm getting the a row (with the same exact data on each row) for every row the order detail contains for that order (order_line). So, if order # 1234 has 18 order_line records, I'm getting 18 rows returned that are all duplicate. Might be confusing since my original sql statement did have me selecting things like product #, product description, etc. I really do not need those fields, they just were in the original query I had written. I DO need to link to order_line to get values from 3-4 fields so I can get data from other tables – Kim Jones Oct 16 '13 at 19:52

1 Answers1

0

I'll try and answer this from a general SQL perspective, because I don't know SQL Server itself. Hopefully it makes sense, but let me know if I'm completely off and I'll kill this answer.

One way you can try to solve this is by creating a dependent subquery in which you use LIMIT 1. As a result the join will ultimately match up with only one order detail row. Like so:

select ...all columns you want...
from [order] o
left outer join order_line ol on o.order_num = ol.order_num
left outer join customer c on o.cust_num = c.cust_no
left outer join ship_via s on o.ship_via_num = s.ship_via_id
left outer join whse_addr w on o.shp_whse = w.whse_num
left outer join salesman sa on o.slsm_num = sa.slsm_num
left outer join terms t on o.ar_term_num = t.ar_term_num
left outer join [route] r on o.route_num = r.route_num
left outer join product p on ol.prod_num = p.prod_num
left outer join prc_ov_rsn rs on ol.prc_ov_rsn_code = rs.rsn_cod
left outer join vend v on ol.prim_vend_num = v.vend_num
left outer join [user_id] u on ol.[user_id] = u.[user_num] 
left outer join category ca on ol.pline_Num = ca.pline_Id
where o.cust_num = '606797' AND order_line.order_num =
    (SELECT order_num 
     FROM order_line 
     WHERE order_line.order_num = order.order_num LIMIT 1)
order by o.cust_num, o.shipto_num, o.order_num

The result is you'll get each order row individually along with the columns you selected from the order_line table, but since you've limited it to 1 order_line row, you should only get one row per order in your result set.

With proper indexing and such, this hopefully won't cause any significant additional load times. Let me know if this makes sense.

Note: Apparently SQL Server does not have a limit clause like that which you find in Oracle, MySQL, PostgreSQL, SQLite...etc etc. I've found references to something that looks like SELECT TOP 1..., but I do not know whether that is the equivalent. Anybody reading this searching for a similar answer, obviously you'll have to substitute the LIMIT 1 with whatever is syntactically correct in that particular DBMS. And let me know the correct way to express a row count limitation.

Paul Richter
  • 10,414
  • 8
  • 47
  • 77
  • Hi Teeg, I"m getting an error message about the term "limit". I have this as the current query (had to change order.order_num to "o.order_num" and "order_line" to "ol"). – Kim Jones Oct 15 '13 at 22:55
  • (select columns, had to remove, too long) from [order] o left outer join customer c on o.cust_num = c.cust_no left outer join ship_via s on o.ship_via_num = s.ship_via_id left outer join whse_addr w on o.shp_whse = w.whse_num left outer join salesman sa on o.slsm_num = sa.slsm_num left outer join terms t on o.ar_term_num = t.ar_term_num left outer join [route] r on o.route_num = r.route_num left outer join product p on ol.prod_num = p.prod_num left outer join prc_ov_rsn rs on ol.prc_ov_rsn_code = rs.rsn_cod – Kim Jones Oct 15 '13 at 22:55
  • left outer join vend v on ol.prim_vend_num = v.vend_num left outer join [user_id] u on ol.[user_id] = u.[user_num] left outer join category ca on ol.pline_Num = ca.pline_Id where o.cust_num = '606797' AND order_line.order_num = (SELECT ol.order_num FROM order_line ol WHERE ol.order_num = o.order_num LIMIT 1) order by o.order_num – Kim Jones Oct 15 '13 at 22:56
  • @KimJones Ok. Like I said I do not know SQL Server; does it not have a `LIMIT` clause like mysql or postgres? Do you have to do something like [this](http://stackoverflow.com/a/603746/877472)? – Paul Richter Oct 15 '13 at 23:00
  • from [order] o left outer join order_line ol on o.order_num = ol.order_num left outer join customer c on o.cust_num = c.cust_no left outer join ship_via s on o.ship_via_num = s.ship_via_id left outer join whse_addr w on o.shp_whse = w.whse_num left outer join salesman sa on o.slsm_num = sa.slsm_num left outer join terms t on o.ar_term_num = t.ar_term_num left outer join [route] r on o.route_num = r.route_num left outer join product p on ol.prod_num = p.prod_num – Kim Jones Oct 15 '13 at 23:01
  • left outer join prc_ov_rsn rs on ol.prc_ov_rsn_code = rs.rsn_cod left outer join vend v on ol.prim_vend_num = v.vend_num left outer join [user_id] u on ol.[user_id] = u.[user_num] left outer join category ca on ol.pline_Num = ca.pline_Id where o.cust_num = '606797' AND order_line.order_num = (SELECT order_num FROM order_line WHERE order_line.order_num = order.order_num) order by o.order_num *** and I'm getting an error on this line saying incorrect syntax on "order"??? – Kim Jones Oct 15 '13 at 23:02
  • Teeg, it doesn't appear to have a "limit" clause :( and don't understand the other. Do you know why I'm getting the error on "order" in my 2nd query? – Kim Jones Oct 15 '13 at 23:04
  • @KimJones I'm guessing because, without the `limit` clause or whatever SQL Server has as an equivalent, the subquery will now return more than one row, which can't work when you're doing `=`. You know SQL Server better than I do, so you're going to have to tell me how you limit number of rows in that particular dbms. Does it work if you do `SELECT TOP 1 order_num...` in the subquery? – Paul Richter Oct 15 '13 at 23:12
  • I can't get it to work, it keeps erroring out on the word "order", no matter if I put it in brackets or what. GOing to try again tomorrow, thanks to all for their help! – Kim Jones Oct 15 '13 at 23:49
  • Ok...well anyone who knows how to do the equivalent of a `limit` clause in SQL Server, by all means let me know... – Paul Richter Oct 15 '13 at 23:54
  • I've simplified my query to just 2 Order (header) fields and 1 Order_Line (detail) field: – Kim Jones Oct 16 '13 at 19:59
  • SELECT o.order_num, o.cust_num, ol.prod_num, FROM [ORDER] o WHERE o.cust_num = '606797' AND order_line.order_num = (SELECT order_num FROM order_line WHERE order_line.order_num = [ORDER].order_num) but I am getting the error "Incorrect syntax near the word FROM". Any ideas why I'm getting this error? – Kim Jones Oct 16 '13 at 20:00
  • oops, I see that I have a comma after the last select field. I fixed it but now am getting these errors: "The multi-part identifier "Order_Line.Order_Num" could not be bound", "The multi-part identifier "Order.Order_Num" could not be bound", and "The multi-part identifier "ol.prod_num" could not be bound". Any help? – Kim Jones Oct 16 '13 at 20:04
  • @KimJones That's a very easy problem to figure out if you google the error message, you don't need my help with that. Hint: pay attention to what's in the FROM clause. – Paul Richter Oct 16 '13 at 20:27
  • I've Googled and am my wit's end. No matter what I try nothing is working...can you just tell me what the problem is? – Kim Jones Oct 16 '13 at 20:58
  • @KimJones [This should help](http://aartemiou.blogspot.ca/2009/01/multi-part-identifier-could-not-be.html). – Paul Richter Oct 16 '13 at 21:08
  • I read that but still cannot figure out how to fix this. Can you PLEASE just tell me what the error is? – Kim Jones Oct 16 '13 at 21:32