Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Join 2 sql tables on only one record on a one to many relationship

I have 2 tables Items and Itemscost. there is a one to many relationship. how do i write a query such that Items and and ITemscost are joined and I only fetch the minimum cost for each item in the Itemcost table.
0
TrialUser
Asked:
TrialUser
  • 4
  • 2
  • 2
  • +2
3 Solutions
 
Steve WalesSenior Database AdministratorCommented:
This should do the trick, I think (off the top of my head)

select a.item, min(b.cost)
from items a join itemcost b
on a.item = b.item
group by a.item

Open in new window

0
 
TrialUserAuthor Commented:
ok, this seems to be working but i also needed, 2 additional thing - add a couple of filters
and i need the result set to have a column supplierid. since it is not in the group by how do i return them?

select a.itemid, min(b.cost), b.supplierid
from itemslist a join suppliercost b
on a.itemid = b.itemid
group by a.itemid ,a.isavailable
having a.itemid in
(100,
10015,
100090,
10026890,
9444258,
10019751)
and a.IsAvailable=1
0
 
awking00Commented:
select itemid, cost, supplierid from
(select a.itemid, b.cost, b.supplierid,
 row_number() over (partition by a.itemid order by b.cost) rn
 from itemslist a inner join suppliercost b
 on a.itemid = b.itemid) as x
where x.rn = 1;
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
TrialUserAuthor Commented:
awking00 - i need to fetch the min cost record from suppliercost table and not the min row number.  thanks

may be that is what the partition is doing? I am not understanding
0
 
dannygonzalez09Commented:
Yes, that is what the row_number partition is doing here, ordering the values by cost and picking the 1st value i.e, item with min cost and supplier id associated to it
0
 
awking00Commented:
To demonstrate -
SQL> select * from itemslist;

    ITEMID
----------
         1
         2

SQL> select * from suppliercost;
    ITEMID       COST SUPPLIERID
---------- ---------- ----------
         1        100         10
         1        200         11
         1         50         12
         2         75         10
         2         65         13

The inner subquery produces this -
SQL> select a.itemid, b.cost, b.supplierid,
  2   row_number() over (partition by a.itemid order by b.cost) rn
  3   from itemslist a inner join suppliercost b
  4   on a.itemid = b.itemid;

    ITEMID       COST SUPPLIERID         RN
---------- ---------- ---------- ----------
         1         50         12          1
         1        100         10          2
         1        200         11          3
         2         65         13          1
         2         75         10          2

So selecting where the rn = 1 from that subquery will produce this -
         1         50         12          1
         2         65         13          1
0
 
PortletPaulCommented:
>>" i need the result set to have a column supplierid. since it is not in the group by how do i return them?"

why can't supplierid be in the group by?

whilst I absolutely agree with the use of row_number() is a viable solution, the issue is that if 2 or more suppliers all supply an equally low price - what do you do?

row_number() will suppress this possibility as there can only be one number 1 within each partition.
0
 
awking00Commented:
Then use rank instead of row_number -
SQL> select * from suppliercost;

    ITEMID       COST SUPPLIERID
---------- ---------- ----------
         1         50         12
         1        100         10
         1        200         11
         2         65         13
         2         75         10
         2         65         14

6 rows selected.

Elapsed: 00:00:00.04
SQL> select itemid, cost, supplierid from
  2  (select a.itemid, b.cost, b.supplierid,
  3   rank() over (partition by a.itemid order by b.cost) rnk
  4   from itemslist a inner join suppliercost b
  5   on a.itemid = b.itemid)
  6  where rnk = 1
  7  ;

    ITEMID       COST SUPPLIERID
---------- ---------- ----------
         1         50         12
         2         65         14
         2         65         13
0
 
awking00Commented:
Always forgetting the alias required by sql server for subqueries -

SQL> select itemid, cost, supplierid from
  2  (select a.itemid, b.cost, b.supplierid,
  3   rank() over (partition by a.itemid order by b.cost) rnk
  4   from itemslist a inner join suppliercost b
  5   on a.itemid = b.itemid) as x
  6  where rnk = 1;
0
 
PortletPaulCommented:
no criticism implied awking00, really my point was that something may have been overlooked and needs to be considered

and I utterly agree about that darn alias, catches me all too often :)
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now