0

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.

Edited by Some-Jackass: more spelling

3
Contributors
3
Replies
8
Views
7 Years
Discussion Span
Last Post by Some-Jackass
0

Hello,

From what I see in the documentation you can treat the GROUP_CONCAT(itemname) as a list to test against.

select ContainerName, ContentType, and GROUP_CONCAT(ItemName) as itemnamelist
where "Apple" in GROUP_CONCAT(ItemName)
group by ContainerName

At least that is the way I read it. I would like to know if it works...

Edited by rch1231: n/a

0

I think you need to change line 2 in the above query to:

WHERE FIND_IN_SET('Apple', GROUP_CONCAT(ItemName))

You can also use the where clause in the following manner (in your query):

WHERE ContainerID IN (SELECT ContainerID FROM items WHERE ItemName = 'Apple')

Edited by pritaeas: n/a

0

Thanx for the help guys! Your replies got me searching in the right direction.
I found the sollution:

SELECT ContainerID, ContainerName, ContentType, GROUP_CONCAT(ItemName)
FROM container, item
WHERE container.ContainerID = item.ContainerID
HAVING GROUP_CONCAT(ItemName) like '%Apple%'
GROUP BY ContainerID
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.