问题描述:

Basically I'm trying to combine two tables together into a new table. The first table contains information on users and the second table stores information on purchases along with address specific information.

The end result would be a new updated table for Users that now includes addresses for each user (eg. zipcode, state, country) that will be merged from the Deals_Users_Assoc table.

Here's the query that I have so far:

INSERT INTO NewUsers (User_ID, FirstName, LastName, City, StateProvince, Country, ZipCode, Username, Username_Clean, Password, Email, ActivationToken, LastActivationRequest, LostPasswordRequest, Active, Group_ID, SignUpDate, LastSignIn)

SELECT DISTINCT a.User_ID, a.FirstName, a.LastName, b.address_city, b.address_state, b.address_country, b.address_zip, a.Username, a.Username_Clean, a.Password, a.Email, a.ActivationToken, a.LastActivationRequest, a.LostPasswordRequest, a.Active, a.Group_ID, a.SignUpDate, a.LastSignIn

FROM Users a, Deals_Users_Assoc b

WHERE a.User_ID = b.user_id

GROUP BY a.User_ID;

It's working, but only inserting data for users that are also stored in the Deals_Users_Assoc table (WHERE a.User_ID = b.user_id). Hundreds of user id's are still remaining in the Users table.

Any help would be appreciated!

网友答案:

Try to use SELECT query with LEFT JOIN clause -

SELECT a.User_ID, a.FirstName, a.LastName, b.address_city, b.address_state, b.address_country, b.address_zip, a.Username, a.Username_Clean, a.Password, a.Email, a.ActivationToken, a.LastActivationRequest, a.LostPasswordRequest, a.Active, a.Group_ID, a.SignUpDate, a.LastSignIn
FROM Users a
LEFT JOIN Deals_Users_Assoc b
  ON a.User_ID = b.user_id
GROUP BY a.User_ID;
网友答案:

In your select distinct query, change from the alias a = alias b to a Left Outer Join syntax.

网友答案:

I think what you want to say is

...
from users a
left join deals_users_assoc b on b.user_id=a.user_id

Note, though, that if it's possible to have more than one address for a given user_id, the distinct clause will give you one record for each such address, not one for each user_id. The "group by" has no effect because you have no aggregate fields, i.e. no fields with a max, count, sum, etc.

If you want only one address per user_id, you'll have to decide how to pick one. You could pick one arbitrarily by saying max(b.address_city), etc. But that's essentially picking one at random.

相关阅读:
Top