Professional Web Applications Themes

join two tables with sharing some columns between two - MySQL

Hi, I have two tables like these: (this is an example, the actual tables have diffferent fields and meanings) TABLE1 id person_name date_arrival date_departure TABLE2 id car_description date_arrival date_departure I'd like to make a query to have such resulting table RESULTING_TABLE id person_name car_description date_arrival date_departure the id is the primary key for the three tables and it's unique for the three (id in table1 cannot be in table2, I use the same counter to generate the id for table1 and table2). I is possible to create such a query? Thanks...

  1. #1

    Default join two tables with sharing some columns between two

    Hi,

    I have two tables like these: (this is an example, the actual tables
    have diffferent fields and meanings)


    TABLE1
    id
    person_name
    date_arrival
    date_departure


    TABLE2
    id
    car_description
    date_arrival
    date_departure


    I'd like to make a query to have such resulting table


    RESULTING_TABLE
    id
    person_name
    car_description
    date_arrival
    date_departure


    the id is the primary key for the three tables and it's unique for the
    three (id in table1 cannot be in table2, I use the same counter to
    generate the id for table1 and table2).


    I is possible to create such a query?


    Thanks

    filippo Guest

  2. #2

    Default Re: join two tables with sharing some columns between two

    filippo wrote:
    > Hi,
    >
    > I have two tables like these: (this is an example, the actual tables
    > have diffferent fields and meanings)
    >
    >
    > TABLE1
    > id
    > person_name
    > date_arrival
    > date_departure
    >
    >
    > TABLE2
    > id
    > car_description
    > date_arrival
    > date_departure
    >
    >
    > I'd like to make a query to have such resulting table
    >
    >
    > RESULTING_TABLE
    > id
    > person_name
    > car_description
    > date_arrival
    > date_departure
    >
    >
    > the id is the primary key for the three tables and it's unique for the
    > three (id in table1 cannot be in table2, I use the same counter to
    > generate the id for table1 and table2).
    >
    >
    > I is possible to create such a query?
    >
    >
    > Thanks
    >
    If the id in table 1 and table 2 are unique between the two tables, how do you
    relate table 1 to table 2?

    To make a connection between the two you have to be able to identify some
    relationship between the two tables.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: join two tables with sharing some columns between two

    Jerry Stuckle ha scritto:
    > If the id in table 1 and table 2 are unique between the two tables, how do you
    > relate table 1 to table 2?
    >
    > To make a connection between the two you have to be able to identify some
    > relationship between the two tables.
    that's what I need:

    resulting tables merging the two, es

    table1
    1 - john - 12/12/2005
    5 - bill - 12/27/2005
    12 - sabrina - 12/01/2005

    table 2
    3 - car - 1/15/2005
    9 - roulotte - 1/1/2006

    RESULTING TABLE
    1 - john - 12/12/2005
    5 - bill - 12/27/2005
    12 - sabrina - 12/01/2005
    3 - car - 1/15/2005
    9 - roulotte - 1/1/2006

    filippo Guest

  4. #4

    Default Re: join two tables with sharing some columns between two


    Peter H. Coffin ha scritto:
    > Look up UNION.
    >
    > SELECT * FROM table1
    > UNION
    > SELECT * FROM table2;
    the problem with UNION is that the all columns must be matched each
    other. I want to match only some column (the column with the same
    meaning) but leave some other column unique for each table:

    ie. table 1 is a person's list, so contain first name; table 2 is tools
    list, so it has description and weight. Is it possible?

    Thanks

    filippo Guest

  5. Moderated Post

    Default Re: join two tables with sharing some columns between two

    Removed by Administrator
    Gordon Burditt Guest
    Moderated Post

  6. #6

    Default Re: join two tables with sharing some columns between two


    Peter H. Coffin ha scritto:
    > SELECT f_name, null, test_date, null, null FROM table1
    > UNION
    > SELECT null, t_name, test_date, description, weight FROM table2;
    >
    (snip)
    >
    > SELECT 'person',f_name, null, test_date, null, null FROM table1
    > UNION
    > SELECT 'tool',null, t_name, test_date, description, weight FROM table2;
    thanks Peter and Gordon, this is exactly what I need.
    > (I must point out, however, that in spite of all this working, it is the
    > kind of working that makes me think there MUST be a better way of
    > accomplishing the actual work you want done. I don't think I could even
    > make up a situation where this would be exactly the right solution to
    > a hypothetical problem....)
    I'm sure you're right. I wrote my application without a strong
    knlowledge about database theory (but some info on normalizarion) so
    I'm sure there is a better way. I will fix some database bad designs
    for the next revision (it want to port my Perl/Tk app to Python/GTK)

    Thanks very much to you guys,

    Filippo

    filippo Guest

Similar Threads

  1. Listing join from tables...
    By createmedia in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 5th, 09:02 PM
  2. Select, Join & field values - 2 tables
    By Justin Koivisto in forum MySQL
    Replies: 3
    Last Post: February 13th, 08:06 PM
  3. New to Joines - Inner Join on 4 Tables
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: June 14th, 03:14 PM
  4. HELP.. Recordset JOIN tables
    By Addy_31 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 4th, 01:51 AM
  5. SELECT from multiple tables (not join though)
    By Madison Kelly in forum PostgreSQL / PGSQL
    Replies: 15
    Last Post: January 10th, 07:43 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