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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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_Messa ge = '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
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_
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.field 1 AND table1.field2=table2.field 2 AND table1.field3=table2.field 3 AND table1.field4=table2.field 4;
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.field 1 AND table1.field2=table2.field 2 AND table1.field3=table2.field 3 AND table1.field4=table2.field 4;
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.field 1 AND table1.field2=table2.field 2 AND table1.field3=table2.field 3 AND table1.field4=table2.field 4;
1- This one selects all the field from the 2 tables
SELECT *
FROM table1, table2
WHERE table1.field1=table2.field
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.field
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.field
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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'"
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)
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
left join cc_history as s
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
same thing
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
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.
[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.
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?
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.
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.
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?
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?
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
(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
AW
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