Make-table issue - no data from Table B

Cegan
Cegan used Ask the Experts™
on
I would like to create a new table (C) from two tables (A & B).  A & B have like primary keys.  I have used a SELECT INTO, but only A's data will go in, not B's.  Although B's column headers are there, there is no data from B.  What am I doing wrong???

SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude INTO QCN_Table
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink, quiscivxref.maplink;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Possibly is there no corresponding maplink fields in both tables?
In the query you show us, your SELECT clause only uses one table: civicno_xyposen, or your "table A". If you want fields from quiscivxref, you need to add those to the SELECT clause.
(°v°)
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
if table B (quiscivxref) has no data, then you need a left join? but why do you join that table if it has no data? only for the order by? not that the "order by" for insert's usually has no meaning.

so, either you do:

SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude
INTO QCN_Table
FROM civicno_xyposen
LEFT JOIN quiscivxref
  ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink, quiscivxref.maplink;

or completely leave out that table B and the order by:
SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude
INTO QCN_Table
FROM civicno_xyposen

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
Okay, it's hard to explain this thoroughly, but here goes.  When I do this:
SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude INTO QCN_Table
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink, quiscivxref.maplink;
I get the four columns of data I want to add to the new table, QCN_Table.

However, I would like to add these four columns to the QCN_Table, AND, I would like to also add ALL of the columns from table quiscivxref too!  And when I try to do this with:
SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude, quiscivxref.* INTO QCN_Table
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink, quiscivxref.maplink;
I get only the columns of data from quiscivxref.  I do get column headers from civicno_xyposen, but no data.  It's weird, eh?

How can I get ALL of the columns from both tables?
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
do the column names you want to have from civicno_xyposen also exist in quiscivxref?
ie, does quiscivxref have a column name Easting ... etc?
if yes, you need to give them column alias names:

SELECT civicno_xyposen.Easting Easting_1, civicno_xyposen.Northing Northing_1, civicno_xyposen.Latitude Latitude_1, civicno_xyposen.Longitude Longitude_1, quiscivxref.* INTO QCN_Table
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink, quiscivxref.maplink;
You are doing it right. What do you see when you just open this query?

SELECT *
FROM civicno_xyposen INNER JOIN quiscivxref
ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink

Any data in the quiscivxref columns? If not, how come? Is there data in the table for the correct maplink values?

(°v°)
Good catch angel, didn't think of that! (^v°)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial