| Author |
Topic |
|
kangaroo_m
Starting Member
9 Posts |
 Posted - 2011-02-03 : 12:36:51
|
| I have 2 tables that hold data, table1 has 3k rows table2 has 9k. They both have a user_id feild. I want to combine the records that have the same user_id and also keep the records that dont(roughly 6k). I can find the matching records using;SELECT table1.*, table2.*FROM table1, table2WHERE table1.user_id = table2.user_idbut it doesn't keep the remaining records that dont have the same user_id. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kangaroo_m
Starting Member
9 Posts |
Posted - 2011-02-03 : 18:51:06
|
| table1user_id...|...age...|...gendre..101..........29..........M..103..........46..........Ftable2user_id...|...date_joined...|...name..101...........12/07/2008......Peter..102...........17/03/2009......John..103...........13/11/2010......MaryBasically I want to add table1 to table2 where the user_id matches but keep the records that dont match aswell. I hope this has made it clearer, I am not very good at explaining myself sometimes. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kangaroo_m
Starting Member
9 Posts |
Posted - 2011-02-04 : 10:02:13
|
| Worked perfectly, thank you.Can I use SQL to check if any of the fields data is entireley duplicate data to another field? e.g.table1user_id...|...age...|...gendre...|...user_name...|...name..|..sex..101..........29..........M..............Peter.............Peter......M..102..........35..........M..............Spikey............John.......M..103..........46..........F...............Mary..............Mary.......FSo I would like it to identify 'gendre' and 'sex' but not 'user_name' and 'name' |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2011-02-04 : 10:22:38
|
side question: why do you have an age column? won't it be out of date in a year? seems like it would be better to have a "birthdate" column and compute the age from that always, in a computed column if you really need an age column.
elsasoft.org |
|
|
|
kangaroo_m
Starting Member
9 Posts |
Posted - 2011-02-04 : 12:21:56
|
| Yes your right, I was just trying to simplify my table for this example, appologies for any confusion caused. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kangaroo_m
Starting Member
9 Posts |
Posted - 2011-02-04 : 16:30:03
|
| but I would like it to find entire columns that match not just individual records. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kangaroo_m
Starting Member
9 Posts |
Posted - 2011-02-05 : 17:08:50
|
| I will get back to my drwing board and do my best to figure it out myself. Thanks for the help on the outer join. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 18:07:02
|
quote:
I want to combine the records that have the same user_id and also keep the records that dont(roughly 6k).
What you want is a MERGE, for example. you would update records based on matching user_id, and insert those who don't. It can be made more complex and do more if needed, but the general idea below..MERGE INTO table1 as t1USING table2 as t2 on t1.user_id = t2.user_idWHEN MATCHED THENUPDATE t1.name = t2.name, t1.age = t2.age, t1.sex = t2.gendreWHEN NOT MATCHED THEN INSERT ( user_id, name, age, sex)Values (t2.user_id, t2.name, t2.age, t2.gendre)You can do a derived query in the USING clause if needed to produce a complete record set...*note: SQL 2K8 only.you would have to do it in two steps using UPDATE and INSERT for prior versions.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
Basic SQL help