Solved

Joining SQL2k tables

Posted on 2003-11-26
22
323 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:LosBear
  • 10
  • 5
  • 4
  • +2
22 Comments
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 100 total points
Comment Utility
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
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 3

Expert Comment

by:yasser_helmy
Comment Utility
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 100 total points
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 9

Expert Comment

by:malharone
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:michaelhehl
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 1

Expert Comment

by:michaelhehl
Comment Utility
try

left join cc_history as s
0
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 1

Expert Comment

by:michaelhehl
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 1

Expert Comment

by:michaelhehl
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 1

Author Comment

by:LosBear
Comment Utility
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
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
ok by me

AW
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now