wasabi3689
asked on
sql query
I have two tables. One table (table1) has only one column with the data format like below
File not found - 28DB4BBE-6E38-4D07-B30A-97 7BB28036E1 .doc
I have another main table (table2) with two fields: one field with path and another field with the columns named "Document_ID" including the document name i.e "28DB4BBE-6E38-4D07-B30A-9 77BB28036E 1.doc"
Now I want to have a result to have all data from table 1 with their paths from table 2
File not found - 28DB4BBE-6E38-4D07-B30A-97
I have another main table (table2) with two fields: one field with path and another field with the columns named "Document_ID" including the document name i.e "28DB4BBE-6E38-4D07-B30A-9
Now I want to have a result to have all data from table 1 with their paths from table 2
ASKER
If I write this, it has a return
select top 10 *
from HPMG_full_GenDocExtract_WV1
where document_ID like '%28DB4BBE-6E38-4D07-B30A-977BB28036E1%'
Hi Wasabi,
Please try below-
The problem with your code is SELECT LEft(RIGHT(RTRIM(NotFoundF ile), 41), 37) It should be 36.
Well for your query you can use like..
My options would be below since IN is slow in nature..
OPTION 1
OPTION 2
Hope it helps!
Please try below-
The problem with your code is SELECT LEft(RIGHT(RTRIM(NotFoundF
Well for your query you can use like..
--
select top 10 *
from HPMG_full_GenDocExtract_WV1
where document_ID in
(
SELECT LEft(RIGHT(RTRIM(NotFoundFile), 41), 36)
FROM [NGConv_utility].[dbo].[FileNotFound2015_20161230_125322]
)
--
My options would be below since IN is slow in nature..
OPTION 1
--
SELECT *
from HPMG_full_GenDocExtract_WV1 a
INNER JOIN
(
SELECT LEFT(RIGHT(RTRIM(NotFoundFile), 41), 36) NotFoundFile
FROM [NGConv_utility].[dbo].[FileNotFound2015_20161230_125322]
)k ON a.document_ID = k.NotFoundFile
--
OPTION 2
--
SELECT *
from HPMG_full_GenDocExtract_WV1 a
INNER JOIN
(
SELECT SUBSTRING ( NotFoundFile , 0, CHARINDEX('.',NotFoundFile,0)) NotFoundFile
FROM [NGConv_utility].[dbo].[FileNotFound2015_20161230_125322]
)k ON a.document_ID = k.NotFoundFile
--
Hope it helps!
ASKER
both query return nothing. it's supposed to return something
Hi,
Can you please provide few rows from both the tables?
Can you please provide few rows from both the tables?
)k ON a.document_ID = k.NotFoundFile
Do the hi-lighted columns above have the same data type?
Do the hi-lighted columns above have the same data type?
Hi Author,
Any luck with this :) ?
Any luck with this :) ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Open in new window