Data Modeling relationship Query

[Getalok]

Hello Everyone -- Need help to design data model , We have Promotional Trans detail table and Market dim table those are one o one mapped . Marketing detail table contain Prom_id and reference id .

Challenge we are facing to filter the data in Promotional trans details table by reference id as Prom_id can be link to multiple reference id . When we are filter the data by reference id , it's showing at Prom_id consolidated details not by breakdown by reference id since this map to Prom_id .

Promotional trans details		Market Details
Prom_id	one to one relationship	Prom_id
		reference id

[ogster1974]

something sounds odd in your explaination as how can you have a one to one relationship between the 2 tables based on prod id but a many relationship on reference id?

perhaps use example to map out an example of your issue and your desired output so we can help you more.

[Getalok]

Hello Ogster  -- Thanks for replying .

Let me try to explain , we have two dim table as mention below related to one to many , while my transaction table (Promotional trans details) is map to Market Details table using Prom_id , while filter the data by reference table this not showing break down details by respective reference level instead this showing at Prom_id level 

Market Details		Reference table
Prom_id1, reference 1...	One to many (one Prom 1 can be  associated with multiple Reference id )	Reference 1
Prom_id2 ,reference 2 ...		Reference 2
		Reference 3