Results 1 to 8 of 8

Thread: JOIN tables

  1. #1
    Join Date
    Sep 2003
    Posts
    19

    Unanswered: JOIN tables

    I have two tables with the same number of rows (100000) and one common column between them that I want to join. In other words I want to add the column named 'lastname' below to table1. I used INNER JOIN, but did not get the result I wanted, which is 100000 rows.

    table1
    ------
    id firstname
    1 bill
    1 bill
    2 george
    3 colin

    table2
    ------
    id lastname
    1 clinton
    1 clinton
    2 bush
    3 powell

    resulting table
    -------------
    id firstname lastname
    1 bill clinton
    1 bill clinton
    2 george bush
    3 colin powell

    Any ideas?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well outside of the fact you have invalid data ...

    SELECT TableA.FirstName, TableB.LastName FROM TableA INNER JOIN TableB ON TableA.ID=TableB.ID;
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2003
    Posts
    19
    Quote Originally Posted by M Owen
    Well outside of the fact you have invalid data ...

    This is done on purpose.

    SELECT TableA.FirstName, TableB.LastName FROM TableA INNER JOIN TableB ON TableA.ID=TableB.ID;
    I tried this and it gives me 6 rows. What I want is only 4 rows. Thanks for the reply.

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi hailu,

    Just a question, do you already have your tables setup with DATA in them? Sounds like you are either asking how to link tables later on after you have created them or trying to set them up. Also, it seems as though each table contains the same information. NOW, if that is the case are you trying to put all of the data into ONE table?
    If you are just setting them up then you need to structure your tables first, called Normalization. Then you set up your TableRelationships....meaning how One table will Relate to the Other table.
    In the case of what I see you saying in your example above you are simply keeping a database of Names. First, Last and not sure about the last table FirstnameLastname. Here is what I would reccommend which is much like a simple FlatFile:

    Table1
    FirstName
    LastName
    MiddleName

    Then, if you need to Show the data in a form as a full name you can Concatenate it like this:

    NamesForm
    [FirstName]&" "&[MiddleName]&" "&[LastName]

    That way you will have a TextBox that shows the FullName of each person in the database. Also, between each &" "& you can place a Seperator of your choice.

    See if this gets you where you need to be, and if not then come back and let it be known. Also, if you have the database already created, make a Copy, Replace real info. with dummy data, Zip and Post it here where myself or others can maybe fix it for you and return it.

    Have a nice one,
    Bud

  5. #5
    Join Date
    Jun 2004
    Posts
    6

    Join table1 plus Distinct(ID) from table2

    This might do what you're looking for... basically, you really want to limit one of the tables to a unique list by ID -- then join the two results:

    SELECT A.firstname, B.lastname
    FROM Table1 AS A, [select distinct(ID), Lastname from Table2]. AS B
    WHERE A.ID=B.ID;

    -smr

  6. #6
    Join Date
    Sep 2003
    Posts
    19
    Quote Originally Posted by sreedva
    This might do what you're looking for... basically, you really want to limit one of the tables to a unique list by ID -- then join the two results:

    SELECT A.firstname, B.lastname
    FROM Table1 AS A, [select distinct(ID), Lastname from Table2]. AS B
    WHERE A.ID=B.ID;

    -smr
    Thanks a lot smr! This works for me.

  7. #7
    Join Date
    Sep 2003
    Posts
    19
    Quote Originally Posted by Bud
    Hi hailu,

    Just a question, do you already have your tables setup with DATA in them?
    Bud
    I already had data in my table and there was a lot of redundant values in one of the columns that was done on purpose. Thanks a lot for your time, Bud.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by hailu
    I already had data in my table and there was a lot of redundant values in one of the columns that was done on purpose. Thanks a lot for your time, Bud.
    Very Welcome hailu and always try to do my part to assist others.

    have a nice one,
    Bud

Posting Permissions

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