MySQL query help

eddjusted

Hi, I have two mysql tables with large amount of data. Table_1 has fields PropID, FullStreetAddress, City, State, Zipcode, Remarks, Date which I want to pick. Table_2 has PropID,URL. Now I want to pick top 60 rows from table_1 order by date desc. Each PropID has several URL records in Table_2. Out of those records, I need to pick only 4 for each PropID in table_1. So in effect I would get a result set of 60 x 4 = 240 rows. Selecting from table_1 and inner joining on PropID in table_2, I am getting a complete result set, but I need to limit that somehow to get 60 rows from table_1 and 4 rows for each of the 60 ids from table_2 in a single query. thanks.

Blue_Boy

use

LIMIT 60

to get 60 rows from table_1

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

eddjusted

I know that part. But I need to create a View of some sort with 60 x 4 = 240 rows, I cant use Limit for both tables in a single query now, can i?

Bernhard Hiller

Only the first part of the solution: how to get the 60 rows from table_1:

SELECT table_1.PropID, FullStreetAddress, City, State, Zipcode, Remarks, Date, table_2.URL
FROM table_1 LEFT JOIN table_2 ON table_1.PropID=table_2.PropID
WHERE table_1.PropID IN
(SELECT PropID
FROM table_1
WHERE
LIMIT 60)

That's still with all releated rows from table_2.