Member Avatar for Some-Jackass

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 because: more spelling
  • 3 Contributors
  • 3 Replies
  • 159 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Some-Jackass
Member Avatar for rch1231

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 because: n/a
Member Avatar for pritaeas

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 because: n/a
Member Avatar for Some-Jackass

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.