CODEWITHSUNDEEP
sqlms-accessjoin
vuyy1182
vuyy1182

Reputation: 1676

Inner join returns same columns from two tables access sql

I have 2 tables and i'm inner join them using EID

CSCcode   Description   BNO        BNO-CSCcode     E_ID

05078     blah1        5430      5430-05078       1098
05026     blah2        5431      5431-05026       1077
05026     blah3        5431      5431-05026       3011
04020     blah4        8580      8580-04020       3000
07620     blah5        7560      7560-07620       7890
07620     blah6        7560      7560-07620       8560
05020     blah1        5560      5560-04020       1056

Second table

        y/n        EID

          y       1056
          n       1098
          y       1077
          n       3011
          y       3000
          n       7890 
          n       8560

I'm selecting all fields from table one and y/n field from table 2. but it retrieve all from table 2 including EID. I don't want to retrieve EID from table2 because result table will have two EID fields.

My query

SELECT *, table2 .EID
FROM table1 INNER JOIN table2 ON table1 .E_ID = table2 .EID;

Upvotes: 1

Views: 380

Answers (5)

potashin
potashin

Reputation: 44581

You can't do things like this 'SELECT *, table2 .EID' - you should include all your fields from table 1. However, it is not a good practice even if you are selecting from one table.

SELECT 
   table1.CSCcode,
   table1.Descriptio,
   table1.BNO,
   table1.BNO-CSCcode,
   table1.E_ID, 
   table2 .EID
FROM 
   table1 INNER JOIN table2 ON table1 .E_ID = table2 .EID

Upvotes: 1

Brad
Brad

Reputation: 12245

The reason you sometimes get stuck with a surprise * is because of a property in the query designer. Change this to No in the designer. Alternatively, like the other answers say, just remove the * from your query.

enter image description here

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

Specify the table from which you want to select all fields modify your query to

SELECT
    table1.*, table2.EID
FROM
    table1
    INNER JOIN table2
        ON table1.E_ID = table2.EID;

If you are using the query designer, Access sometimes sets the property Output All Fields of the query to Yes. Make sure it is set to No and select the fields you want manually. (Right click in the designer and select Properties... in the context menu in order to see the properties.)

Upvotes: 1

Zach Lesperance
Zach Lesperance

Reputation: 347

Your query is selecting everything from both tables as well as specifically the EID column from table 2. Change it to:

SELECT table1.*, table2.y/n
...

Although, as stated in other answers, avoid using * and instead list the column names table.columnName, etc.

Upvotes: 1

Guffa
Guffa

Reputation: 700242

"I'm selecting all fields from table one"

No, you are selecting all fields from all tables. You need to specify the table if you only want all fields from one table:

SELECT table1.*, table2.EID

However, using * is not good practive. It's better to specify the fields that you want, so that any field that you add to the table isn't automatically included, as that might break your queries.

Upvotes: 2

Related Questions