Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jan 2004
    Posts
    45

    Unanswered: Struggling with Innodb

    Being very new to Databasing, Innodb is confusing the crap out of me.

    I have built two tables.

    Customer Info and Equipment.

    Both tables have an "equipaccount" column. In the customer info table this is my primay key as customers can only have one number. In my equipment table it is used for relation purposes. To make things easy I have created two queries if you search by equipment account it will bring up he account info as well as the equipment bought. It works but seems to be a bandaid.

    I want to beable to search by phone number and customer name but, do not want to add those columns to the equipment table as I have heard fo foreign keys.

    From what I understand I should bebable to create a foreign key (or mabe muliples in the equipment table and relate it to the primary key in the customer info table. Is my understanding correct? This way I could have one query (maybe) that could tell what I was searching ( Equipment account, Phone, or Name) and display the correct information fomr both tables.

    As i am building test databases in Innodb, I must be missing something as they always end up being built MyISAM. What do I have to consider when building an Innodb table. I have set foriegn keys, but they are not the first column, are they supposed to be?

    I am using a GUI since I am new and hand coding a table takes me forever and a week (but i guess it would make me learn).

    What am I missing to create these tables. I have tried to search for a tutorial on them but have found very little.

    Any help would be appreciated.

    Thanks
    Josh

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whatever your gui is, it must have some way of displaying the table layout, i.e. the columns and their properties

    can you please show those, so that we can see what you're doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    45
    I could not figure out a cut and paste so i took some screen shots

    This is my customer info table
    Attached Thumbnails Attached Thumbnails customerinfotable.jpg  

  4. #4
    Join Date
    Jan 2004
    Posts
    45
    and here is my equipment info table

    I am looking to search by equipment account, phone and compnay name from customerinfo, and pull info from both tables.
    Attached Thumbnails Attached Thumbnails equipmenttable.jpg  

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks like you do not have a primary key defined in the equipment table

    your ability to run a query that searches on fields of the customer table, does not include those fields in the result, but does return fields from both customer and equipment tables, has nothing to do with whether you have primary or foreign keys defined

    primary and foreign keys really only prevent data inconsistencies such as adding equipment for an account that doesn't exist in the customer table, or deleting a customer if there are equipment rows for it

    if you do not want the database handling those inconsistency checks for you automatically, you could as easily declare myisam tables as innodb

    if you do, you will have to declare the account in the equipment table as a foreign key to the account in the customer table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Posts
    45
    Do foreign keys allow duplicates?

    The way I have set it up is if there is an account number for every model and serial number so there could be as many as 100 of the same account numbers in that column, I know primary keys do not allow duplicates.


    Fron what you are saying I should bebable to pull the information from both tables in one query (should I build it correctly) whether I have keys or not. So in reality I could pull the information buy any information in the customer or equipment field if I set the query right?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of course foreign keys allow duplicates

    just don't make equipaccount on the equipment table the primary key -- primary keys don't allow duplicates

    here's a typical query:
    Code:
    select C.equipmentaccount
         , C.Company_name
         , C.address
         , C.city
         , C.zip_code
         , C.phone_number
         , E.make
         , E.modelnumber
         , E.serialnumber
         , E.deliverydate
      from customer_info C
    inner
      join equipment E
        on C.equipaccount = E.equipaccount
     where C.city = 'Smallville'
        or C.zipcode like '123%'
        or C.phone_number like '937%'
    as i said, you don't need primary and foreign keys to be able to run queries like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Posts
    45
    on C.equipaccount = E.equipaccount
    where C.city = 'Smallville'
    or C.zipcode like '123%'
    or C.phone_number like '937%'

    Thanks for all the help. Above can I assume that I coulds define the 'values' to a form so it will search whatever it typed in to the form by the user(s)?

  9. #9
    Join Date
    Jan 2004
    Posts
    45
    I tried a version of the code you gave me to see if it would do waht I wanted

    SELECT C.equipmentaccount,
    C.company_name,
    C.address,
    C.city,
    C.zip_code,
    C.phone_number,
    E.make,
    E.modelnumber,
    E.serialnumber,
    E.deliverydate,
    C.state,
    C.contact_name,
    C.account_type,
    C.email,
    C.fax_number,
    C.`phone_number2`,
    C.partsaccount
    FROM customer_info C,
    equipment E
    WHERE
    (
    (equipmentaccount = 'KLEE60')
    )

    This worked and oly pulled up the KLEE60 account name, but it pulled all of the equipment in the Equipment table and related them to KLEE60.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the reason it pulled out all the equipment in the Equipment table is because your version of my query did not have the proper join condition

    do me a favour, okay?

    try your query again, but remove these lines --
    Code:
    FROM customer_info C,
    equipment E
    and insert these lines --
    Code:
      from customer_info C
    inner
      join equipment E
        on C.equipaccount = E.equipaccount
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2004
    Posts
    45
    The query runs but diplays no information.
    ---------------------------------------------
    SELECT C.equipmentaccount,
    C.company_name,
    C.address,
    C.city,
    C.zip_code,
    C.phone_number,
    E.make,
    E.modelnumber,
    E.serialnumber,
    E.deliverydate,
    C.state,
    C.contact_name,
    C.account_type,
    C.email,
    C.fax_number,
    C.phone_number2,
    C.partsaccount
    from customer_info C
    inner
    join equipment E
    on C.equipmentaccount = E.equipaccount
    WHERE
    (
    (C.equipmentaccount = 'KLEE60')
    )
    --------------------------------------------

    the C. column is equipmentaccount
    while the E. column is equipaccount
    is there a prolbem here?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, sorry, yeah, i was a bit too fast & loose trying to type column names into my trext editor from your screen shots

    you seem to be well on your way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2004
    Posts
    45
    This brought back just compnay info no equipment info:
    All I did was change it to LEFT instead of INNER (RIGHT and OUTER did nothing and I know little of these command I am just hacking at it).
    -----------------------------------------------------
    SELECT C.equipmentaccount,
    C.company_name,
    C.address,
    C.city,
    C.zip_code,
    C.phone_number,
    E.make,
    E.modelnumber,
    E.serialnumber,
    E.deliverydate,
    C.state,
    C.contact_name,
    C.account_type,
    C.email,
    C.fax_number,
    C.phone_number2,
    C.partsaccount
    from customer_info C
    left
    join equipment E
    on C.equipmentaccount = E.equipaccount
    WHERE
    (
    (C.equipmentaccount = 'KLEE60')
    )
    ------------------------------------------------------------

  14. #14
    Join Date
    Jan 2004
    Posts
    45
    Originally posted by r937
    oops, sorry, yeah, i was a bit too fast & loose trying to type column names into my trext editor from your screen shots

    you seem to be well on your way
    Sorry I have been thinking to fast.

    The first full script I gave produce no information with the correct C. and E. names

    The second I messed with and inserted Left instead of Inner and got just company info.

    I am going ot go back and play with it.


    BTW: Thanks for all your help, I am understanding this more and more now.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you got only company info with a left outer join, it means your data is inconsistent, there really is no equipment for that company
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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