sql server - Complex SQL View with Joins & Where clause

Question

My SQL skill level is pretty basic. I have certainly written some general queries and done some very generic views. But once we get into joins, I am choking to get the results that I want, in the view I am creating.

I feel like I am almost there. Just can't get the final piece

SELECT dbo.ics_supplies.supplies_id,
       dbo.ics_supplies.old_itemid,
       dbo.ics_supplies.itemdescription,
       dbo.ics_supplies.onhand,
       dbo.ics_supplies.reorderlevel,
       dbo.ics_supplies.reorderamt,
       dbo.ics_supplies.unitmeasure,
       dbo.ics_supplies.supplylocation,
       dbo.ics_supplies.invtype,
       dbo.ics_supplies.discontinued,
       dbo.ics_supplies.supply,
       dbo.ics_transactions.requsitionnumber,
       dbo.ics_transactions.openclosed,
       dbo.ics_transactions.transtype,
       dbo.ics_transactions.originaldate
FROM   dbo.ics_supplies
       LEFT OUTER JOIN dbo.ics_orders
                    ON dbo.ics_supplies.supplies_id = dbo.ics_orders.suppliesid
       LEFT OUTER JOIN dbo.ics_transactions
                    ON dbo.ics_orders.requisitionnumber =
                       dbo.ics_transactions.requsitionnumber
WHERE  ( dbo.ics_transactions.transtype = 'PO' ) 

When I don't include the WHERE clause, I get 17,000+ records in my view. That is not correct. It's doing this because we are matching on a 1 to many table. Supplies table is 12,000 records. There should always be 12,000 records. Never more. Never less.

The pieces that I am missing are:

1. I only need ONE matching record from the ICS_Transactions Table. Ideally, the one that I want is the most current 'ICS_Transactions.OriginalDate'.

2. I only want the ICS_Transactions Table fields to populate IF ICS_Transacions.Type = 'PO'. Otherwise, these fields should remain null.

Sample code or anything would help a lot. I have done a lot of research on joins and it's still very confusing to get what I need for results.

EDIT/Update

I feel as if I asked my question in the wrong way, or didn't give a good overall view of what I am asking. For that, I apologize. I am still very new to SQL, but trying hard.

ICS_Supplies Table has 12,810 records ICS_Orders Table has 3,666 records ICS_Transaction Table has 4,701 records

In short, I expect to see a result of 12,810 records. No more and no less. I am trying to create a View of ALL records from the ICS_Supplies table.

Not all records in Supply Table are in Orders and or Transaction Table. But still, I want to see all 12,810 records, regardless.

My users have requested that IF any of these supplies have an open PO (ICS_Transactions.OpenClosed = 'Open' and ICS_Transactions.InvType = 'PO') Then, I also want to see additional fields from ICS_Transactions (ICS_Transactions.OpenClosed, ICS_Transactions.InvType, ICS_Transactions.OriginalDate, ICS_Transactions.RequsitionNumber).

If there are no open PO's for supply record, then these additional fields should be blank/null (regardless to what data is in these added fields, they should display null if they don't meet the criteria).

The ICS_Orders Table is nly needed to hop from the ICS_Supplies to the ICS_Transactions (I first, need to obtain the Requisition Number from the Orders field, if there is one).

I am sorry if I am not doing a good job to explain this. Please ask if you need clarification.

Answer 1

Here's a simplified version of Ross Bush's answer (It removes a join from the CTE to keep things more focussed, speed things up, and cut down the code).

;WITH
  ordered_ics_transactions AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY requisitionnumber
                           ORDER BY originaldate DESC
                      )
                        AS seq_id
  FROM
    dbo.ics_transactions
)
SELECT
  s.supplies_id,      s.old_itemid,
  s.itemdescription,  s.onhand,
  s.reorderlevel,     s.reorderamt,
  s.unitmeasure,      s.supplylocation,
  s.invtype,          s.discontinued,
  s.supply,
  t.requsitionnumber, t.openclosed,
  t.transtype,        t.originaldate
FROM
  dbo.ics_supplies           AS s
LEFT OUTER JOIN
  dbo.ics_orders             AS o
    ON  o.supplies_id = s.suppliesid
LEFT OUTER JOIN
  ordered_ics_transactions   AS t
    ON  t.requisitionnumber = o.requisitionnumber
    AND t.transtype         = 'PO'
    AND t.seq_id            = 1

This will only join the most recent transaction record for each requisitionnumber, and only if it has transtype = 'PO'

IF you want to reverse that (joining only transaction records that have transtype = 'PO', and of those only the most recent one), then move the transtype = 'PO' filter to be a WHERE clause inside the ordered_ics_transactions CTE.