Multiple Selects same table

banbutcher

lo,
i have a table called properties and i am running this select statement:

$querykk = 'SELECT `RecordID`,`Ref_Num`,`Main_Image_Path`,`County` FROM properties'
        . ' where county = "kilkenny"'
        . ' ORDER BY Date_Registered DESC'
        . ' LIMIT 1'
        . ' ';

and i want to do this for a number of counties ie. dublin, cork, limerick... so my question is can i have multiple selects from the same table? then write the results to a table?

Thanks
Richie

Beano

banbutcher wrote: »

lo,
i have a table called properties and i am running this select statement:

$querykk = 'SELECT `RecordID`,`Ref_Num`,`Main_Image_Path`,`County` FROM properties'
        . ' where county = "kilkenny"'
        . ' ORDER BY Date_Registered DESC'
        . ' LIMIT 1'
        . ' ';

and i want to do this for a number of counties ie. dublin, cork, limerick... so my question is can i have multiple selects from the same table? then write the results to a table?

Thanks
Richie

First off what flavour of SQL is this?

I only do TSQL or PL/SQL so my answer is limited to these flavours of SQL. I would replace

where county = "kilkenny"

with

where county in ("kilkenny", "dublin", "cork", "limerick")

Adam

What exactly do you mean by multiple selects? Do you mean a select where "kilkenny" can be any county based on user input?

banbutcher

its mySQL, and basically what i want to do is show the latest property added to the database for each coulny in a table!

@Mirror, i dont know if multiple selects is the right terminology for it, but i taught thats what would be needed!

thanks again
Richie

Adam

Oh well If that's what you want to do, you want something like

$query="SELECT `RecordID`,`Ref_Num`,`Main_Image_Path`, unique(`County`) FROM properties ORDER BY `your_date_reference`";

This will return the most recent result for each county.

banbutcher

thanks mirror, but if i only wanted to show results from 8 counties rather than all counties, how would i go about that?
thanks

Adam

Ok, well I'm not an expert on efficiency of queries, but this should serve the purpose:

SELECT `RecordID`,`Ref_Num`,`Main_Image_Path`,`County` max(`Date_Registered`) FROM properties' WHERE county = "kilkenny" OR county = "dublin" OR county = "carlow" etc. GROUP BY `County`

This will return the max `Date_Registered` entry for each county specified in the WHERE criteria.

banbutcher

Mirror that works Perfect, thanks a million!
Richie

Adam

No problem, good luck with the project!