linked table problem

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

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

Could you post the schemas and some sample data as this structure is
not making much sense at the moment.

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?

"Captain Paralytic" <paul_lautman@yahoo.com> a écrit dans le message de
news: 1173176254.801146.69440@64g2000cwx.googlegroups.co m...
> 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

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

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

Thanks for the link but I can't get it to work.

Isn't there a way like a subquery ?

select * from client left join department
on client.clientid = department.clientid
where department.departmentid = (select min(departmentid from deparment
where clientid = client.clientid)).

Not so confortable with SQL language to know the correct syntax and if it's
possible or not.

Bob

On 6 Mar, 11:04, "Bob Bedford" <b...@bedford.com> wrote:
> You need the (now) famous "Strawberry Query". See the pattern for it
> in this post:http://tinyurl.com/yex7s2
>
> Thanks for the link but I can't get it to work.
>
> Isn't there a way like a subquery ?
>
> select * from client left join department
> on client.clientid = department.clientid
> where department.departmentid = (select min(departmentid from deparment
> where clientid = client.clientid)).
>
> Not so confortable with SQL language to know the correct syntax and if it's
> possible or not.
>
> Bob

You DON'T want to use a subquery. The most efficient way to do this is
with the Strawberry Query.
I'll happily help you out, but I need more information than "I can't
get it to work".

What doesn't work? What do you get? ...

Earlier on I asked you for schema and sample data. Still seen no
sample data.

If you post the schema in the form of CREATE TABLE statements and the
sample data in he form of inserts, I can setup the tables myself to
check that the results look like what you are expecting (you were
gonna give sample required results weren't you ;-)