Search code examples
phpmysqlinner-join

Table joining using PHP MySql

enter image description here

enter image description here

I have these two tables where I wanna join these two tables and take the amount details from the 1st table by ID's in the '''left & right''' right column of 2nd table

** EDIT:

In stead of images, you could have posted text:

id   email  amount
  1  jk001  3000
858  jkS25  1500
869  jkS26  1500
860  jkS27  1500
861  jkS28  1500
862  jkS29  750
863  jkS30  760
864  jkS31  1500
865  jkS32  1500
866  jkS33  750
867  jkS34  760
868  jkS35  3000
869  jkS36  3000

It is left as an exercise (for Sam) to do the same with the second picture.

Solution

  • To be able to create a join between the two tables that allows you tu pull the amount value of the left and right id's in the second table from the first table you need to LEFT JOIN two times renaming each join with the first table as la and ra (or whatever other name you think appropriate) indicating the relation to the left and right id's respectively. Something like this:

    SELECT 
    `origin`.`id`, 
    `origin`.`amount`, 
    `origin`.`userid`, 
    `origin`.`left1`, 
    `la`.`amount` AS leftAmount, 
    `origin`.`right1`, 
    `ra`.`amount` AS rightAmount 
FROM `table2` AS origin

LEFT JOIN 
    `table1` AS la 
ON `origin`.`left1`=`la`.`email`

LEFT JOIN 
    `table1` AS ra 
ON `origin`.`right1`=`ra`.`email`

    I had to asume the names of the tables and rename left and right columns to left1 and right1 because those words are reserved in my MySQL database. Also you need to make sure the syntax is the correct one for your database, I'm using the syntax for a MySQL v8.0.15.