Im a little stuck here, any help would be great
I have three database tables
ProductsTable > Fields (product_id, product_description)
GroupsTable > Fields (group_id, group_name)
PriceTable > Fields (price_id, group_id_fk, product_id_fk, price)
The reason behind this is my site sells a single set of products, but the price of each product is different according the group I will be selling to.
Typical data placed in these tables would be
- ProductsTable
- 1 > Apples
- 4 > Oranges
- 6 > Grapes
- GroupsTable
- 16 > Shops
- 21 > Malls
- 23 > EndUser
- PriceTable
- 117 > 16(Shops) > 1(Apples) > $3,00
- 119 > 16(Shops) > 4(Oranges) > $4,00
- 125 > 16(Shops) > 6(Grapes) > $2,00
- 141 > 21(Malls) > 4(Oranges) > $3,75
- 163 > 23(EndUser) > 1(Apples) > $4,00
- 166 > 23(EndUser) > 6(Grapes) > $3,00
What I am trying to do is make a list, filtered by Group, where it only returns all available products that have not had a price set already.
So if I chose from a select dropdown, “Shops” it should return nothing because all pricing for Shops has already been set.
If I choose Malls, it should only show me Apples and Grapes because I only have a price set for Oranges.
If I choose EndUser, it should only show me Oranges because it is the only product left without price.
I hope this makes sense. It seemed so simple to me until i actually tried to do it. Just can not get the correct result returned.
Last updated: