Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-04-20
10
Medium Priority
?
324 Views
Last Modified: 2014-05-20
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
Comment
Question by:TrialUser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 668 total points
ID: 40011991
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
 

Author Comment

by:TrialUser
ID: 40012335
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1332 total points
ID: 40012427
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:TrialUser
ID: 40012471
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
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 40012610
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1332 total points
ID: 40012642
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40014206
>>" 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
 
LVL 32

Expert Comment

by:awking00
ID: 40014690
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
 
LVL 32

Expert Comment

by:awking00
ID: 40014698
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40014914
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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