|
View previous topic :: View next topic
|
| Author |
Message |
hariibm
New User
Joined: 05 Feb 2007
Posts: 61
Location: Chennai
|
|
|
|
Hi,
I need a SQL query for the below requirement.
I have two tables to join lets say Table1, Table2.
Table1 rows must be there in the result so I am using the LEFT OUTER JOIN between these two.
My requirement is:
| Code: |
Table1 Table2
---------- ---------
field1 field2 field1 field2 field3
code1 A code1 * ***
code2 B code1 P ***
code1 P C
|
Outer join is based on the field1 of Table1 and field1 of Table2.
the result after Outer join is like below:
| Code: |
code1 A code1 * ***
code1 A code1 P ***
code1 A code1 P C
code2 B null null null |
this is for finding the nearest match. so in the above result set for the (code1, A), I need to have only (code1 P C) in the result set.
ie, like below.
| Code: |
code1 A code1 P C
code2 B null null null |
ordering the result by field1, field2(of table1), then by field1 in ascending order and then field2, field3 by descending order
will bring the required result at the top for each group(like below). But I am not sure how to eliminate the below two rows.
So, I need the 1st and 4th row from the below result set. Please help me with the query.
| Code: |
code1 A code1 P C
code1 A code1 P *** (these two rows
code1 A code1 * *** (should be eliminated from the result set.)
code2 B null null null
|
Thanks,
Hari |
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
Please start using code tags for the input data
Do you want to select only row from table B or all rows for code1 which will satisfy your criteria?
Also is there any specific criteria that you dont want to slect rows from table B?
From Inputs you have provided it looks like you dont want to select rows wheer its returning Table 2 fields having value as '*'
Regards,
Chandan |
|
| Back to top |
|
|
hariibm
New User
Joined: 05 Feb 2007
Posts: 61
Location: Chennai
|
|
|
|
Hi Chandan,
Please start using code tags for the input data
Thanks for putting the things right. I was not aware.
Do you want to select only row from table B or all rows for code1 which will satisfy your criteria?
Also is there any specific criteria that you dont want to slect rows from table B?
From Inputs you have provided it looks like you dont want to select rows wheer its returning Table 2 fields having value as '*'
I need to select all rows from Table1 with the nearest match in Table2 for every rows of Table1.
As per the example row from Table2 removed from result set because there is a row with exact match found in Table2.(with exact match for P and C).
yes I forgot to mention, apart from the join criteria, the P, C(or different values) will be input to this query. So the row with exact match from Table2 should be taken. If not then the row with partial match(like P, ***) should be taken and if none of the matches found, then the ultimate matching row is the row with *, ***).
I hope it can be understood. Let me know if there is any confusion.
Regards,
Hari |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Hariibm,
Can you please define the term exact matching ?
You second explanation is like "ignotum per ignotius"
If I understand your requirement correctly
Is it something like you always try to field 2 value when it is "P" and the key of the record in Table2 matching with key of the record in TABLE1
and then if present you wanted to select the record from table2 having field 3 value of c
If the above record is not present
You wanted a record from table2 having field2 as P and field3 as any value ( Ofcourse it cant be C)
And even if the both the above are not present
You wanted a record from table2 having field2 as any value (ofcourse cant be P) and field3 as any value ( it cant be C and other values)
and apart from this the unmatched records from Table1
Please confirm |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select * from
table1 A
left join table
(select * from table2 B where b.field1 = a.field1
order by b.field2 desc, b.field3 desc
fetch first 1 row only) B2 on 1=1 |
|
|
| Back to top |
|
|
|
|
