I Have 2 Tables & in that there is no two fields i can link each other but i want records from these two tables where say 3 letters of field 1 of table A matches with 3 letters of field 1 of table B
Printable View
I Have 2 Tables & in that there is no two fields i can link each other but i want records from these two tables where say 3 letters of field 1 of table A matches with 3 letters of field 1 of table B
Hi,
Use a query like
SELECT
table1.field1, table2.field2
FROM
table1 CROSS JOIN table2
WHERE
LEFT(field1, 3) = LEFT(field2, 3) and LEN(field1) > 0
I put LEN(...) to the where clause to avoid records with empty values for those fields.
Be aware that you can get double records because you do not want to link the tables via a key value.
Regards,
Maurice
Quote:
Originally posted by Maurice Sanders
Hi,
Use a query like
SELECT
table1.field1, table2.field2
FROM
table1 CROSS JOIN table2
WHERE
LEFT(field1, 3) = LEFT(field2, 3) and LEN(field1) > 0
I put LEN(...) to the where clause to avoid records with empty values for those fields.
Be aware that you can get double records because you do not want to link the tables via a key value.
Regards,
Maurice
Hi Maurice,
Thanx for the help extended by you,will try out this & if something
else comes across then will get back to you.
Just have a doubt that shall i put this query in show SQl Query in crystal Report and b/w the 2 tables do i have to put any join .
Regards
Rupali
Quote:
Originally posted by RupaliSood
Hi Maurice,
Thanx for the help extended by you,will try out this & if something
else comes across then will get back to you.
Just have a doubt that shall i put this query in show SQl Query in crystal Report and b/w the 2 tables do i have to put any join .
Regards
Rupali
Hi Maurice,
I'm back
on running this query it is giving the error that SQL command not properly ended and moreover my tables 2 contains 1000's of records so that is also not feasible to use Cross join,can u suggest something else
Regards
Rupali
I'm not Maurice but I'm happy to try and help if I can. :)
If at all possible I would strongly recommend putting a join like this into a query that runs on the server and then use the query as the basis of your crystal report. The reason being that if Crystal attempts a join like that it will have to drag down all records from both tables which will get very ugly.
Also, I think Crystal uses Length and not Len and I'm not 100% sure that it accepts CROSS JOIN but I guess that would depend on which database driver you use. What database driver are you using?
If you are unable to set up a query on the server I think the following might work:
SELECT
table1.field1, table2.field2
FROM
table1, table2
WHERE
LEFT(table1.field1, 3) = LEFT(table2.field2, 3) and LENGTH(table1.field1) > 0
But I think creating a query will give you a much better result.
Hope this helps,
Nathan.
Quote:
Originally posted by Nathan Liebke
I'm not Maurice but I'm happy to try and help if I can. :)
If at all possible I would strongly recommend putting a join like this into a query that runs on the server and then use the query as the basis of your crystal report. The reason being that if Crystal attempts a join like that it will have to drag down all records from both tables which will get very ugly.
Also, I think Crystal uses Length and not Len and I'm not 100% sure that it accepts CROSS JOIN but I guess that would depend on which database driver you use. What database driver are you using?
If you are unable to set up a query on the server I think the following might work:
SELECT
table1.field1, table2.field2
FROM
table1, table2
WHERE
LEFT(table1.field1, 3) = LEFT(table2.field2, 3) and LENGTH(table1.field1) > 0
But I think creating a query will give you a much better result.
Hope this helps,
Nathan.
Hi Nathan,
My database diver is Oracle and if i'm trying to run the same query in orcale then also its no accepting.
Creating a query u mean to create a view or something else
Thanx a lots
rupali
OK. Oracle I can help you with. The query if you were running it on Oracle would be:
SELECT t1.field1, t2.field2
FROM table1 t1, table2 t2
WHERE SUBSTR(NVL(t1.field1, '?><M'), 1, 3) = SUBSTR(NVL(t2.field2, '?><M'), 1, 3)
The NVL function basically operates by saying if the value is null, use the other value.
eg NVL(t1.field1, '?><M') will return ?><M if t1.field1 is null. Otherwise it will return t1.field1.
This is the best way to account for null values in these fields.
And yes, I did mean a View. I wasn't sure whether you were running on Oracle or Access or SQL Server. So many different words for the same thing. ;)
I would definitely create an Oracle view and use that as the source for your Report.
Hope this helps,
Nathan.
Hi
Than x A Lot ,i'll try out this
Quote:
Originally posted by Nathan Liebke
OK. Oracle I can help you with. The query if you were running it on Oracle would be:
SELECT t1.field1, t2.field2
FROM table1 t1, table2 t2
WHERE SUBSTR(NVL(t1.field1, '?><M'), 1, 3) = SUBSTR(NVL(t2.field2, '?><M'), 1, 3)
The NVL function basically operates by saying if the value is null, use the other value.
eg NVL(t1.field1, '?><M') will return ?><M if t1.field1 is null. Otherwise it will return t1.field1.
This is the best way to account for null values in these fields.
And yes, I did mean a View. I wasn't sure whether you were running on Oracle or Access or SQL Server. So many different words for the same thing. ;)
I would definitely create an Oracle view and use that as the source for your Report.
Hope this helps,
Nathan.
[QUOTE]Originally posted by RupaliSood
Hi
Than x A Lot ,i'll try out this
Ya i tried this query now its not giving any syntax error but not returning any record though the data is correct
Are you saying that based on the data you have that this query should return something?
It's worth noting that the String match is case sensitive.
eg 'Hou' <> 'HOU'
The following query is case insensitive.
SELECT t1.field1, t2.field2
FROM table1 t1, table2 t2
WHERE UPPER(SUBSTR(NVL(t1.field1, '?><M'), 1, 3)) = UPPER(SUBSTR(NVL(t2.field2, '?><M'), 1, 3))
Hope this helps,
Nathan.
Ya It worked now,such a silly thing didn't strike me .Quote:
Originally posted by Nathan Liebke
Are you saying that based on the data you have that this query should return something?
It's worth noting that the String match is case sensitive.
eg 'Hou' <> 'HOU'
The following query is case insensitive.
SELECT t1.field1, t2.field2
FROM table1 t1, table2 t2
WHERE UPPER(SUBSTR(NVL(t1.field1, '?><M'), 1, 3)) = UPPER(SUBSTR(NVL(t2.field2, '?><M'), 1, 3))
Hope this helps,
Nathan.
Thanx a Ton