Professional Web Applications Themes

linked table problem - MySQL

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 ...

  1. #1

    Default 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



    Bob Guest

  2. #2

    Default Re: linked table problem

    On 6 Mar, 10:10, "Bob Bedford" <com> wrote: 

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

    Captain Guest

  3. #3

    Default Re: linked table problem

    On 6 Mar, 10:10, "Bob Bedford" <com> wrote: 

    If there are many departmentids, how do you decide which one you want
    to see in the results?

    Captain Guest

  4. #4

    Default Re: linked table problem

    "Captain Paralytic" <com> a écrit dans le message de
    news: googlegroups.com... 
    >
    > If there are many departmentids, how do you decide which one you want
    > to see in the results?[/ref]
    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







    Bob Guest

  5. #5

    Default Re: linked table problem

    On 6 Mar, 10:35, "Bob Bedford" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > 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 -[/ref]

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

    Captain Guest

  6. #6

    Default Re: linked table problem

    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



    Bob Guest

  7. #7

    Default Re: linked table problem

    On 6 Mar, 11:04, "Bob Bedford" <com> wrote: 

    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 ;-)

    Captain Guest

Similar Threads

  1. Open an MS Access Linked Table via ASP
    By Jason Whitaker in forum ASP Database
    Replies: 2
    Last Post: January 10th, 05:10 AM
  2. Connecting to a Linked Table in Access
    By cmb290t in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 17th, 09:23 PM
  3. Linked Table
    By StylusDesigns in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 3rd, 06:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139