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.