CODEWITHSUNDEEP
phpmysqlsql
Akshay Raje
Akshay Raje

Reputation: 892

Combining rows of multiple queries into one

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

Upvotes: 4

Views: 229

Answers (2)

Hogan
Hogan

Reputation: 70538

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?

Upvotes: 2

Matt
Matt

Reputation: 4795

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.

Upvotes: 0

Related Questions