Hi There, can someone please lend me their superior sql skills.
I have three tables:
Services: id, name, price, category
Addons: id, name, price, category
Products: id, name, price, category
the category table has 3 rows, Service, product and addon
Each of the tables are linked to the category table respectively, so if a new service is added, then it automatically adds a “1” in the category field stipulating that it linked to the “service” row in the category tab and so on for products and addons.
I have created a editble grid form where there is a select field to the category table pulling in the 3 rows, Service, addon and product. The next select field is supposed to be drawing in info from all three tables, services, products, and addons and then only displaying the data filtered by the category field, so if the user selects “Service” from the category select field, then the “item” select field" should only show the rows from the service table, and if they select the product category then the item field should show only products.
at the moment i thought a join should do it but im not too familair with joins, so hoping can someone can assist me here,
here is my code under the category and item fields and some demo data of the services, addons and products tables:
Category:
SELECT catid, name
FROM category
ORDER BY name
Item:
SELECT
services.category,
services.name,
products.category,
products.name,
addons.category,
addons.name
FROM
services,
products,
addons
WHERE services.category = ‘{category}’
ORDER by Services.name
Demo data:
Category table:
| edit | 1 | SERVICE | 2022-11-16 | 15:08:06 | 1 |
|---|---|---|---|---|---|
| edit | 2 | ADDON | 2022-11-16 | 15:08:13 | 1 |
| edit | 3 | PRODUCT | 2022-11-16 | 15:08:21 | 1 |
Services:
| Modify | id | name | desc | price | date | category |
|---|---|---|---|---|---|---|
| edit | 1 | Wash and Go | Wash and Go | 60 | 2022-10-04 | 1 |
| edit | 2 | Wash and Dry | Wash and Dry | 80 | 2022-10-04 | 1 |
| edit | 3 | FullWash | Wash, Dry, interior vacuum and basic clean | 80 | 2022-10-07 | 1 |
Addons:
| Modify | id | name | description | notes | date | price | category |
|---|---|---|---|---|---|---|---|
| edit | 1 | Wax | Autoglym Express liquid based Wax, applied by mist and buff. | 2022-10-06 | 150 | 2 | |
| edit | 2 | Autoglym Polish | Carnuba based polish developed for darker colours. | 2022-10-06 | 150 | 2 | |
| edit | 3 | Steam Sanitisation | Steam Sanitisation - opens leather pores for leather treatment or general purpose clean use. | 2022-10-06 | 50 | 2 | |
| edit | 4 | Supa Polish | Carnuba based polish for paintwork | 2022-10-06 | 100 | 2 | |
| edit | 6 | XL Vehicle Charge | Extra R20 Charged for larger vehicles | 2022-10-08 | 20 | 2 |