antmeeks Posted December 10, 2012 Share Posted December 10, 2012 I've been stuck on this for a couple of hours now & I'd really appreciate some help(!) I have 2 tables: entries +----+--------+------+-------+------------+ ¦ id ¦ status ¦ type ¦ order ¦ date ¦ +----+--------+------+-------+------------+ ¦ 1 ¦ 1 ¦ term ¦ 4 ¦ 0000-00-00 ¦ ¦ 2 ¦ 0 ¦ item ¦ 6 ¦ 0000-00-00 ¦ ¦ 3 ¦ 1 ¦ term ¦ 1 ¦ 0000-00-00 ¦ ¦ 4 ¦ 1 ¦ term ¦ 7 ¦ 0000-00-00 ¦ ¦ 5 ¦ 0 ¦ item ¦ 5 ¦ 0000-00-00 ¦ ¦ 6 ¦ 1 ¦ term ¦ 8 ¦ 0000-00-00 ¦ ¦ 7 ¦ 1 ¦ term ¦ 3 ¦ 0000-00-00 ¦ ¦ 8 ¦ 0 ¦ item ¦ 2 ¦ 0000-00-00 ¦ +----+--------+------+-------+------------+ entry_text +----+----------+----------+---------+---------+ ¦ id ¦ entry_id ¦ language ¦ name ¦ value ¦ +----+----------+----------+---------+---------+ ¦ 1 ¦ 7 ¦ en ¦ title ¦ varchar ¦ ¦ 2 ¦ 7 ¦ en ¦ content ¦ varchar ¦ ¦ 3 ¦ 7 ¦ fr ¦ title ¦ varchar ¦ ¦ 4 ¦ 7 ¦ fr ¦ content ¦ varchar ¦ ¦ 5 ¦ 3 ¦ en ¦ title ¦ varchar ¦ ¦ 6 ¦ 3 ¦ en ¦ content ¦ varchar ¦ ¦ 7 ¦ 3 ¦ fr ¦ title ¦ varchar ¦ ¦ 8 ¦ 3 ¦ fr ¦ content ¦ varchar ¦ ¦ 9 ¦ 6 ¦ en ¦ title ¦ varchar ¦ ¦ 10 ¦ 6 ¦ en ¦ content ¦ varchar ¦ ¦ 11 ¦ 6 ¦ fr ¦ title ¦ varchar ¦ ¦ 12 ¦ 6 ¦ fr ¦ content ¦ varchar ¦ ¦ 13 ¦ 2 ¦ en ¦ title ¦ varchar ¦ ¦ 14 ¦ 2 ¦ en ¦ content ¦ varchar ¦ ¦ 15 ¦ 2 ¦ fr ¦ title ¦ varchar ¦ ¦ 16 ¦ 2 ¦ fr ¦ content ¦ varchar ¦ +----+----------+----------+---------+---------+ What I'm trying to do is some sort of join statement like: select * from entries where status = 1 and type = term join name,value from entry_text where entry_id = entries.id and language = en order by entries.order, entries.date ASC where I end up with an associative result, indexed by entries.id with the result from entry_text being a name/val pair nested array: array( [3] => array( [id] => 3 [status] => 1 [type] => term [order] => 1 [date] => 0000-00-00 [text] => array( [title] => varchar [content] => varchar ) ) [7] => array( [id] => 7 [status] => 1 [type] => term [order] => 3 [date] => 0000-00-00 [text] => array( [title] => varchar [content] => varchar ) ) [1] => array( [id] => 1 [status] => 1 [type] => term [order] => 4 [date] => 0000-00-00 [text] => array( [title] => varchar [content] => varchar ) ) etc... ) Doable...? (sorry for the wonky formatting - I can't get the editor to preserve my spacing) Link to comment https://forums.phpfreaks.com/topic/271833-help-with-1-to-many-join/ Share on other sites More sharing options...
Christian F. Posted December 11, 2012 Share Posted December 11, 2012 I think you're using the wrong JOIN syntax there, actually never seen that variant myself. What you want to be doing is using a straight INNER JOIN, using the following syntax: SELECT {$Fields} FROM {$Table} [AS alias_1] INNER JOIN {$Table_2} [AS alias_2] ON alias_2.entry_id = alias_1.id AND {$Lang} {$Conditions} Link to comment https://forums.phpfreaks.com/topic/271833-help-with-1-to-many-join/#findComment-1398701 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.