CODEWITHSUNDEEP
mysql
Priya
Priya

Reputation: 111

How to select three table with same column name but different values

Hi i'm so confused to select 3 table data value.

Ex: In table1:

Id    value   price  <br> 
1     101     30   <br> 
2     102     40  <br>
3     103     50  <br>

In table 2 

value       price   <br>
101       25  <br>
102       35   <br>
103       45  <br>

In table 3 

value       Price   <br>
101          5%  <br>
102         6%   <br>
103         7%  <br>

in this how can i get all price value with id

I need a result like 

ID    Price   Price   Price <br>
1  --   30  --   25 --     5%<br>
2  --    40  --   35   --   6%<br>
3  --    50  --   45   --   7%<br>

Upvotes: 1

Views: 2179

Answers (4)

Martin
Martin

Reputation: 2424

What you need to do is perform a JOIN on your tables, using a coherent value known as primary and foreign key. The coherent values that links your tables together in this case is the column named value. So that's what you need to be using in your JOIN

SELECT
    table1.id as TABLE1_ID,
    table1.price as TABLE1_PRICE,
    table2.price as TABLE2_PRICE,
    table3.price as TABLE3_PRICE
FROM
  table1
LEFT JOIN
    table2 ON table1.`value` = table2.`value`
LEFT JOIN
    table3 ON table2.`value` = table3.`value`

Outputs:

enter image description here

Working SQL fiddle

More about JOIN's here.

Since you mentioned PHP in the comment section, try something like this:

$sql="SELECT
        table1.id as TABLE1_ID,
        table1.price as TABLE1_PRICE,
        table2.price as TABLE2_PRICE,
        table3.price as TABLE3_PRICE
    FROM
      table1
    LEFT JOIN
        table2 ON table1.`value` = table2.`value`
    LEFT JOIN
        table3 ON table2.`value` = table3.`value`"
$result_set=mysqli_query($conn, $sql);
$row=mysqli_fetch_array($conn, $result_set);

$rowcount=mysqli_num_rows($result_set);
$count=0;

while($rowcount > $count) {
    $count++;

    echo 'id: '.$row['id'].'<br />';
    echo 'price: '.$row['price'].'<br />';

    $row=mysqli_fetch_array($conn, $result_set);
}

Remember to include your database connection file so that $conn is accessible.

If it doesn't work initially, try to swap out the actual column names with the defined aliases, like so:

$row['id'] to $row['TABLE1_ID'] etc.

Upvotes: 0

Biplab Sarker
Biplab Sarker

Reputation: 13

select table1.id,table1.value, table1.price,table2.price,table3.price

from table1 inner join table2 on table1.value=table2.value inner join table3 on table2.value=table3.value

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

Try with inner join

select id,table1.value, table1.price as price1,table2.price as price2,table3.price as price3 from table1 
inner join table2 on table1.value=table2.value
inner join table3 on table2.value=table3.value

Upvotes: 1

ยุทธกร ทิพย์รักษ์
ยุทธกร ทิพย์รักษ์

Reputation: 3

SELECT table1.id , table1.price , table2.price ,table3.price 
FROM table1 
JOIN table2 ON table.id = table2.id 
JOIN table3 ON table.value = table3.value

Result

Upvotes: 0

Related Questions