2 Where Ins for Relational Database

[John Giotta]

I start by trying to explain my goal. I have a relational database that allows users to cross reference 1 to many categories and add tags that are also one to many.

In short, one row can have many tags and be a part of many categories.

1 table for the meat of data, 1 tags table, and a cross reference table to join data to tags. Same goes for categories; 1 category table and a cross reference table to join data to categories.

[sqlcode]
SELECT ad.site_id, ad.create_date, ad.created_by, ct.*, GROUP_CONCAT(DISTINCT c.label) AS category, GROUP_CONCAT(DISTINCT t.`text`) AS tags
FROM (`asset_details` ad)
LEFT JOIN `categories_xref_assets` cxa
ON cxa.assets_id = ad.assets_id
LEFT JOIN `categories` c
ON cxa.categories_id = c.id
LEFT JOIN `tags_xref_assets` txa
ON txa.assets_id = ad.assets_id
LEFT JOIN `tags` t
ON t.id = txa.tags_id
INNER JOIN `content` ct
ON ad.assets_id = ct.assets_id

WHERE (t.`text` IN ('wedding'))
OR (c.code IN ('business'))

AND ((ad.site_id IN ('jdg')) OR ad.shared = 1)
AND ad.status = 1
GROUP BY ad.assets_id
[/sqlcode]

Now granted the above has a lot to be desired in optimization (i.e.;views). However, the WHERE IN clause fails to pull both data with wedding tag and category code of business.

Got a clue what I can do?