On 6 Mar, 10:35, "Bob Bedford" <b...@bedford.com> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> a écrit dans le message denews: 1173176254.801146.69__BEGIN_MASK_n#9g02mG7!__...__ END_MASK_i?a63jfAD$z__@64g2000cwx.googlegroups.com ...
>
>
>
> > On 6 Mar, 10:10, "Bob Bedford" <b...@bedford.com> wrote:
> >> Hi all,
>
> >> I've a problem with a mysql query. I've a table with an unique ID
> >> (company).
> >> This ID is taken by many records in an other table (departments). Since I
> >> do
> >> a link between the tables, I'd like to have only one record and not many
> >> occurences as they are departments:
>
> >> select companyid, departmentid from company left join departments on
> >> compant.companyid = departments.companyid
> >> inner join ....
>
> >> Since I can't group by as I've many records I want from this query, how
> >> to
> >> do it ?
> >> In fact I'm retrieving all orders from a company but every order is shown
> >> as
> >> many times as they are departments in a company.
>
> >> Thanks for helping.
>
> >> Bob
>
> > If there are many departmentids, how do you decide which one you want
> > to see in the results?
>
> I'd like the first deparment...here is the schema:
>
> table client:
> clientid : mediumint
> name: varchar(50)
>
> table department
> idrecord: mediumint
> clientid : mediumint (linked to table client)
> departmentid : mediumint
> departmentname : varchar(50)
> address : varchar(50)
> phone....
>
> table order
> orderid :mediumint
> articleid: mediumint
> number: mediumint
> value: float (10,2)
> purchdate: date
>
> so now I'd like to have all orders for every client, taking the first
> deparment (the one with the littlest idrecord in table department) for the
> department link between client and department.
> The idea is to have this result:
> order.orderid, order.articleid, order.number, order.value, client.clientid,
> client.name, client.name, department.address, department.phone order by
> client.clientid, order.purchdate
>
> Thanks for helping.
>
> Bob- Hide quoted text -
>
> - Show quoted text -

You need the (now) famous "Strawberry Query". See the pattern for it
in this post:
http://tinyurl.com/yex7s2