Being pretty new to SQL, I ask for your patience. I have been banging my head trying to figure out how to create this VIEW by joining 3 tables. I am going to use mock tables, etc to keep this very simple. So that I can try to understand the answer - no just copy and paste.
ICS_Supplies:
Supplies_ ID Item_Description
-------------------------------------
1 | PaperClips
2 | Rubber Bands
3 | Stamps
4 | Staples
ICS_Orders:
ID SuppliesID RequisitionNumber
----------------------------------------------------
1 | 1 | R1234a
6 | 4 | R1234a
2 | 1 | P2345b
3 | 2 | P3456c
4 | 3 | R4567d
5 | 4 | P5678e
ICS_Transactions:
ID RequsitionNumber OrigDate TransType OpenClosed
------------------------------------------------------------------
1 | R1234a | 06/12/20 | Req | Open
2 | P2345b | 07/09/20 | PO | Open
3 | P3456c | 07/14/20 | PO | Closed
4 | R4567d | 08/22/20 | Req | Open
5 | P5678e | 11/11/20 | PO | Open
And this is what I want to see in my View Results
Supplies_ID Item RequsitionNumber OriginalDate TransType OpenClosed
---------------------------------------------------------------------------------------
1 | Paper Clips | P2345b | 07/09/20 | PO | OPEN
2 | Rubber Bands | Null | Null | Null | Null
3 | Stamps | Null | Null | Null | Null
4 | Staples | P56783 | 11/11/20 | PO | OPEN
I just can't get there. I want to always have the same amount of records that we have in the ICS_Supplies Table. I need to join to the ICS_Orders Table in order to grab the Requisition Number because that's what I need to join on the ICS_Transactions Table. I don't want to see data in the new added fields UNLESS ICS_Transactions.TransType = 'PO' AND ICS_Transactions.OpenClosed = 'OPEN', otherwise the joined fields should be seen as null, regardless to what they contain. IF that is possible?
My research shows this is probably a LEFT Join, which is very new to me. I had made many attempts on my own, and then posted my question yesterday. But I was struggling to ask the correct question and it was recommended by other members that I post the question again . .
If needed, I can share what I have done, but I fear it will make things overly confusing as I was going in the wrong direction.
I am adding a link to the original question, for those that need some background info
If there is any additional information needed, just ask. I do apologize in advance if I have left out any needed details.
This is a bit tricky, because you want to exclude rows in the second table depending on whether there is a match in the third table - so two left joins are not what you are after.
I think this implements the logic you want:
select s.supplies_id, s.item_description,
t.requisition_number, t.original_date, t.trans_type, t.open_closed
from ics_supplies s
left join ics_transaction t
on t.transtype = 'PO'
and t.open_closed = 'Open'
and exists (
select 1
from ics_order o
where o.supplies_id = s.supplies_id and o.requisition_number = t.requisition_number
)
Another way to phrase this would be an inner join in a subquery, then a left join:
select s.supplies_id, s.item_description,
t.requisition_number, t.original_date, t.trans_type, t.open_closed
from ics_supplies s
left join (
select o.supplies_id, t.*
from ics_order o
inner join ics_transaction t
on t.requisition_number = o.requisition_number
where t.transtype = 'PO' and t.open_closed = 'Open'
) t on t.supplies_id = s.supplies_id