4

I am building a social media news aggregation web app in PHP and MySQL. It needs to map various URLs (column url) to specific trends (column trend_id). Here's my dummy data structure:

Sample data

I need to build a query to fetch URLs for a particular trend_id (for example 12). This is simple:

SELECT url FROM url_table WHERE trend_id = '12'

Now, some trends are related to a specific parent trend. For example trend_ids 12,16 and 45 are related to a parent trend_id 12. So here's my query:

SELECT '12' as parent_trend_id,url FROM url_table WHERE (trend_id = '12' OR trend_id = '16' OR trend_id = '45')

Here's another example of similar query:

SELECT '345' as parent_trend_id,url FROM url_table WHERE (trend_id = '345' OR trend_id = '457' OR trend_id = '16')

The issue is that there are multiple such parent - child relationships between trends. As of now I run a for loop within PHP and execute multiple queries. Also, given the nature of my app the parent trend cannot be a part of this table. Is there a way to have a single query which clubs multiple such queries?

Edit:

I have a separate table defining child relationships. It's a simple 3 column table with ID,trend_id (parent) and related_trend_id (child). However, one related_trend_id (child) can have multiple trend_ids (parents). Here's a snapshot of the relations table:

Relations table sample data

Akshay Raje
  • 852
  • 9
  • 20

2 Answers2

2

You specific query would now be:

SELECT r.trend_id as parent_trend_id,url 
FROM url_table
Join relations r on r.related_trend_id = childid
WHERE r.trend_id = 12

or to get all of them

SELECT r.trend_id as parent_trend_id,url 
FROM url_table
Join relations r on r.related_trend_id = childid

Works exactly as expected for me:

http://sqlfiddle.com/#!3/b137a/9

What results did you expect?

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • Not working... probably as there could be multiple parents for a child. Well, is there a way to simply combine results on multiple Select statements into one... as in combine all rows as such? – Akshay Raje Jul 27 '12 at 21:19
  • 1
    I can combine the rows on sql server but I don't know how to do that on mysql -- I suggest you just do that in the client program, php is better at that kind of string manipulation. – Hogan Jul 27 '12 at 22:49
  • Did just that, Used PHP to create a UNION query to combine rows into one subtable. – Akshay Raje Jul 28 '12 at 10:25
0

Join on the table containing the parent-child relationships?

SELECT url_table.*
FROM url_table
INNER JOIN parents
    ON url_table.trend_id = parents.trend_id
WHERE parents.parent_id IN (1,2,3);

(1,2,3) being all the parent trends for which you want to extract tuples.

Matt
  • 4,515
  • 5
  • 22
  • 29