Ruslan F.
Ruslan F.

Reputation: 5786

MySQL query help SELECT JOIN

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

Answers (1)

SimonMayer
SimonMayer

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

Related Questions