Joining SQL2k tables

Ok, easy question.  I have a main table with say 10 fields.  I have another table with the same field names (with the exception of a primary key field intelligently named "ID")

I have a lot of data in both, unfortunately, some of this data are dups

I need to sql statements:
1. joins both tables using field1, field2, field 3, field4, etc.
2. joins both but not duplicates

The first one is more important to me, but I could really use the second one too.
thanks in advance!
Bear
LVL 1
LosBearAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
are you saying the Table1 does NOT have a PrimaryKey Field?

so does Table1 have 10 fields, and Table2 has the SAME texct fields, in the same order, AND 1 more field (total of 11) named 'ID'?

AW
0
Arthur_WoodCommented:
should have read:

so does Table1 have 10 fields, and Table2 has the SAME 10 fields, in the same order, AND 1 more field (total of 11) named 'ID'?

AW
0
LosBearAuthor Commented:
fields for table 1 (Primary table)
ID, Field1, Field2, Field3, Field4, etc...

fields for table 2 (a backup table / archives)
Archive_ID, ID, Field1, Field2, Field3, Field4, etc...

In table 2, Archive_ID is a primary, and ID is just a numeric field that holds the Primary Field (ID) from table 1

bear
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
select -1 as Archive_ID, ID, Field1, Field2... from Table1
UNION
Select Archive_ID, ID, Fields1, Field2... From Table2

that will get you a result set that has ALL of the records, from both tables, with the Table1 records having the Archive_ID field with a Value of -1.

But is the Table2 records hold the Primary key of the corresponding Table1 reocrd, what exactly are you trying to accomplish. what do you mean when you say 'some of this data are dups'???

why can't you use:

Select Table1.*, Table2.* from Table2 JOIN Table1 on Table2.Id = Table1.ID

or is that NOT what you are after?

AW
0
LosBearAuthor Commented:
Hey Arthur-
sorry, here's my original sql statement; didn't mean to sound like I was hiding it or anything

'This is the primary table
Me.DataControl1.Source = "select * from cc_transactions where [Response_Message] = 'Approved' AND [Today_Date] >= '" & frmCCSelReport2.zFrom & " 12:00:00 AM' AND [Today_Date] <= '" & frmCCSelReport2.zTo & " 11:59:59 PM'"

'Now, I want to get a similar recordset from this table:
Me.DataControl1.Source = "select * from cc_transactions where [Response_Message] = 'Approved' AND [Today_Date] >= '" & frmCCSelReport2.zFrom & " 12:00:00 AM' AND [Today_Date] <= '" & frmCCSelReport2.zTo & " 11:59:59 PM'"

As you can see, they both have the same fieldnames.  I only mention that cc_history has an extra field that's the primary field called "history_id" because I thought it might be useful in the sql statement when unique-ing the duplicates records.

You see, SOMEHOW some duplicate records got into the history, and I don't want to show them as part of the recordset.

I tried this sql statement, but got no records back :-(

Me.DataControl1.Source = "select * from cc_transactions, cc_history where (cc_transactions.Response_Message = 'Approved' AND cc_transactions.Today_Date >= '" & frmCCSelReport2.zFrom & " 12:00:00 AM' AND cc_transactions.Today_Date <= '" & frmCCSelReport2.zTo & " 11:59:59 PM') OR (cc_history.Response_Message = 'Approved' AND cc_history.Today_Date >= '" & frmCCSelReport2.zFrom & " 12:00:00 AM' AND cc_history.Today_Date <= '" & frmCCSelReport2.zTo & " 11:59:59 PM')"

thanks for your time.
bear
0
yasser_helmyCommented:
I think this is what you want:
1- This one selects all the field from the 2 tables
SELECT *
FROM table1, table2
WHERE table1.field1=table2.field1 AND table1.field2=table2.field2 AND table1.field3=table2.field3 AND table1.field4=table2.field4;

you can also select some fields (not all) from the 2 tables as:
SELECT table1.ID, table2.archive_id, table1.field1, table1.field9, table2.field7 (enter the fields you want)
FROM table1, table2
WHERE table1.field1=table2.field1 AND table1.field2=table2.field2 AND table1.field3=table2.field3 AND table1.field4=table2.field4;

2- This is basically the same as the previous, but eliminate duplicate results
SELECT DISTINCT table1.field1, table1.field9, table2.field7 (enter the fields you want)
FROM table1, table2
WHERE table1.field1=table2.field1 AND table1.field2=table2.field2 AND table1.field3=table2.field3 AND table1.field4=table2.field4;
0
Arthur_WoodCommented:
what do you mean by "duplicate" records.  In each record, the values in the fields are the same in each record?

If that is the case, then you can use "Select Distinct...." to only get the DISTINCT records.  This does mean, however, that you will need to EXPLICITLY list the fields, rather than using *, becuase the ID, if it is in fact a Primary Key, will make EACH record effectively DISTINCT, even if the other fields in the records all are the same, record to record.

AW
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LosBearAuthor Commented:
yasser
check out my last post, I need the two tables to meet the same criteria
IE:  [date] > now and [date] < now
I foresaw this and used the same names in both tables :)

thx
0
LosBearAuthor Commented:
Art,
Here's an example

TABLE 1
Field1          Field2                  Field3
1234           11/20/2003          23
4687          11/15/2003           235
234            11/19/2003          975

TABLE 2
Field1          Field2                    Field3
8977          10/25/2004             568
234           11/19/2003             975
734           11/20/2003             546

I would need it to return this:
Field1          Field2                  Field3
1234           11/20/2003          23
4687          11/15/2003           235
234            11/19/2003          975
8977          10/25/2004             568
734           11/20/2003             546


bear

0
malharoneCommented:
select field1, field2, field3 from table1 union select field1, field2, field3 from table2
this will return the above table. in union queries, the "distinct" is the default option.
so the above is same as
select distinct field1, field2, field3 from table1 union  select field1, field2, field3 from table2
and will return
field1      field2      field3
1234      11/20/2003      23
234      11/19/2003      975
4687      11/15/2003      235
734      11/20/2003      546
8977      10/25/2004      568



but if you want ALL rows (all 6 of them), then you explicitely need to define "ALL"
e.g.
select field1, field2, field3 from table1 union  all select field1, field2, field3 from table2
returns:
field1      field2      field3
1234      11/20/2003      23
4687      11/15/2003      235
234      11/19/2003      975
8977      10/25/2004      568
234      11/19/2003      975
734      11/20/2003      546
0
michaelhehlCommented:
Me.DataControl1.Source = "select * from cc_transactions1 p join cc_transactions2 s on p.Response_Message = s.Response_Message where [Response_Message] = 'Approved' AND [Today_Date] >= '" & frmCCSelReport2.zFrom & " 12:00:00 AM' AND [Today_Date] <= '" & frmCCSelReport2.zTo & " 11:59:59 PM'"



0
LosBearAuthor Commented:
GETTING WARMER!

i had to tweak it a tad:
select * from cc_transactions as p join cc_history as s on p.Response_Message = s.Response_Message where [Response_Message] = 'Approved' AND [Today_Date] >= '11/1/2003 12:00:00 AM' AND [Today_Date] <= '12/1/2003 11:59:59 PM'

it pulls up three records (which is correct), BUT the recordset contains these 3 looped 3,859 times (for a total of 11577 records as a result)
0
michaelhehlCommented:
try

left join cc_history as s
0
LosBearAuthor Commented:
select * from cc_transactions as p left join cc_history as s on p.Response_Message = s.Response_Message where [Response_Message] = 'Approved' AND [Today_Date] >= '11/1/2003 12:00:00 AM' AND [Today_Date] <= '12/1/2003 11:59:59 PM'

same thing
0
LosBearAuthor Commented:
I've tried several other things, and no luck.  All i wanted to do was join two tables that met the same criteria instead of throwing it all into an array and doing it in code.

didn't know sql could be this difficult.
so far michael has gotten me the closest.  If anyone has a solutions, say today or tonight points go to him.

thx 4 all ur effort tho
bear
0
michaelhehlCommented:
try

[p.Response_Message] = 'Approved' and [p.Today_Date] = '11/1/2003 12:00:00 AM'
and [s.Response_Message = 'Approved' and [p.Today_Date] = '11/1/2003 12:00:00 AM'

Use one or the other or both. Play around with it a little and let me know how it works.
0
LosBearAuthor Commented:
here's the closest thing I came up with

Here is a record count for the dates of 10/1/2003 and 12/1/2003:
cc_transactions: 328
cc_history: 43

so combined they SHOULD give me a combo of 371 records plus or minus some because of doubles.


If i switch your ON section to s.Counter = p.Counter (which is a primary field in cc_transactions) then it gives me 328 records (cc_transactions count).  If i leave as is, then I kept getting 0 records with the 12+ combinations I tried.

maybe i missed something?

0
michaelhehlCommented:
Sounds like we are getting close. How many duplicates do you suppose you have. It looks like the query is not returning anything out of your history table.

Try flipping the statement.

select * from cc_history h join cc_transactions t on etc. etc.

Oh, the h that follows cc_history is an identifier for the table. It probably shouldn't be as h. This allows us to identify what fields are pulling from what tables in the future. I.E. [h.count] or
t.count.

We may also want to add a group by statement at the end.
group by h.count.

Let me know.
0
LosBearAuthor Commented:
yep yep, i know about the identifier, although I admit, I thought it was used as cc_history AS h, but your way seems to work too :)

If I flip the cc_history and the cc_transactions, guess what happens :)????
i get 43,

does sound like we're getting closer, the criteria is definately working. sounds like the issue is in the combining of the two together...

group by's are cool, our previous programmer used them EVERYWHERE, but i can't cypher out the usage.  would it help here?
0
LosBearAuthor Commented:
The solution I was looking for was this:
(SELECT * FROM table1 WHERE field = value) UNION (SELECT * FROM table2 WHERE field = value)

this merges both tables and elliminates duplicates.

I'll split points amongs michael and arthur for their time
0
Arthur_WoodCommented:
ok by me

AW
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.