What's the best way to retrieve this data?

Question

The architecture for this scenario is as follows:

I have a table of items and several tables of forms. Rather than having the forms own the items, the items own the forms. This is because one item can be on several forms (although only one of each type, but not necessarily on any). The forms and items are all tied together by a common OrderId. This can be represented like so:

OrderItems | Form A   | Form B etc....
---------- |--------- |
ItemId     |FormAId   |
OrderId    |OrderId   |
FormAId    |SomeField |
FormBId    |OtherVar  |
FormCId    |etc...

This works just fine for these forms. However, there is another form, (say, FormX) which cannot have an OrderId because it consists of items from multiple orders. OrderItems does contain a column for FormXId as well, but I'm confused about the best way to get a list of the "FormX"s related to a single OrderId. I'm using MySQL and was thinking maybe a stored proc was the best way to go on this, but I've never used a stored proc on MySQL and don't really know the best way to go about it. My other (kludgy) option was to hit the DB twice, first to get all the items that are for the given OrderId that also have a FormXId, and then get all their FormXIds and do a dynamic SELECT statement where I do something like (pseudocode)

SELECT whatever FROM sometable WHERE FormXId=x OR FormXId=y....

Obviously this is less than ideal, but I can't really think of any other way... anything better I could do either programmatically or architecturally? My back-end code is ASP.NET.

Thanks so much!

UPDATE

In response to the request for more info:

Sample input:

OrderId = 1000

Sample output

FormXs:
-----------------
FormXId | FieldA | FieldB | etc
-------------------------------
1003    | value  | value  | ...
1020    | ... .. ..
1234    | .. . .. . . ... 

You see the problem is that FormX doesn't have one single OrderId but is rather a collection of OrderIds. Sometimes multiple items from the same order are on FormX, sometimes it's just one, most orders don't have any items on FormX. But when someone pulls up their order, I need for all the FormXs their items belong on to show up so they can be modified/viewed.

I was thinking of maybe creating a stored proc that does what I said above, run one query to pull down all the related OrderIds and then another to return the appropriate FormXs. But there has to be a better way...

Answer 1

Hello, I understand you need to get a list of the "FormX"s related to a single OrderId. You say, that OrderItems does contain a column for FormXId. You can issue the following query:

select 
    FormX.*
From 
    OrderItems
join
    Formx
on
    OrderItems.FormXId = FormX.FormXId
where
    OrderItems.OrderId = @orderId

You need to pass @orderId to your query and you will get a record set with FormX records related to this order.

You can either package this query up as a stored procedure using @orderId paramter, or you can use dynamic sql and substitute @orderId with real order number you executing your query for.