How to design domain containing 2 data sources

[lbjmj236]

Hi all,

I am facing a problem in Domain design. There are 2 types of social media posts in my database, say facebook and twitter. The records are stored separately in 2 tables, while their primary key(post id) is stored in a third table in pair of topics. 1 topic pairs with multiple post ids.

      --Tables: FB_POST(id, content), TW_POST(id, content), TOPIC(topic, id)

      --Query: Find all social media posts regarding a topic, e.g. 'Inside Out'.

If I only create 1 join tree in domain, it gives no results. Because no post shares the same id across 2 social platform.

If I duplicate the TOPIC table and create 2 join trees in the domain, then how could they both be imported to a single Ad Hoc view? If not, what can I do to implement this query?

Thanks

[hozawa]

Check "Joins" in Domain Designer.

http://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v610/joins-tab

[djohnson53]

You could make a derived table with an SQL Query thats a union of two selects. I don't quite think a simple join will do it, although your description is a bit hazy to say for sure.

[lbjmj236]

Thanks for all the answers.

I have followed @hozawa link and completed example query by outer join. Then another issue happens becasue my goal is to find all posts for every TOPIC. The query ran for hours and I never got results. I think it is because the outer join is too expensive in this case, especially tables here are huge.

For @djohnson53 suggestion, it is doing subselect during creating derived tables. It worked for sure. But can we figure out a way to make it more general and find all posts attached to every TOPIC?

Thanks again.