August 28, 2007 at 6:27 am
Pardon my ignorance and not being that savvy with writing queries, but I have ran into a problem, where I'd need you all's expertise.
I need to write a query where the output would provide me results based on two POSSIBLE columns from two different tables. Here is an example.
I have tables called Table1 and Table2. Both of these tables hold two columns that could possibly be used as join ids. For example in Table1 I have a column called PrimaryMatchID. The PrimaryMatchID in table1 could have integers, 0's or NULLs, then there is SecondaryMatchID which holds Long Ints only, no nulls, nor 0's. In Table2 I also have the same exact two columns, however if one of them holds a value, the other one is set to 0. For example, if one row of Table2's PrimaryMatchID holds an id, then SecondaryMatchID in Table2 holds a 0 and vice versa. So Table two would looks something like this:
Table2
PrimaryMatchID | SecondaryMatchID
23234234 | 0
0 | 433
0 | 567
4432424 | 0
So I hope this makes it clear. I know the design is bad and this is something I have inhereted. However, I do have the flexibility to redesign the Table2. If not then, how can I go about writing the joins to get the viable results. Any help would truly appreciated. Thank you.
August 28, 2007 at 7:12 am
It would have been more helpful if you had provided example data for both tables and the output you were looking for (ie. the result set). But based on my interpretation of what you described let me know if this works:
if object_id('table1') is not null drop table table1
go
create table table1 (PMI int, SMI int not null default 0)
go
if object_id('table2') is not null drop table table2
go
create table table2(PMI int not null default 0, SMI int not null default 0,
constraint pk_table2 primary key (PMI, SMI))
go
insert into table1 (PMI,SMI)
select NULL, 23234234 union
select 433,0 union
select 0,567 union
select 0,4432424
go
insert into table2 (PMI,SMI)
select 23234234, 0 union
select 0,433 union
select 0,567 union
select 4432424,0
go
select t1.*, t2.*
from table1 t1 join table2 t2 on ((t1.pmi = t2.pmi and t1.pmi <> 0) or
(t1.smi = t2.smi and t1.smi <> 0) or
(t1.pmi = t2.smi and t1.pmi <> 0) or
(t1.smi = t2.pmi and t1.smi <> 0))
--James.
August 28, 2007 at 7:39 am
Another alternative for the ON clause of the previous example
Coalesce(NullIf(t1.pmi,0),NullIf(t1.smi,0),-1) = Coalesce(NullIf(t2.pmi,0),NullIf(t2.smi,0),-2)
August 28, 2007 at 7:56 am
Excellent! I like the eloquence of your join statement. I'm curious if it is also more efficient or do the functions take a toll? I've never put one together that way, I'll keep it in mind if I run into something similar in the future.
Though I think my simple join statement might be easier for a novice to wrap his head around.
James.
August 28, 2007 at 8:04 am
Thank you for the suggestions folks. I will give these a try.
August 28, 2007 at 8:35 am
Thank you. Don't think the functions take that much of a toll on what we have don in the past but weren't very large datasets either. Just posted as an option but I was concerned yours doesn't tae the condition of NULL into account on what to do and probably need a NOT IS NULL added for each just in case the ansi setting for null evaluation is different from the assumed.
August 28, 2007 at 8:38 am
Ok - folks. I tried but it didnt give me the results I was looking for. Let me give you better and detailed example.
Table1
SecondaryID PrimaryID
23432423 | 234
32432432 | NULL
12123321 | 0
42134242 | 3456
Table2
SecondaryID PrimaryID
0 | 234
32432432 | 0
12123321 | 0
0 | 3456
The resultset should match exactly what the recordset looks like in Table2. Sorry about the confusion. I've been little burnt out and have been going at it since last night with little to no sleep. Yet again, I appreciate the help.
August 28, 2007 at 8:51 am
if the result needs to look exactly like table 2 then simply select everything from table 2. No need for a join at all. Please post an "EXACT" list of what the result set should look like if the data you provided above is used. For example:
t1.PMI t1.SMI t2.PMI t2.SMI
----------- ----------- ----------- -----------
12123321 0 12123321 0
23234234 234 0 234
32432432 NULL 32432432 0
42134242 3456 0 3456
From the example result set above I can tell what it is you want to match on and what you are looking to get from the test data. With that most folks can assist you with writing a SQL statement to accomplish it.
James.
August 28, 2007 at 8:53 am
OK didn't realize Table1 could have a value in both Pmi and SMI that is non-null and non-zero so try this
declare @table1 table (PMI int, SMI int not null default 0)
declare @table2 table(PMI int not null default 0, SMI int not null default 0)
insert into @table1 (PMI,SMI)
select 234, 23432423 union
select NULL,32432432 union
select 0,12123321 union
select 3456,42134242 union
select 37,24
insert into @table2 (PMI,SMI)
select 234,0 union
select 0,32432432 union
select 0,12123321 union
select 3456,0 union
select 24,0
SELECT
t2.*
FROM
@table1 t1
JOIN
@table2 t2
ON
Coalesce(NullIf(t1.pmi,0),NullIf(t1.smi,0),-1) = Coalesce(NullIf(t2.pmi,0),NullIf(t2.smi,0),-2) OR
Coalesce(NullIf(t1.smi,0),NullIf(t1.pmi,0),-1) = Coalesce(NullIf(t2.pmi,0),NullIf(t2.smi,0),-2)
You will note I just put out the values from Table2 which based on your statement you are looking for the rows in table2 that match in any way with table1.
But what if there was also a record in Table 2 of
SMI | PMI
37 | 0
would I have a match still consider I put in table 1
SMI | PMI
24 | 37
And table 2 already has a match on
SMI | PMI
0 | 24
August 28, 2007 at 9:40 am
Thank you very much Antares! That's exactly what I was looking for. Although the performance is effected by this query, I will deal with this in time. Thanks again everyone for taking your time to help me solve this.
August 28, 2007 at 10:44 am
Keep it simple!
SELECT
t2.*
FROM @table1 AS t1
INNER JOIN @table2 AS t2 ON t2.PMI + t2.SMI IN (t1.PMI, t1.SMI)
N 56°04'39.16"
E 12°55'05.25"
August 28, 2007 at 10:52 am
What if there are ID's with values of -1 or -2?
OP hasn't told us.
N 56°04'39.16"
E 12°55'05.25"
August 28, 2007 at 11:33 am
And you are right, as a developer I would still say send more details and hash these things out, but posting here I do tend to work under some assumptions such as most people implement ints 0 or positive (especially indentity columns which I have started at -(smallest negative can get) before.
August 28, 2007 at 11:35 am
Good point, got on one path and forgot to rethink when I had new information given to me.
August 28, 2007 at 11:42 am
The ID's are values of 0's, NULLs or greater than 0. Nonetheless it's a good point.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply