#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0

    Help with 1 to many join


    I've been stuck on this for a couple of hours now & I'd really appreciate some help(!)

    I have 2 tables:
    Code:
    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:
    Code:
    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...?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,167
    Rep Power
    4274
    Originally Posted by gerwalk
    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
    nice try, but several syntax errors

    try this --
    Code:
    SELECT entries.* 
         , entry_text.name
         , entry_text.value
      FROM entries 
    INNER
      JOIN entry_text 
        ON entry_text.entry_id = entries.id 
       AND entry_text.language = 'en'  
     WHERE entries.status = 1 
       AND entries.type = 'term'
    ORDER 
        BY entries.order
         , entries.date
    Originally Posted by gerwalk
    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:
    no idea what this means, it certainly isn't part of mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo