Link to home
Start Free TrialLog in
Avatar of wasabi3689
wasabi3689Flag for United States of America

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-977BB28036E1.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-977BB28036E1.doc"

Now I want to have a result to have all data from table 1 with their paths from table 2
Avatar of wasabi3689
wasabi3689
Flag of United States of America image

ASKER

Here is my query, but no result to return. Something is missing, can you correct?

select  top 10 *
from HPMG_full_GenDocExtract_WV1
where document_ID in
(
SELECT LEft(RIGHT(RTRIM(NotFoundFile), 41), 37)
  FROM [NGConv_utility].[dbo].[FileNotFound2015_20161230_125322]
)

Open in new window

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%'

Open in new window

Hi Wasabi,
Please try below-

The problem with your code is SELECT LEft(RIGHT(RTRIM(NotFoundFile), 41), 37) It should be 36.

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]
)

--

Open in new window


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
--

Open in new window


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
--

Open in new window


Hope it helps!
both query return nothing. it's supposed to return something
Hi,
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?
Hi Author,
Any luck with this :) ?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial