[SOLVED] Complex Multi-Table Query on a One-to-Many Relationship...

[mem0ri]

Here's the story...

 

I've got a SELECT statement pulling from two tables simultaneously...and it works beautifully...

 

SELECT (stuff) FROM table1 t1

LEFT JOIN table 2 t2 ON t1.id = t2.t1id

WHERE (stuff) AND $cols LIKE '%$searchtxt%'

ORDER BY (stuff) ASC

 

It's beautiful with a 1 to 1 relationship on both tables...but what I need...is...

 

Pull from table 1 a single entry...

...that dissects multiple entries from table 2 and LEFT JOINs with only the most recent...

 

Something like (and I know this syntax isn't valid at all)

 

SELECT (stuff) FROM table1 t1

LEFT JOIN table 2 t2 ON t1.id = t2.t1id ORDER BY t2.id DESC LIMIT 1

WHERE (stuff) AND $cols LIKE '%$searchtxt%'

ORDER BY (stuff) ASC

 

I'm a little lost on how to put it all together....basically...

 

table 1                    |                      table 2

  1                                                  1, 1

  2                                                  1, 2

  3                                                  1, 3

                                                      1, 4

 

I need the SQL to return table 1 (1) and table 2 (1,4)...while skipping over (1,1 and 1,2 and 1,3 and 1,4) and join those two...AND the SQL needs to still pick up table 2 and 3 even though there is no corresponding data with table 2.

[richardw]

This may work for you... It selects your data from table 1 and should match to the most recent data fronm table 2. I use this script to group records under a common id, but I have always selected all. I don't see why you can't use it to select the most recent by ordering and limit the select to the most recent record.

 

<?php
$sql = "SELECT * FROM table1 ORDER BY (stuff) asc";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) { 
$match= $row["Table1_CommonFIELD-cols"] ;
echo $row["Table1MatchFiled-identifier"];  // use only if you want to display the parent record title 
	$tbl2 = "SELECT * FROM table WHERE (stuff AND ($cols LIKE '%$match%')) ORDER BY date desc LIMIT 0,1";
	$result3 = mysql_query($tblt2);
	while ($row3= mysql_fetch_assoc($result3)) { 
	echo "most recent results matched here" ; 

	}	 
?>

 

I hope this steers your query in the right direction,

[mem0ri]

I think that's gettin' me there...thank you.

 

I think I'm lookin' at a situation where I need to throw a sub-query in the mix.  Workin' on a solution now and I'll post it if/when it works.  Thanks for steerin' me toward the right answer!

[fenway]

I'm confused about what you're trying to do...

[mem0ri]

Sorry about that Fenway...let me try to say it in a bit more readable fashion.

 

(btw...still actively working on the problem...this is a frustrating one)

 

I have TABLE1 from which I want to gather * on all rows where userid = x;

 

I have TABLE2 from which I want to join a couple columns of info to TABLE1 on t2.joinid = t1.id;

 

...for each TABLE1 row there could be multiple TABLE2 rows...

 

...but for each TABLE1 row I want to only join the LAST (most recent) TABLE2 row available.

 

I've been trying to work with sub-queries...but they're not workin' for me...mostly due to some unfamiliarity with doing them.

 

[fenway]

Ah... in that case, write the query to get the most recent, and then join it back to table1.

[mem0ri]

Thanks...I've got it working now and thought I'd post up the result just for anyone who searches these forums later. 

 

SELECT t1.x, t1.y, t1.z,  t2.x

FROM table1 t1

LEFT JOIN table2 t2

ON t2.t1id = t1.id AND t2.y = 0

WHERE t1.user = '$user' AND t1.a = 0 AND $cols LIKE '%$searchtxt%'

ORDER BY t1.x ASC