Link to home
Start Free TrialLog in
Avatar of LosBear
LosBear

asked on

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
SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of LosBear
LosBear

ASKER

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
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
Avatar of LosBear

ASKER

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
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;
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LosBear

ASKER

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
Avatar of LosBear

ASKER

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

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
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'"



Avatar of LosBear

ASKER

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)
try

left join cc_history as s
Avatar of LosBear

ASKER

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
Avatar of LosBear

ASKER

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
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.
Avatar of LosBear

ASKER

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?

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.
Avatar of LosBear

ASKER

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?
Avatar of LosBear

ASKER

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
ok by me

AW