Click here to Skip to main content
14,978,175 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a mysql table in which I have headers in the following manner:

https://i.stack.imgur.com/o3bO0.png[^]
I am trying to write a sql query to lookup the product cost A and B based on the selected product name and state. I have not worked with something like this before; where I had to match the input textbox value with the header of a table to retrieve the desired outcome. Any help or guidance will be highly appreciated.


What I have tried:

I tried creating sub tables for each state; but, that did not work.
Posted
Updated 22-Mar-21 6:03am

1 solution

To be honest, that's probably a design problem with your DB.
Instead of allocating "fixed" columns for your two states you should have a table for the states, and a separate table linking your state to your product. That way, you have considerably more flexibility: you can add a new state without changing existing rows.

Products
ID        INDENTITY or UNIQUEIDENTIFIER
Name      NVARCHAR
SKU       NVARCHAR


States
ID        INDENTITY or UNIQUEIDENTIFIER
Name      VVARCHAR


Costs
ID        INDENTITY or UNIQUEIDENTIFIER
StateID   Foreign Key to States.ID 
ProductID Foreign Key to Products.ID
CostA     DECIMAL
CostB     DECIMAL


You can then use JOINs to "put the information together" in whatever form you need.
SQL
SELECT p.Name, p.SKU, s.Name, c.CostA, c.CostB From Products p
JOIN Costs c ON p.ID = c.ProductID
JOIN States s ON s.ID = c.StateID
WHERE s.Name = "Alabama"
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900