Search returning multiple results based on database foreign key table

I've added an 'industryID' column to an existing tablethat had foreign keys for product ID and lubricant ID already. This will allow products to be categorized by both lubricant and industry (see mysqlTable.jpg file).

Each product has multiple lubricants (for example, it may be classified as both an industrial oil and a gear oil) and multiple industries. In the table, there may be several records which show the same product number (PN) key and lubricant (L) key, but different industry (I) keys [(PN) 200, (L) industrial oil, (I) agriculture, marine, construction).

This works fine except on searches by product number or lubricant, which don't filter out the industry, returning results like: (PN) 200, (L) industrial oil, industrial oil, industrial oil (see Search.jpg file). It's ok if the results show more than one product for each lubricant, but how can I get it to stop showing multiple results based on the added industry column?

The search is online, but the latest industry page is not, so I've included screen shots along with files.

Just add "GROUP BY ProductNumber" to the end of your SQL statement and then the same product number won't be displayed twice.

The SQL statements already have GROUP BY (see attached pdf that includes the SQL statement, database table and current result). I've also tried changing the GROUP BY column names to those in the foreign key table, but got the same result.

The second page of the pdf uses a similar query from the same database table. Is there a way to eliminate having the lubricant after every product, and just as a heading?

You are grouping by both ID and LubeTyepID, which means if either is unique it will show as a unique row. If you don't want to show repeats, don't group by the LubeTypeID and only group by ID.

You would probably need to write a custom IF statement to see if the type changed row by row and only show it when it changes.

I've tried grouping by ID from the product table, and also using ProdIDType from the foreign key table - each give similar results whether LubeID is added or not.

I've attached a pdf file that shows the table (which has been changed to force feed results), one of the the SQL statements, and the search results page.

I think you want it grouped by the productID so that each product only shows once. Then you can use the GROUP_CONCAT() statement to get all of the ProductTypes and LubeTypes for the grouped result.

Example: there are nine listings in the foreign key table. Three with product #10 that are greases; three with product #15 that are gear oils and three with product #10 that are engine oils.

The results should show one #10 grease & engine oil, and one #15 gear oil.
The results show one #10 grease, grease, grease, engine oil, engine oil, engine oil, and #15 gear oil, gear oil, gear oil.

It looks like the product is showing correctly, but not the lubes. I've tried multiple combinations of GROUP BY and Group_Concat, but the lube results are always the same. I have a recordset using GROUP BY on another page working properly (though it SELECTS from a foreign key table, rather than a parent table like the one I'm having issues with. Tried changing the SELECT * FROM prod to FROM prod type (the foreign key table), but with no difference. Query below:

SELECT *, Group_Concat(Lube) AS LubeCats
FROM prod LEFT OUTER JOIN prodtype ON prod.ID = prodtype.ProdIDType LEFT OUTER JOIN lubetype ON prodtype.LubeID = lubetype.LubeTypeID LEFT OUTER JOIN prodsize ON prod.ID = prodsize.ProdSizeID LEFT OUTER JOIN contsize ON prodsize.ContSizeID = contsize.ContID LEFT OUTER JOIN Tech_DS ON prod.ID = Tech_DS.TDS_ID LEFT OUTER JOIN industry ON industry.IndustryID = prodtype.IndustryTypeID
GROUP BY ID

If I need to post this for further troubleshoot, let me know. I'll need to update the Db first.

Would appreciate a solution on this today if at all possible.

Submitted the latest post last Thursday, but haven't received a reply. Is this a work in progress, or is there a delay for some reason? Let me know if you need anything further on my end to resolve.

How do I view the page with the problem?

You may need to add a DISTINCT specification to the GROUP_CONCAT() function call to remove the repeats it is returning:
SELECT *, Group_Concat(DISTINCT Lube) AS LubeCats