locked
Join produces empty resultset RRS feed

  • Question

  • User-2006371901 posted

    WTF, all I'm trying to do is add the imageurl and description from plantimages to a first join that ties in the genus name with the plantnameid

    CREATE TABLE [dbo].[Genus](  [GenusID] [bigint] IDENTITY(1,1) NOT NULL,  [GenusName] [varchar](150) NOT NULL,  CONSTRAINT [PK_Genus] PRIMARY KEY CLUSTERED (

    CREATE TABLE [dbo].[PlantNames](  [PlantNameID] [bigint] NOT NULL,  [GenusID] [bigint] NULL,  [SpeciesID] [bigint] NULL,  [VarietyID] [bigint] NULL,  [CommonNameID] [bigint] NULL,  CONSTRAINT [PK_PlantName] PRIMARY KEY CLUSTERED (  

    CREATE TABLE [dbo].[PlantImage](  [PlantImageID] [bigint] IDENTITY(1,1) NOT NULL,  [Description] [varchar](250) NULL,  [PlantNameID] [int] NULL,  [ImageUrl] [varchar](250) NULL,    CONSTRAINT [PK_PlantImage] PRIMARY KEY CLUSTERED (

    select

      plantnames.plantnameid , plantnames.genusid, genus.genusname,

    plantimage.Description,plantimage.imageurl

    from plantnames 

    inner join plantimage on plantimage.plantnameid = plantnames.plantnameid

    inner join genus on   plantnames.genusid = genus.genusid

    where genus.genusname like '%acer%'

    ???
    thanks for assistance

    Norklehead

    Wednesday, May 4, 2016 3:35 AM

Answers

  • User77042963 posted

    Your where clause is not right with the wildcard search and it should look like below:

     where t.genusname like '%'+@s+'%';

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2016 2:42 PM

All replies

  • User-219423983 posted

    Hi norkle,

    WTF, all I'm trying to do is add the imageurl and description from plantimages to a first join that ties in the genus name with the plantnameid

    Could you clarify what do you mean the “first join”?

    If you want to first get the result from the combing [PlantNames] with [Genus] and then get the “imageurl” and “description” from [plantimage] based on the first result, I think your above query is right.

    About the empty result, I suggest you’d better first test

    Select * from [PlantNames], [Genus] on plantnames.genusid = genus.genusid where genus.genusname like '%acer%'

    to check whether it could return the expected result.

    Then you could export the above result to a temporary table [#Temp] and then left join it with [plantimage] as below to see whether it could return result.

    Select * from #temp p left join [plantimage] on plantimage.plantnameid = p.plantnameid

    After that, you could know where causes the error.

    Best Regards,

    Weibo Zhang

    Wednesday, May 4, 2016 6:09 AM
  • User-2006371901 posted

    OK, created store proc to test combining temp table into the scheme:

    ALTER

    PROCEDURE [dbo].[_spxgenusplimg]

       

    @s varchar(50)

    AS

    BEGIN

       

    SET NOCOUNT ON;

       

    CREATE TABLE #Temp

       

    (

           

    plantnameID integer NOT NULL,

    genusID integer NOT NULL,

           

    genusname nvarchar(50) NOT NULL

       

    );

       

    INSERT INTO #Temp

           

    (plantnameID,genusID, genusname)

       

    SELECT

           

    p.plantnameID,

    g.genusname,

    p.genusid

           

       

    FROM plantnames AS p

    ----

    join genus g on   p.genusid = g.genusid

    WHERE g.genusname

    LIKE '%@s%';

       

    SELECT

           

    t.genusname,t.plantnameID,t.genusname,i.description,i.nuurl

           

     

    FROM #Temp AS t

     

    /*JOIN Production.TransactionHistory AS th ON

            th.ProductID = t.ProductID*/

    join plantimage as i on t.plantnameid = i.plantnameid where t.genusname like '%@s%';

       

    DROP TABLE #Temp;

    END

    ;

    ...given the original DDL I posted for the tables, I still get empty resultset Surprised

    Wednesday, May 4, 2016 11:27 PM
  • User-219423983 posted

    Hi norkle,

    created store proc to test combining temp table into the scheme

    No, maybe you misunderstand my ideas.  What I suggested above is want you to test the statements in the database command window separately without any stored procedures.

    Besides, the where clause queries the '%@s%', you should make sure it there’re some items that contain '%@s%'.

    Besides, if the above SP could return the expected result when you execute it on database, you’d better share the corresponding fetching data DLL code and I could make a test on my client to help you solve the problem.

    Best Regards,

    Weibo Zhang

    Thursday, May 5, 2016 3:13 AM
  • User364663285 posted

    WTF, all I'm trying to do is add the imageurl and description from plantimages to a first join that ties in the genus name with the plantnameid

    CREATE TABLE [dbo].[Genus](  [GenusID] [bigint] IDENTITY(1,1) NOT NULL,  [GenusName] [varchar](150) NOT NULL,  CONSTRAINT [PK_Genus] PRIMARY KEY CLUSTERED (

    CREATE TABLE [dbo].[PlantNames](  [PlantNameID] [bigint] NOT NULL,  [GenusID] [bigint] NULL,  [SpeciesID] [bigint] NULL,  [VarietyID] [bigint] NULL,  [CommonNameID] [bigint] NULL,  CONSTRAINT [PK_PlantName] PRIMARY KEY CLUSTERED (  

    CREATE TABLE [dbo].[PlantImage](  [PlantImageID] [bigint] IDENTITY(1,1) NOT NULL,  [Description] [varchar](250) NULL,  [PlantNameID] [int] NULL,  [ImageUrl] [varchar](250) NULL,    CONSTRAINT [PK_PlantImage] PRIMARY KEY CLUSTERED (

    select

      plantnames.plantnameid , plantnames.genusid, genus.genusname,

    plantimage.Description,plantimage.imageurl

    from plantnames 

    inner join plantimage on plantimage.plantnameid = plantnames.plantnameid

    inner join genus on   plantnames.genusid = genus.genusid

    where genus.genusname like '%acer%'

    ???
    thanks for assistance

    Norklehead

    You need to adjust your query to ensure it does return records.

    Thursday, May 5, 2016 6:05 AM
  • User-2006371901 posted

    WEibo, @s is the search term a user queries the procedure with

    example : exec _spxgenusplimg 'spart'

    ...so ive used a temp table to separate the subsets, it should be bringing back the associated "description" and 'imgurl' along with genusname, plantnameid, etc, but returns no data, so I am wondering whats wrong with the join assignments

    Friday, May 6, 2016 4:04 AM
  • User77042963 posted

    Your where clause is not right with the wildcard search and it should look like below:

     where t.genusname like '%'+@s+'%';

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2016 2:42 PM
  • User-2006371901 posted

    Thanks for help - this is really weird; now I have set the input parameter as you suggested with the concatenation :

    like '%' + @s + '%'

    in both places that it comes up in the stored procedure, but now trying to make a search query with a term, I get:

    Conversion failed when converting the varchar value 'Spartium' to data type int.

    ..as for some reason the engine treats the input like an integer or return value???

    Nork

    Ps - these tiny mce or fck editors used in this forums sucks!!!!


    Tuesday, May 10, 2016 2:55 PM
  • User77042963 posted

    You need to show your table and query information with your updated query. You only show  the like part of your query is not enough to know what is wrong.

    Tuesday, May 10, 2016 3:01 PM
  • User-2006371901 posted

    Limo , its really the same as the original post; same DDL and the stored procedure is just altered slightly with the input parameter formatting
    HTH
    Nork

    Tuesday, May 10, 2016 3:24 PM
  • User77042963 posted

    No error for me

    CREATE TABLE [dbo].[Genus](  [GenusID] [bigint] IDENTITY(1,1) NOT NULL
    ,  [GenusName] [varchar](150) NOT NULL
    ,  CONSTRAINT [PK_Genus] PRIMARY KEY CLUSTERED ([GenusID]) )
     
    CREATE TABLE [dbo].[PlantNames](  [PlantNameID] [bigint] NOT NULL,  
    [GenusID] [bigint] NULL,  [SpeciesID] [bigint] NULL,  [VarietyID] [bigint] NULL,  
    [CommonNameID] [bigint] NULL,  CONSTRAINT [PK_PlantName] PRIMARY KEY CLUSTERED ([PlantNameID])  )
     
    CREATE TABLE [dbo].[PlantImage](  [PlantImageID] [bigint] IDENTITY(1,1) NOT NULL,  [Description] [varchar](250) NULL,  
    [PlantNameID] [int] NULL,  [ImageUrl] [varchar](250) NULL,    CONSTRAINT [PK_PlantImage] PRIMARY KEY CLUSTERED ([PlantImageID]))
    
     
    
    
    declare @s varchar(50)
    
    select  plantnames.plantnameid , plantnames.genusid, genus.genusname,
    plantimage.Description,plantimage.imageurl
    from plantnames 
    inner join plantimage on plantimage.plantnameid = plantnames.plantnameid
    inner join genus on   plantnames.genusid = genus.genusid
    where genus.genusname like '%'+@s +'%'
    


    :

    Tuesday, May 10, 2016 6:52 PM
  • User-2006371901 posted

    Thanks for help. I'll go over it again on my end
    Nork

    Wednesday, May 11, 2016 3:16 AM
  • User-219423983 posted

    Hi norkle,

    Conversion failed when converting the varchar value 'Spartium' to data type int.

    As limno said above, your initial code doesn’t show the related code for the “Spartium”, you’d better show you’re the code to clarify it.

    its really the same as the original post; same DDL and the stored procedure is just altered slightly with the input parameter formatting

    In my opinion, you could debug your code to get the value of “Spartium” and make sure whether it could be converted to int value.

    Best Regards,

    Albert Zhang

    Thursday, May 12, 2016 9:09 AM
  • User-2006371901 posted

    No, it Is suppose not to be converted to data type int, it should remain a varchar. So I need to figure out why it the data entered has all of a sudden want to become an integer when the input parameter @s has been initialized as a varchar.
    Nork

    Thursday, May 12, 2016 2:24 PM
  • User-219423983 posted

    Hi norkle,

    So I need to figure out why it the data entered has all of a sudden want to become an integer when the input parameter @s has been initialized as a varchar.

    At first, I’m sorry to say the “Conversion failed” error is a new issue in this thread, you’d better break it into a new thread with the related code (DDL code) for better support.

    As I still don’t know the code, you should try solving it by yourself following below links that’re about T-SQL debugging. Before this, you should also make sure whether this new error happens in the Stored procedure or not.

    https://msdn.microsoft.com/en-us/library/hh272701(v=vs.103).aspx

    https://www.mssqltips.com/sqlservertip/2410/sql-server-2012-tsql-debugging-enhancements/

    Best Regards,

    Weibo Zhang

    Friday, May 13, 2016 2:32 AM