Help please retrieving data from two tables (joins)

[Duncan Disorderly]

Hi All
I think this falls into the PHP + Mysql Joins issues category

Scenario:
I have two tables: listings and listings_p

listings has the following fields: ID,Type,Area,Value, Desc.

listings_p has :ID, L_ID, Path - "path" being the path to a jpeg file.
Note: a one to many relationship - listings_p.L_ID = listing.ID

2: I have three dropdown boxes that I use to search the listings, these are Type, Area and Value.

Requirement:
I need to display all the data from listings plus any matching records (photos) from listings_p.
where Type=X AND Area=X AND Value BETWEEN X AND X

Issue:
Getting the relevant records from listing is easy but what I can not workout is the Query to get the records from listings AND the matching records from listings_p where listings_p.L_ID = listing.ID.
ie: For every listing record there are many Listing_p records

Also :I need to do the above but this time get only the first matching record from listings_p (ie first photo)
For every listing record get only the first Listing_p record


any advice gratefully received.

Cheers
DD

[fenway]

Well, a JOIN will give you your "additional" records, but you'll need to run a separate query on the initial table; I guess you could do a UNION if you really felt the need to pull it back from a single statement.

[Duncan Disorderly]

[!--quoteo(post=387095:date=Jun 23 2006, 02:37 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 23 2006, 02:37 AM) [snapback]387095[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well, a JOIN will give you your "additional" records [/quote]
Fenway,
Thank you for your kind reply.
I had thought it would require a join... unfortunately,I do not know which join to use or how to use it..

All
I have asked a few people about this and the general response is .."use a join" which is great if i knew how but I dont. I have read the Mysql manual entries for it and just dont get it.

I suppose what I am really asking is: can some kindly person write the query for me? I can then follow it to see how and why each portion of it goes where in the query, basically learning by example.

I would like to know how to include the join into my query statement, given that I firstly need to get all "listings" records that match the search criteria (type,Val,Area) THEN get each "listing_p" record where listings_p.L_ID matches listings.ID from the "listings" records returned by the inital search criteria.
a one to many relationship (many "listings_p" records for each "listings" record)

This was my original query, which worked great until I decided I need to add X more pics to each listings record - hence the listings_p table to hold all the associated image paths.

<?
.............
$result = mysql_query("SELECT * FROM Listings WHERE Type='$L_Type'AND Area='$L_Area'AND Value='$L_Val'") or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

<img src="<? echo $row['Path'];?>">, <? echo $row['ID'],": ",$row['Description'];?>

}
?>

Having just written the above, it suddenly became apparent that i have another issue:
I do not know how to extract/display the "listings_p" records for each "listings" records from the array, given that "path" is now from the listings_p table and both "ID and Description" are from the listings table.
Any help here would be kindly accepted too!.

Sorry for the confusing mail.

Kind regards to all
DD

[fenway]

Something like the following should work (UNTESTED):

[code]SELECT l.*, p.*
FROM listings AS l
LEFT JOIN listings_p AS p ON ( p.L_ID = l.ID )
WHERE Type='$L_Type' AND Area='$L_Area'AND Value BETWEEN '$L_Val' AND '$H_Val'[/code]

To which you'd need to add the "easier" query.

[Duncan Disorderly]

[!--quoteo(post=387396:date=Jun 24 2006, 02:48 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 24 2006, 02:48 AM) [snapback]387396[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Something like the following should work (UNTESTED):

[code]SELECT l.*, p.*
FROM listings AS l
LEFT JOIN listings_p AS p ON ( p.L_ID = l.ID )
WHERE Type='$L_Type' AND Area='$L_Area'AND Value BETWEEN '$L_Val' AND '$H_Val'[/code]

To which you'd need to add the "easier" query.
[/quote]


Fenway, Once again kind thanks for your response...
I actually worked it out late last night...about 4am!....

<?
$result = mysql_query("Select listings_P.`Path`,listings_P.`ID` ,listings.`Description`, listings.`ID`
FROM `listings_p`
RIGHT JOIN `listings`
ON listings_p.`L_ID`=`listings`.`ID`
WHERE listings.`Type`='$L_Type' AND `listings`.`Area`='$L_Area' AND `listings`.`Value`='$L_Value'")
or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
>?
<img src="<? echo $row['Path'];?>">, <? echo $row['ID'],": ",$row['Description'];?>
<?
}
?>

This grabs all matching records from listings and each associated record (image) from listings_p (One to many) via listings_p.L_ID = listings.ID

Cheers
DD

[fenway]

[!--quoteo(post=387401:date=Jun 24 2006, 05:08 AM:name=Duncan Disorderly)--][div class=\'quotetop\']QUOTE(Duncan Disorderly @ Jun 24 2006, 05:08 AM) [snapback]387401[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Fenway, Once again kind thanks for your response...
I actually worked it out late last night...about 4am!....

<?
$result = mysql_query("Select listings_P.`Path`,listings_P.`ID` ,listings.`Description`, listings.`ID`
FROM `listings_p`
RIGHT JOIN `listings`
ON listings_p.`L_ID`=`listings`.`ID`
WHERE listings.`Type`='$L_Type' AND `listings`.`Area`='$L_Area' AND `listings`.`Value`='$L_Value'")
or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
>?
<img src="<? echo $row['Path'];?>">, <? echo $row['ID'],": ",$row['Description'];?>
<?
}
?>

This grabs all matching records from listings and each associated record (image) from listings_p (One to many) via listings_p.L_ID = listings.ID

Cheers
DD
[/quote]
Glad you got it working... but [b]NEVER[/b] use RIGHT JOIN! It's almost never needed, and everyone uses LEFT JOIN by convention; simply switch the order of the tables in your query.