How To Remove a Second Id From JOIN In SQL

Question

I'm new to programming in SQL, and I need help on removing the second 1 through 5 id's on the right from my output (the numbers are highlighted in bold):

ID	Name	Gender	ID	Country
1	Abdur-Rahman	M	1	America
2	Don Madden	M	2	England
3	Dustin Tompkins	M	3	America
4	Nicki Harris	F	4	Germany
5	Samantha Harris	F	5	France

    CREATE TABLE test ( id INTEGER PRIMARY KEY, name TEXT); 
      INSERT INTO test VALUES (1,"Albert Franco"); 
      INSERT INTO test VALUES (2,"Don Madden");
      INSERT INTO test VALUES (3,"Dustin Tompkins");
      INSERT INTO test VALUES (4,"Nicki Harris");
      INSERT INTO test VALUES (5,"Samantha Harris");
      
      ALTER TABLE test
      ADD COLUMN gender TEXT;
      
      UPDATE test
      SET gender = 'M'
      WHERE id = 1;
      
      UPDATE test
      SET gender = 'M'
      WHERE id = 2;
      
      UPDATE test
      SET gender = 'M'
      WHERE id = 3;
      
      UPDATE test
      SET gender = 'F'
      WHERE id = 4;
      
      UPDATE test
      SET gender = 'F'
      WHERE id = 5;
      
      CREATE TABLE country (
      
     id INTEGER,
     nation TEXT
     );
     
      INSERT INTO country VALUES (1,"America");
      INSERT INTO country VALUES (2,"England");
      INSERT INTO country VALUES (3,"America");
      INSERT INTO country VALUES (4,"Germany");
      INSERT INTO country VALUES (5,"France");
     
      SELECT * FROM test
      JOIN country
      ON test.id = country.id;

Does this answer your question? [Why is SELECT \* considered harmful?](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) — Madhukar, Sep 09 '21 at 07:45

GarethD · Accepted Answer · 2021-09-12T19:31:03.003

To actually answer your question, you should explicitly state the columns you want, i.e.

SELECT t.id, t.name, t.gender, c.nation
FROM   test AS t
       JOIN country AS c
          ON c.id = t.id;

It is however worth noting that your schema doesn't really make sense, you have to duplicate countries, which is not normalised. You've simply created a 1:1 relationship, you'd be as well just adding a nation column to test.

A better solution though would be to normalise the data, so your country table would become:

CREATE TABLE Country
( 
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Nation VARCHAR(50)
);
INSERT INTO Country(Nation)
VALUES ('America'), ('England'), ('France'), ('Germany');

Then in your Test Table, add CountryId as foreign key to your country table:

CREATE TABLE Test
(
    Id  INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(500) NOT NULL,
    Gender CHAR(1) NOT NULL,
    CountryId INT NOT NULL,
  CONSTRAINT FK_Test__CountryId FOREIGN KEY (CountryID) REFERENCES Country (Id)
);
INSERT INTO Test (Name, Gender, CountryId)
SELECT data.Name, data.Gender, c.Id
FROM (
     SELECT 'Albert Franco' AS Name, 'M' AS Gender, 'America' AS Nation
     UNION ALL
     SELECT 'Don Madden' AS Name, 'M' AS Gender, 'England' AS Nation
     UNION ALL
     SELECT 'Dustin Tompkins' AS Name, 'M' AS Gender, 'America' AS Nation
     UNION ALL
     SELECT 'Nicki Harris' AS Name, 'F' AS Gender, 'Germany' AS Nation
     UNION ALL
     SELECT 'Samantha Harris' AS Name, 'F' AS Gender, 'France' AS Nation
    ) AS data
    INNER JOIN Country AS c
        ON c.Nation = data.Nation;

Your final query is largely similar:

SELECT t.Id, t.Name, t.Gender, c.Nation
FROM Test AS t
     INNER JOIN Country AS c
         ON c.Id = t.CountryId;

But you have now normalised your countries, so America only appears once. Obviously in a simple example this only saves you one row, but if you have a lot of names, this has saved a lot of duplication, and potential for error. With free type entry (e.g. having a nation column in test) you inevitably end up with multiple variations of everything, e.g. "America", "USA", "U.S.A", "US", "U.S", "United States", this doesn't even consider typos! All of this leads to headaches down the road.

Full Example on SQL Fiddle

^{*N.B. There's a pretty good argument that the country table should not have a surrogate primary key (AUTO_INCREMENT) and it should instead use the ISO country code. The natural vs surrogate keys debate has been going on for years and years, and is well beyond the scope of this answer}

score 0 · Answer 2 · answered Sep 09 '21 at 07:41

0

If I understood your question correctly, you want something like this

Then you need to write your query like this

Select test.*, country.nation from test join country on test.id=country.id;

Now, you will only receive the id from your test table

answered Sep 09 '21 at 07:41

Ankur Kumar

46
7

Recommending to use `select *` is not something you should be doing, it's a bad habbit and an anti-pattern to avoid. – Stu Sep 09 '21 at 08:42
I agree with your view. I recommended it to the user because it was a very small table. However, I will rewrite the query for your reference. select t.id, t.name, t.gender, c.nation from test t join country c on c.id = t.id – Ankur Kumar Sep 09 '21 at 14:29

How To Remove a Second Id From JOIN In SQL

2 Answers2