CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
Ahmad Gulzar
Ahmad Gulzar

Reputation: 363

joining the same table twice for different columns

i have one table products and one table locations. products table have two columns pickLocation and recLocation. in locations table i have id and name columns. pickLocation and recLocation have id from location table. how can i join table in codeigniter.

Here is my code

$this->db->select("locations.name as plname");
$this->db->select("locations.name as rcname");
$this->db->join("locations","locations.id=products.pickLocation","LEFT");
$this->db->join("locations","locations.id=products.recLocation","LEFT");

Here is Products table

+----+--------------+-------------+
| Id | pickLocation | recLocation |
+----+--------------+-------------+
|  1 |           12 |          23 |
|  2 |           12 |          12 |
+----+--------------+-------------+

Here is Location table

+----+-----------+--+
| Id |   name    |  |
+----+-----------+--+
| 12 | Location1 |  |
| 23 | Location2 |  |
+----+-----------+--+

I want result like this

+-----------------------+
| 1 Location1 Location2 |
+-----------------------+
| 2 Location1 Location1 |
+-----------------------+

Upvotes: 1

Views: 3396

Answers (2)

AdrienXL
AdrienXL

Reputation: 3008

Use aliases. Also, your product table never appears except in the join clause. It should also be in the from.

$query = $this->db->select("p.id, l1.name as plname, l2.name as rcname")
                 ->join("location l1", "l1.id = p.pickLocation", "left")
                 ->join("location l2", "l2.id = p.recLocation", "left")
                 ->get("product p");

Upvotes: 6

Daniel
Daniel

Reputation: 3514

You need to use aliases, to be able to distinguish between these two joins. Try something like this:

$this->db->select("pickLoc.name as plname");
$this->db->select("recLoc.name as rcname");
$this->db->join("locations as pickLoc","pickLoc.id=products.pickLocation","LEFT");
$this->db->join("locations as recLoc","recLoc.id=products.recLocation","LEFT");

Upvotes: 2

Related Questions