Help with 1 to many join

Dev Shed Forums Sponsor:

December 10th, 2012, 02:56 PM

gerwalk

Registered User

Join Date: Dec 2012

Posts: 1

Time spent in forums: 45 m 6 sec
Reputation 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...?

December 10th, 2012, 07:25 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,357

Time spent in forums: 3 Months 1 Week 2 Days 4 h 33 m 23 sec
Reputation Power: 4140

Quote:

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

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Help with 1 to many join

Developer Shed Advertisers and Affiliates