Databases
StackOverflow2013
Postsdbo
POonly want one row when doing inner join in one-to-many relationship
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POonly want one row when doing inner join in one-to-many relationship
    primarykey
    data
    text
    <p>I'm sure they're a simple answer to my problem.</p> <p>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.</p> <p>I tried select distinct but got the same results. My query is:</p> <pre><code>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 </code></pre> <p>How can I just have the query return only one row regardless of how many Order_Line records there are per order?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKim Jones
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POonly want one row when doing inner join in one-to-many relationship
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POonly want one row when doing inner join in one-to-many relationship
      1. This table or related slice is empty.
      1. VTUpMod
    1. COif 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?
      singulars
      1. POonly want one row when doing inner join in one-to-many relationship
      1. USIłya Bursov
    2. COwhat data you need from orderline? Why you need to join on that?
      singulars
      1. POonly want one row when doing inner join in one-to-many relationship
      1. USAnup Agrawal
    3. COsay 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.
      singulars
      1. POonly want one row when doing inner join in one-to-many relationship
      1. USKim Jones
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload