Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Unanswered: Insert Into Left Join

    Hello everyone, I'm wondering if its possible to insert the result of a left join comparsion statement (see the following).

    SELECT *
    FROM demographics
    LEFT JOIN demo ON demographics.MPI = demo.MPI
    WHERE demo.MPI IS NULL;

    The above statement compares demo vs. demographics and give me a list of records that are on demographics that are not in demo, this worked great.

    Now I want to insert the result of that statement into demo, easier said than done I added INSERT INTO Demo in front of the statement above.

    INSERT INTO demo
    SELECT *
    FROM demographics
    LEFT JOIN demo ON demographics.MPI = demo.MPI
    WHERE demo.MPI IS NULL;

    #1136 - Column count doesn't match value count at row 1

    These two tables are idenitcal the same table structure and field types. I know this because i copied the table and renamed one of them. The only differences between the two are demo has 15 records and demographics have 17 records. Can someone please point me at the right direction.

    Thank you very much!!!

    Joe

  2. #2
    Join Date
    Aug 2004
    Posts
    6
    One more thing I forgot to mention that this works:

    INSERT INTO demo
    SELECT *
    FROM demographics
    WHERE MPI =17;

    This is why it is odd that with a LEFT JOIN statement it crashes because it follows the same structure, an INSERTstatment followed by a SELECT statment.


    Thanks again

    Joe

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    The left join will bring back columns from both tables if you use the *. This will result in too many columns being returned to the insert statement. Try using the column names instead of *. (It's also a good practice in general to avoid using the * in SQL statements.)

  4. #4
    Join Date
    Aug 2004
    Posts
    6
    urquel thanks for the reply. What you pointed out does make alot of sense however i still have no luck with it, this is what I did.

    INSERT INTO `demo` ( `MPI` , `ID1` , `ID2` , `date entered` , `first name` , `MI` , `last name` , `occupation` , `home phone` , `work phone` , `ssn` , `address` , `city` , `state` , `zip` , `complaint` , `medication` , `pcphysician` , `staffphys` , `refphysician` , `age` , `dob` , `sex` , `marital status` , `race` , `information given by` , `admitted by` , `admitting diagnosis` , `date modified` , `height` , `weight` , `bmi` , `psgnum` , `latestpsgdate` , `msltnum` , `latestmsltdate` )
    SELECT ( `MPI` , `ID1` , `ID2` , `date entered` , `first name` , `MI` , `last name` , `occupation` , `home phone` , `work phone` , `ssn` , `address` , `city` , `state` , `zip` , `complaint` , `medication` , `pcphysician` , `staffphys` , `refphysician` , `age` , `dob` , `sex` , `marital status` , `race` , `information given by` , `admitted by` , `admitting diagnosis` , `date modified` , `height` , `weight` , `bmi` , `psgnum` , `latestpsgdate` , `msltnum` , `latestmsltdate` )
    FROM demographics
    LEFT JOIN demo ON demographics.MPI = demo.MPI
    WHERE demo.MPI IS NULL

    #1052 - Column 'MPI' in field list is ambiguous

    Then I thought my first step should try to create the same result as using *. I can't even do that I replaced * with ( `MPI` , `ID1` , `ID2` , `date entered` , `first name` , `MI` , `last name` , `occupation` , `home phone` , `work phone` , `ssn` , `address` , `city` , `state` , `zip` , `complaint` , `medication` , `pcphysician` , `staffphys` , `refphysician` , `age` , `dob` , `sex` , `marital status` , `race` , `information given by` , `admitted by` , `admitting diagnosis` , `date modified` , `height` , `weight` , `bmi` , `psgnum` , `latestpsgdate` , `msltnum` , `latestmsltdate` ) no luck error #1052 - Column 'MPI' in field list is ambiguous. and those are all the fields.

    Thanks You

    Joe

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to tell it which of the MPI values to use in your SELECT list. Nevermind that you tell it they have to be the same in your WHERE clause, it still wants to know which one you meant in the SELECT list (sometimes database engines can be a wee bit dense).

    -PatP

  6. #6
    Join Date
    Aug 2004
    Posts
    6
    Got it!!!!! thank you very much!!!!!

    Joe

Posting Permissions

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