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).
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
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
Member
8 Points
31 Posts
How to identify a secondary object to a main object
Dec 01, 2015 05:57 AM|rolf muenchbourg|LINK
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
Contributor
5460 Points
1123 Posts
Re: How to identify a secondary object to a main object
Dec 01, 2015 11:19 AM|gimimex|LINK
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 pHope this helps.
Member
8 Points
31 Posts
Re: How to identify a secondary object to a main object
Dec 02, 2015 12:47 AM|rolf muenchbourg|LINK
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