0

i got 3 tables, 2 index tables and 1 data table

tables' structure is like this:

users:

id      | name
--------+----------- 
1       | John
2       | Mike
3       | James

idx and idx2:

user_id  | user_id2 | action   | date
---------+----------+----------+----------
1        | 2        | message  | 2013-03-20
1        | 3        | message  | 2013-03-21
3        | 2        | chat     | 2013-03-21

earlier i had just 2 tables, 1 index table (named 'idx') and 1 data table (named 'users'), so the query was something like this:

SELECT * FROM idx inner join users WHERE idx.user_id='2' and idx.user_id2=users.id ORDER BY idx.date DESC

but now a new index table (named 'idx2') was added, the structure is similar to 'idx' (i can't combine 'idx' and 'idx2' into a single table, so we need to work with 3 tables at once)

so now i can't make a query with 3 tables

i tried to do something like this:

SELECT * FROM idx,idx2 inner join users WHERE (idx.user_id='2' and idx.user_id2=users.id) or (idx2.user_id='2' and idx2.user_id2=users.id) ORDER BY idx.date DESC

but this doesn't work, i guess the query should consist both union and join...

any ideas?

thanks!

dimaninc
  • 765
  • 5
  • 16

1 Answers1

1

your'r right UNION seems the way to go, but the table structure should match, can you give more information? maybe using sqlfiddle?

this should do it if i understood well your question:

(SELECT * FROM idx inner join users WHERE idx.user_id='2' and idx.user_id2=users.id)
UNION
(SELECT * FROM idx2 inner join users WHERE idx2.user_id='2' and idx2.user_id2=users.id) ORDER BY date DESC;

the query returns data from both the tables, and order them properly: check out this example i made

Stefano Crespi
  • 196
  • 5