0

I'm trying to retrieve data from three different tables.

Table1KIT_CONT

  • holds kit unique id, kit content part numbers and kit revision among others.
KIT_CONT_ID KIT_CONT_PN KIT_CONT_REV KIT_CONT_QTY
1 ABC 1 2
1 DEF 1 1
2 GHI 1 3
2 ABC 1 4
3 ABC 2 5

...

Table2KIT_PN

  • holds Kit Unique id, Kit Part number and kit revision among others
KIT_ID KIT_PN KIT_REV
1 KIT-1 1
2 KIT-2 1
3 KIT-1 2
4 KIT-3 1

...

Table3PN_PRICE

  • holds Part Number, Part Price, Pricelist rev among others
PN PN_PRICE PRICELIST_REV
ABC 500 1
DEF 700 1
GHI 900 1
ABC 550 2
DEF 750 2
GHI 950 2

...

I need a query that returns:

KIT_PN KIT_CONT_PN KIT_CONT_QTY KIT_REV PN_PRICE PRICELIST_REV

join all KIT_CONT PN to the KIT_PN ON ID number where kit rev is the highest for each kit join the PN_Price on PN = KIT_CONT_PN where pricelist rev is highest for each PN

Select

A.KIT_ID
A.KIT_PN,

B.KIT_CONT_PN,
B.KIT_CONT_QTY,

C.PN_PRICE,
C.PN_PRICELIST_REV

FROM Table2KIT_PN   A WITH (NOLOCK) 
left JOIN 
Table1KIT_CONT          B WITH (NOLOCK)

ON A.KIT_ID = B.KIT_CONT_ID

left JOIN 
Table3PN_PRICE          C WITH (NOLOCK)

On B.KIT_CONT_PN = C.PN

and B.KIT_CONT_REV = (Select Max(B.KIT_CONT_REV) FROM Table1KIT_CONT D where B.KIT_CONT_REV = D.KIT_CONT_REV )

This does not work at all. And I haven't even got to the part of only retrieving the max PN_PRICE.

I have tried multiple versions of queries found in this link, but cannot seem to wrap all of this info to my case.

Any help is much appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Cato.Fuglestad
  • 1
  • 1
  • 1
    As an aside, unless you are fully aware of the implications on using `nolock` and have a specific need to do so, you really shouldn't. – Dale K Jan 25 '23 at 08:14
  • Thanks for the heads up on this. I adapted the practice from other persons without knowing the full implications. Im fully self-thought on sql query and is only using it for data retrieval. – Cato.Fuglestad Jan 25 '23 at 13:56

1 Answers1

1

as you use nolock (even if not adiviseable, I assume your answer needs MSSQL). So, you can write something like the folloging:

;with t1cont as (
  select 
    *
    ,row_number() over(partition by 
                          kit_cont_id
                          ,kit_cont_pn 
                       order by kit_cont_rev) rn
  from Table1KIT_CONT t1cont
)
select 
  (whatver fields you need)
from t1cont
outer apply (
  select top 1 *
  from Table2KIT_PN    t2kpn
  where t1cont.kit_cont_id = t2kpn.kit_id
  order by kit_cont_rev desc
) t2kpn
outer apply (
  select top 1 *
  from Table3PN_PRICE  t3pnp
  where t1cont.kit_cont_pn = t3pnp.pn
  order by pricelist_rev desc
) t3pnp
where rn=1

I did not test it but it should be fine. Adjust it for you needs (fields / filters)

Dumitrescu Bogdan
  • 7,127
  • 2
  • 23
  • 31
  • Thank you for the suggested answer. I applied it like you wrote it but it returns all 5000 lines of table 1 (all revisions) I even tried removing the reference to the price table and only join the one kit pn table. same result. I have removed the nolock – Cato.Fuglestad Jan 25 '23 at 13:58
  • The query basically takes T1 and joins it with the latest revisions of T2 and the latest revisions of T3. If this is not what is needed .. try to explain better which is the target. – Dumitrescu Bogdan Jan 25 '23 at 18:23
  • Changed the code to assume T1 PK(kit_cont_id,kit_cont_pn) – Dumitrescu Bogdan Jan 25 '23 at 18:31
  • Op tagged SQL server - so no need to infer from nolock – Dale K Jan 25 '23 at 19:08