locked
How to identify a secondary object to a main object RRS feed

  • Question

  • User-1518883836 posted

    I have a simple data repeater that will display plant pictures with short description. The plantname is the name of the plant duhh, and the nameid also is in another table plantnames to identify it in two areas of the dbase. But the table dbo.plants I am using just to display thumbnails that are referred to as a URL. In some cases the same plantname will have more than one image (two show different aspects for certain plants). I will enter the plantname twice for this dbo.plants table, use the same nameid , but if it is the main picture for the plantname, I will tag it with dbo.isDefault = 1 (Bit/Boolean datatype).

    DDL dbo.plants =

    CREATE TABLE [dbo].[plants] (

        [pid]       INT          IDENTITY (1, 1) NOT NULL,

        [nameid]     int not null,

        [plantname]     VARCHAR (50)   NOT NULL,

        [isDefault]   bit,

    [imageURL] varchar (70)

        [Description] VARCHAR (500)  NULL,

        PRIMARY KEY CLUSTERED ([pid] ASC)

    );

    SELECT p.plantname,p.Description,p.nameid,p.imageUrl,p.isDefault,

    (select imageurl as secondary from plants where plants.isdefault <> 1) from plants p 

    doesn't work because "subquery returns more than 1 value ..not permitted when subquery follows < = > etc."

    Will I have to build a special user function for this? Thanks for any assistance for this!

    RolfM

    Tuesday, December 1, 2015 5:57 AM

Answers

  • User-808054615 posted

    Try:

    SELECT 
        p.plantname,
        p.Description,
        p.nameid,
        p.imageUrl,
        p.isDefault,
        (select top(1) s.imageurl 
         from plants s 
         where s.nameid = p.nameid and s.isdefault <> 1) as secondary 
    from plants p  
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 1, 2015 11:19 AM
  • User-1518883836 posted

    Awesome, yeah this is the idea , I know with the way I have my assignments set up in my asp.net that I'll have to work on this with some tweaks, but yes needed that subquery to kick in
    Muench

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 2, 2015 12:47 AM

All replies

  • User-808054615 posted

    Try:

    SELECT 
        p.plantname,
        p.Description,
        p.nameid,
        p.imageUrl,
        p.isDefault,
        (select top(1) s.imageurl 
         from plants s 
         where s.nameid = p.nameid and s.isdefault <> 1) as secondary 
    from plants p  
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 1, 2015 11:19 AM
  • User-1518883836 posted

    Awesome, yeah this is the idea , I know with the way I have my assignments set up in my asp.net that I'll have to work on this with some tweaks, but yes needed that subquery to kick in
    Muench

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 2, 2015 12:47 AM