asked on

Make-table issue - no data from Table B

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;

Atropa

Possibly is there no corresponding maplink fields in both tables?

Markus Fischer

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]

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

Cegan

ASKER

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?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Markus Fischer

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

Markus Fischer

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