Reputation: 5786
I have some amount of tables. In each table there is a field id(int). In each table can be multiple record with the same id. For example:
id1 val1 id1 val2 id1 val3
id1 val4 id1 val5 id1 val6
I need a select query witch gives me
val1 val2 val3
val4 val5 val6
I tried
SELECT * FROM table1
INNER JOIN table2 ON table1.ban_id = table2.ban_id
INNER JOIN table3 ON table1.ban_id = table3.ban_id
INNER JOIN table4 ON table1.ban_id = table4.ban_id
INNER JOIN table5 ON table1.ban_id = table5.ban_id
INNER JOIN table6 ON table1.ban_id = table6.ban_id
And as I understand it gives me all possible permutations of my values.
UPDATE1
Lets i geve you more info. I am writing an application in witch users may be banned in multiple ways for example by nick ip guid(some guid-like id)
Also in database reasons of this ban is stored. So there is a situation when there is one record with guid ip nick but multiple reasons or multiple nicks witch are responding the same id.
http://pastebin.com/HQs4XxDm - here is structure of the tables
Upvotes: 1
Views: 110
Reputation: 4925
What are you getting at the moment? It's easier to help if you give some debug info.
I expect you have a problem with the ambiguous column names. Try
SELECT
`table1`.`id1` AS `t1_id1`,
`table2`.`id1` AS `t2_id1`,
`table3`.`id1` AS `t3_id1`,
`table4`.`id1` AS `t4_id1`,
`table5`.`id1` AS `t5_id1`,
`table6`.`id1` AS `t6_id1`
FROM table1
INNER JOIN table2 ON table1.ban_id=table2.ban_id
INNER JOIN table3 ON table1.ban_id=table3.ban_id
INNER JOIN table4 ON table1.ban_id=table4.ban_id
INNER JOIN table5 ON table1.ban_id=table5.ban_id
INNER JOIN table6 ON table1.ban_id=table6.ban_id
Upvotes: 1