MySQL query help SELECT JOIN

Question

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

poor table normalization, each row should have its own ID, value, not multiple per same row... unless that was just poor formatting of the posted question. Which gets me to the next part... After you have the possible values, you specifically want to roll them up into rows of 3 values each? Any particular reason for creating such a simulated cross-tab of values 3 per returned row result? — DRapp
– DRapp, Commented Jan 28, 2012 at 11:08
It looks like you'd be better off placing all of this into one table. You can set NULL values for details that aren't needed, e.g. if you ban an IP address or a nickname. JOINS are really for linking data that NEEDS to be stored in separate tables, e.g. "SELECT cats.name, meals.title FROM cats LEFT JOIN meals ON cats.favourite_meal = meals.id" might return Garfield | Lasagne — SimonMayer
– SimonMayer, Commented Jan 30, 2012 at 15:31

SimonMayer · Accepted Answer · 2012-01-28 10:33:44Z

1

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

answered Jan 28, 2012 at 10:33

SimonMayer

4,9655 gold badges35 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query help SELECT JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related