Lookup table multiple columns

August 28, 2021, 11:18:27 AM

Hello, my knowledge of php and sql is very beginner level. I'm doing a test run and my problem is that in a sales form, I can't see multiple fields in the products table as a lookup.

In the 1st picture there are database tables.

The product field information in the sales form is taken from the product table. I get the model field from the pruduct table, I get the colorID from the product table and the color from the color table, and the sizeID from the product table and the size information from the size table. I want to select the product selection box in the sales form as a list, but this field is blank when I use custom sql.

My custom sql query is running in the sql management program. As seen in the last picture.

Re: Lookup table multiple columns

Reply #1 – August 28, 2021, 03:06:32 PM

I solved the problem, I had a mistake in my query. It was solved when the code was as follows. Maybe it will be useful to someone in the future.

Quote

"SELECT CONCAT (products.model,\" \" , colors.color ,\" \", sizes.size) AS label,products.id AS value
FROM (
(products INNER JOIN colors ON products.colorsID = colors.id)
INNER JOIN sizes ON products.sizeID = sizes.id) ORDER BY products.model ASC"