join - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

join

Author

Topic

titsiros
Starting Member

2 Posts

Posted - 2012-02-24 : 15:43:38

I have two tables which I am trying to join, one holding pricing and another invoices.

table1

type|minqty|maxqty|date|price
A|1|6|02/24/2012|123
A|3|5|02/25/2012|136

table2

invoice|type|qty
123456|A|2
456789|A|3

I join them using :

select invoice,type,qty,price
from table1 inner join
table2 on table1.type=table2.type and qty between minqty and maxqty

But I get a total of 3 records, I would like to only get 2 (one for each invoice). The rule is that if more than one pricing record qualifies then I wish to only get the one with the largest date.

The desired resultset should be:

invoice|type|qty|date|price
123456 |A |2|02/24/2012|123
456789 |A |3|02/25/2012|136

Any idea on how to get rid of the duplicate record for invoice 456789?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-24 : 15:48:29

That really isn't a valid relationship

Do you have the DDL, including primary keys for these tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 11:43:34

quote:
Originally posted by titsiros

I have two tables which I am trying to join, one holding pricing and another invoices.

table1

type|minqty|maxqty|date|price
A|1|6|02/24/2012|123
A|3|5|02/25/2012|136

table2

invoice|type|qty
123456|A|2
456789|A|3

I join them using :

select invoice,type,qty,price
from table1 inner join
table2 on table1.type=table2.type and qty between minqty and maxqty

But I get a total of 3 records, I would like to only get 2 (one for each invoice). The rule is that if more than one pricing record qualifies then I wish to only get the one with the largest date.

The desired resultset should be:

invoice|type|qty|date|price
123456 |A |2|02/24/2012|123
456789 |A |3|02/25/2012|136

Any idea on how to get rid of the duplicate record for invoice 456789?


SELECT t1.invoice,t1.type,t1.qty,
t2.date,t2.price
FROM table2 t1
CROSS APPLY (SELECT TOP 1 date,price
             FROM table1
             WHERE type=t1.type
             AND t1.qty BETWEEN minqty AND maxqty
             ORDER date DESC
             )t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

titsiros
Starting Member

2 Posts

Posted - 2012-02-27 : 18:02:55

thanks so much visakh16!

I was able to find an alternative solution using Rank() over partition and only selecting the first record BUT your sql seems to be much faster than mine...

SELECT * FROM (
SELECT t1.invoice,t1.type,t1.qty,
t2.date,t2.price,RANK() OVER( PARTION BY t1.invoice,t1.type ORDER BY t2.date desc) AS ranking
FROM table1 INNER JOIN
table2 ON table1.type=table2.type AND qty BETWEEN minqty AND maxqty
) AS RANKED WHERE RANKING=1

I guess it works both ways..?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-27 : 19:24:34

welcome
it will work both ways

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources