IN Function

[Keyth]

Good day to you all.

I have an nvarchar value stored in a table, example such as '(15645),(15646)' and '(15680),(15679),(15678),(15677)' under field name SALES_ORDER_ITEM_ID

Example Query;

select Pallet.*, Purchase_Order_Item.SALES_ORDER_ITEM_ID
from [Quotes].[dbo].[pallet] 
left outer join quotes.dbo.purchase_order_item on purchase_order_item.item_id = pallet.po_item_id
where pallet.active = 1

I want to join other tables where the PK is IN(15645,15646) or IN(15680,15679,15678,15677)

As the database is expecting an array of INT, can anybody help with the correct syntax/conversion for doing this please?

Much appreciated
Keith

Example Table Data:

 

[simian336]

Your probably going to need to use a split function (you can goggle one, there are a bunch) and create a temp table with individual values fore SALES_ORDER_ITEM_ID

Simi

 

[r937]

do some research on first normal form

you're hurting yourself with the current design

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Keyth]

I know it looks daft how the table has been designed but I am building on something that has been in use for a long time, when I posted this thread I thought it was too much work to re-design.

@r937: I know what you mean and I am aware of database relationships, ive been building dbs and writing apps for a long time. I know how these tables should have been designed but unfortunately I am unable to change the cardinality of what was developed years ago without re-writing the 3 dll modules which read this database. I can now work around the problem but wondered if anybody had a way of achieving what I wanted before I started re-writing the app.

An afterthought... I could add FK SALES_ORDER_ITEM.POI_ITEM_ID for PURCHASE_ORDER_ITEM and populate accordingly. Im thinking that because the relationship between these tables could be many-to-many OR many-to-one the table was designed as it is without the consideration that a user may want to find information when looking at the purchase order, only assuming they would always look at sales_order for purchase order information! Its only come to light now Ive added a PALLET booking in system with goods received notes, I want to quickly query the SALES_ORDER status but cannot perform the correct joins.

Bit strange but worse things happen at sea.

Thanks both for your advice, I will plod along and see how I go.
Regards, Keith