List:General Discussion« Previous MessageNext Message »
From:Pure Web Solution Date:March 28 2006 8:38am
Subject:Re: Complicated select query
View as plain text  
try something like this

select customer,max(time),name
from customers join projects on projects.parent_id=customer.id
group by customer;

regards

Pure Web Solution
http://www.purewebsolution.co.uk
PHP, MYSQL, Web Design & Web Services

Barry <Barry@stripped> wrote:

> Gabriel PREDA wrote:
> > About the first problem I think you need to give us more data !
> > 
> 
> Okay i thought yesterday how i can make up some real problem and have this:
> 
> Guess you have a database with customers and a database with projects.
> Those prijects have timestamps so you know what time he added that project.
> 
> Now my problem is to get the First project the customer has added.
> And i want it to have it like
> 
> +--------+----------+---------------+
> |Customer|Time      |Projectname    |
> +--------+----------+---------------+
> |John    |2005-11-23|Teatime        |
> |Jim     |2005-02-19|Having Lunch   |
> |Bob     |2005-06-11|My big Project!|
> +--------+----------+---------------+
> 
> So that i have the first project every customer has added first and only 
> that.
> 
> But the Databases looks like
> 
> Customer:
> +--+----+------+--------------------+-------+-------------+
> |id|Fnam|Snam  |Street              |Code   |City         |
> +--+----+------+--------------------+-------+-------------+
> |01|John|Doh   |Funnystreet 4       |87624  |Somewherecity|
> |02|Jim |Bobjoe|Anotherfunnystreet 8|213+E13|Othercity    |
> |03|Bob |Joejim|boringstreet 67     |324456 |Boringcity   |
> +--+----+------+--------------------+-------+-------------+
> 
> Projects
> +--+---------+----------------+----------+
> |id|parent_id|Name            |Time      |
> +--+---------+----------------+----------+
> |01|01       |Teatime         |2005-11-23|
> |02|01       |Suppertime      |2005-12-14|
> |03|02       |having Lunch    |2005-02-19|
> |04|02       |having Dinner   |2005-04-12|
> |05|02       |having something|2005-07-17|
> |06|03       |My small Project|2005-02-10|
> |07|03       |My big Project! |2005-06-11|
> +--+---------+----------------+----------+
> 
> I hope this enlights it a bit
> 
> Barry




Thread
Complicated select queryBarry27 Mar
  • Re: Complicated select queryGabriel PREDA27 Mar
    • Re: Complicated select queryBarry28 Mar
      • Re: Complicated select queryBarry28 Mar
      • Re: Complicated select queryPure Web Solution28 Mar
  • Re: Complicated select queryPeter Brawley27 Mar