close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Search returning multiple results based on database foreign key table

Thread began 8/08/2017 4:18 pm by tmcbinc99963 | Last modified 8/15/2017 2:19 pm by Ray Borduin | 209 views | 10 replies |

tmcbinc99963

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.

Sign in to reply to this post

Ray BorduinWebAssist

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

Sign in to reply to this post

tmcbinc99963

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?

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

tmcbinc99963

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.

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

tmcbinc99963

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.

Sign in to reply to this post

tmcbinc99963

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

Sign in to reply to this post

tmcbinc99963

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.

Sign in to reply to this post

Ray BorduinWebAssist

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

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...