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

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

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.)

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

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

Got it!!!!! thank you very much!!!!!

Joe