I'm having some difficulty trying to figure out how to write a certain query.
The very basics are that I have two relevant tables: `containers` and `items`.
`containers` have the fields: ContainerID, ContainerName, and ContentType.
`items` have the fields: ItemID, ItemName, and ContainerID.
The easy part of the query is to return a result with the following fields:
ContainerName, ContentType, and GROUP_CONCAT(ItemName) to show all the Items in a container in one field, separated by commas or some other separator.
The problem comes in next. I need something such as a WHERE clause and/or a nested query or something that will return all containers that contain the item 'Apple' (or whatever item is specified in the where clause). The GROUP_CONCAT(Itemname) column must not just display 'Apple', it must still display all the items in that container, separated by a comma or some other separating charater.
I have tried different ideas but to no avail.
Any help will be appreciated.
PS. I'm using MySQL with PHP. So PHP suggestions are also welcome.