Syntax for similar Pattern between the tables [message #605712] |
Mon, 13 January 2014 15:31 |
|
imransi17@gmail.com
Messages: 4 Registered: November 2013
|
Junior Member |
|
|
Hello Friends,
I am trying to search the following two elements between the two tables. You can also view the attached jpg file for the result set.
1. where Postal Code matches (i am using = sign and it works fine)
2. and street_1 matches street_2
I am having issue with item 2 above. In addition to Postal Code, I only need to retrieve records that matches STREET_1 and STREET_2. So, in this scenario, I am getting many records from street_2 where 'TORONTO' matches between the two columns. I should only be able to see one record where row number 4 'TORONTO' matches between STREET_1 and STREET_2
Also, I don't want to use Like or exists operators. I am searching millions of records, so I don't know what would match to what.
Could you please help me with the syntax or function or pattern query etc?
thank you.
--- query ---
select a.postalcode, a.street_1, b.street_2
from Table_1 a, Table_2 b
where a.postalcode = b.postalcode
---- Result Set ----
POSTALCODE STREET_1 STREET_2
Q2M 4V7 QAUAN 27 TORONTO BLVD
Q2M 4V7 ANGLE 27 TORONTO BLVD
Q2M 4V7 LILLY 27 TORONTO BLVD
Q2M 4V7 TORONTO 27 TORONTO BLVD
Q2M 4V7 FRIEND 27 TORONTO BLVD
Q2M 4V7 DUFFY 27 TORONTO BLVD
Q2M 4V7 TICKLE 27 TORONTO BLVD
Q2M 4V7 MAINGLE 27 TORONTO BLVD
Q2M 4V7 KEATIN 27 TORONTO BLVD
Q2M 4V7 BITEE 27 TORONTO BLVD
Q2M 4V7 TAKIAQ 27 TORONTO BLVD
Q2M 4V7 BLUE 27 TORONTO BLVD
Q2M 4V7 VILLAGE 27 TORONTO BLVD
Q2M 4V7 WHITECAP 27 TORONTO BLVD
Q2M 4V7 OCEANIC 27 TORONTO BLVD
Q2M 4V7 LOGAN 27 TORONTO BLVD
Q2M 4V7 KARA 27 TORONTO BLVD
Q2M 4V7 SALTEEN 27 TORONTO BLVD
Q2M 4V7 BINGO 27 TORONTO BLVD
Q2M 4V7 PICASA 27 TORONTO BLVD
Q2M 4V7 WALTER PO BOX 217
Thanks.
imransi.
[EDITED by LF: applied [code] tags]
[Updated on: Mon, 13 January 2014 15:51] by Moderator Report message to a moderator
|
|
|
Re: Syntax for similar Pattern between the tables [message #605715 is a reply to message #605712] |
Mon, 13 January 2014 15:46 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Not really sure what you mean. Also, with a non-formatted post, I don't know what is in which column, so unless you have missing column headers, 27 and BLVD are part either street 1 or street 2, hence, they do not match.
Do you want "similar" records or exact matches?
|
|
|
|