4 Replies Latest reply on Oct 2, 2008 8:27 PM by mrcharis2003@yahoo.c

    Correct way to join two tables....using QUB??

    mrcharis2003@yahoo.c Level 1
      Hi there

      I am having a really hard time displaying data from two joined tables so I would just like to ask a quick question to make sure I am joining the tables correctly.....

      I have two tables paintings and artists

      in the painting table I have these fields

      id_painting int 200 auto inc primary key
      name_painting varchar 200
      price_painting varchar 200
      artist_painting int 20
      datadded_painting datetime 200 0000-00...bla bla bla

      And a artist table with these fields
      id_artist int 200 auto increment primiry key
      name_artist varchar 200
      phone_artist varchar 200
      datadded-artist varchar 200

      I decided to put all the painting information in only one table so it is easier to upload and sort

      I want to be able to sort the records later by using the artist_painter field

      So.... now i have to join the tables

      I want to join the artist_painting field in the painting table to the id_artist field in the artist table

      So.... i open a new page and click insert a recordset then advanced then QUB then I put the two tables up on the QUB window and drag a line ... a link.... between the artist_painting field in the painting table and the id_artist field in the artist table....

      Does that sound correct.....

      My questions are ..... does the field artist_painting have to be an INT interger field? or just a varchar....

      Do I have to make both the tables inobDB not myisiam?

      Any help would be great this is giving me a headache
      I cannot find any relevant info on google

      Thanks for any help
      Have a nice day
        • 1. Re: Correct way to join two tables....using QUB??

          To me it looks just like you need to link the two tables using a parent & child relationship, this means having a foreign id in the child table which has a value the same as the autonumber Id field from the first. You can link the two tables together in QUB by dragging the f_id from the second table onto the parent autonum id.

          • 2. Re: Correct way to join two tables....using QUB??
            mrcharis2003@yahoo.c Level 1
            hi there thanks for the reply

            i know about joining tables in QUB .... I have done before and had it all working fine.... I had a product table and a category table.. and could link ok....

            but that was a while ago.... and i forgot exactly what I needed to do to set up the tables correctly

            I read somewhere a long time ago that the storage engine has to be inoDB
            not myisiam and that the field that links the first table has to be an INT field (the foreign key field) with a default of '0' to the primary key of the second

            I went back and changed my tables and got them joined ok

            But I still don't know exactly why I had to set the default value in the linked field to '0' or why the field has to be INT ??

            If you could tell me why .... that would be great

            but all is working fie now

            Thanks for your help much appreciated

            have a great day
            • 3. Re: Correct way to join two tables....using QUB??
              Level 1
              Hi well I personally would say that the main difference between MyISAM & InnoDB comes down to security inoDB is a bit more secure. InoDB does not lock rows but MyISAM locks tables which prevents multipal changes in the same table at the same time. there has quite a few forum threads regarding speed but this is totally dependent on db structor InnoDB is transactional, supports Foreign Key and has an operational overhead.

              If I remember correctly (I use falcon now)a foreign key field cannot be set as default to NULL and by its nature it would need to be an interger to link correctly
              • 4. Re: Correct way to join two tables....using QUB??
                mrcharis2003@yahoo.c Level 1
                Thank you very much for your response

                that makes sense to me

                Have a great day thanks again