Jump to content

Help With 1 To Many Join


antmeeks

Recommended Posts

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
Share on other sites

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
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.