4 Table SQL Search

[shredded]

I am still learning SQL and I was hoping for some help to add another relationship to my equation.

I would like the SQL to show me all the ItemsIDs that are assigned to selected years (via key) that are originated from Details table layout.

This is working good, but it's showing my ALL ItemsID meeting that year criteria, but I would like to only see __ItemsID based off the layout I am on, which is Details.

I think I need to add FROM "\Details\" and WHERE _fkDetailsID = ? ; Details::__DetailsID, but I am struggling to add in another table!!

Any help would be greatly appreciated to get where I am going or simplify!!

 

 

Details::__DetailsID <-> Items::_fkDetailsID 

                         Items::__ItemsID <-> AssignedYears::_fkItemID

                                              AssignedYears::_fkYearID <-> Years::__YearsID

Let (

[

Key = 8  // YearsID from Dropdown

] ;

ExecuteSQL( "

SELECT \"_fkItemsID\"

FROM \”AssignedYears\” INNER JOIN \”Years\"  

ON \”AssignedYears\ . \"_fkYearsID\" = \"Years\" . \”__YearsID\"

WHERE \”AssignedYears\ . \"_fkYearsID\" = ?

ORDER BY \"_fkYearsID\" ASC "

; " " ; "" ; Key )

    )

 

Edited November 20, 2017 by shredded

[Fitch]

Your graph doesn't match your SQL -- fkItemsID vs. fkItemID , fkyearsID vs. fkyearID -- so first thing is double check the field names are correct.

It doesn't look like you need a join. You're selecting a field from AssignedYears, querying a field from AssignedYears, and sorting by a field in AssignedYears. All same table.