SQL关联查询返回正确值但id字段异常的解决求助

Hey there! I totally get your frustration—dealing with a 900-column table means manually listing every field is impossible, and that random id value breaking your edit page is a real headache. Let's break down what's happening and fix it.

The Root Problem #

When you use SELECT * with a JOIN between two tables that both have an id column (which I'm guessing is the case here), your database returns both id columns—but your application is probably picking up the wrong one (either from spouse_details or whichever the database returns first/last), hence the "random" incorrect value.

Simple Solutions That Don't Require Listing 900 Columns #

1. Explicitly Specify the Correct id First, Then Include All Other Columns

Just explicitly call out the id from the table you care about (I assume that's register_bs), then use table.* to pull in all other columns from both tables. This ensures the correct id is at the front, and your application will prioritize it over the duplicate id from the other table:

SELECT register_bs.id, register_bs.*, spouse_details.*
FROM register_bs
INNER JOIN spouse_details ON register_bs.reg = spouse_details.reg
WHERE register_bs.country NOT IN('Australia', 'USA', 'Germany', 'Canada');

Pro tip: Use table aliases to clean this up and make it more readable:

SELECT rb.id, rb.*, sd.*
FROM register_bs rb
INNER JOIN spouse_details sd ON rb.reg = sd.reg
WHERE rb.country NOT IN('Australia', 'USA', 'Germany', 'Canada');

(Note: I added rb. to country to avoid ambiguity—if country is in spouse_details, swap it to sd.country instead.)

2. Exclude the Unwanted id (Database-Specific)

If you want to avoid duplicate id columns entirely, some databases support syntax to exclude specific columns from *:

• PostgreSQL: Use the EXCLUDE clause

  SELECT rb.id, rb.*, sd.* EXCLUDE (id)
  FROM register_bs rb
  INNER JOIN spouse_details sd ON rb.reg = sd.reg
  WHERE rb.country NOT IN('Australia', 'USA', 'Germany', 'Canada');
  

• For databases like SQL Server, excluding a column from * requires more complex dynamic SQL—so the first method is far simpler for your use case.

Why This Works #

By explicitly defining register_bs.id first, you're ensuring that the correct id is the first one returned in your result set. Even though register_bs.* will include id again, most applications will use the first occurrence of the column name they find—so your edit page will pull the right value.

内容的提问来源于stack exchange，提问作者Seep Sooo