Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

select * from products where productid=(results of query1)

Posted on 2011-03-16
2
Medium Priority
?
405 Views
Last Modified: 2012-05-11
USE [ow1222]
CREATE TABLE [dbo].[products](
      [productid] [int] IDENTITY(1,1) NOT NULL,
      [folderid] [smallint] NULL,
      [archive] [int] NULL,
      [title] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [titleinternal] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [price] [decimal](10, 2) NULL,
      [pricesalesman] [decimal](10, 2) NULL,
      [estimatedcost] [decimal](10, 2) NULL,
      [site] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [orderable] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [leadtime] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [country] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [condition] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [manufacturer] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [model] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [mpn] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [active] [tinyint] NULL,
      [discontinued] [int] NULL,
      [important] [tinyint] NULL,
      [commision] [smallint] NULL,
      [commissionamount] [smallint] NULL,
      [spiff] [smallint] NULL,
      [spiffqualifier] [decimal](10, 2) NULL,
      [spiffamount] [decimal](10, 2) NULL,
      [sundesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [dsndesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [pyxisdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [hdewdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [searchpriority] [int] NULL,
      [autotxtname] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [currentfeed] [int] NULL,
      [pgfeed] [int] NULL,
      [internalsku] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [asin] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [inwarehouse] [smallint] NULL,
      [sentquantity] [smallint] NULL,
      [date] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [estimateddate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [date1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [inwarehouse2] [smallint] NULL,
      [inwarehouse3] [smallint] NULL,
      [cost1] [decimal](10, 2) NULL,
      [cost2] [decimal](10, 2) NULL,
      [cost3] [decimal](10, 2) NULL,
      [internalsku2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [internalsku3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ourinwarehouse] [smallint] NULL,
      [ourinwarehouse2] [smallint] NULL,
      [ourinwarehouse3] [smallint] NULL,
      [sentquantity2] [smallint] NULL,
      [sentquantity3] [smallint] NULL,
      [date2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [date3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [estimateddate2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [estimateddate3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [g1] [smallint] NULL,
      [g2] [smallint] NULL,
      [g3] [smallint] NULL,
      [e1] [smallint] NULL,
      [e2] [smallint] NULL,
      [e3] [smallint] NULL,
      [skucount] [smallint] NULL,
      [skucount2] [smallint] NULL,
      [skucount3] [smallint] NULL,
      [startprice] [decimal](8, 2) NULL,
      [buyitnowprice] [decimal](8, 2) NULL,
      [s1] [smallint] NULL,
      [s2] [smallint] NULL,
      [s3] [smallint] NULL,
      [s0] [smallint] NULL,
      [oldstartprice] [decimal](7, 2) NULL,
      [auk] [smallint] NULL,
      [auk2] [smallint] NULL,
      [auk3] [smallint] NULL,
      [ny] [smallint] NULL,
      [ny2] [smallint] NULL,
      [ny3] [smallint] NULL,
      [ebaytitle] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [internalsku1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [inwarehouse1] [smallint] NULL,
      [ourinwarehouse1] [smallint] NULL,
      [sentquantity1] [smallint] NULL,
      [skucount1] [smallint] NULL,
      [auk1] [smallint] NULL,
      [ny1] [smallint] NULL,
      [cost] [decimal](10, 2) NULL,
      [internalsku4] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [inwarehouse4] [smallint] NULL,
      [ourinwarehouse4] [smallint] NULL,
      [sentquantity4] [smallint] NULL,
      [auk4] [smallint] NULL,
      [ny4] [smallint] NULL,
      [cost4] [decimal](10, 2) NULL,
      [s4] [smallint] NULL,
      [skucount4] [smallint] NULL,
      [e4] [smallint] NULL,
      [g4] [smallint] NULL,
      [date4] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]







CREATE TABLE [dbo].[packageitems](
      [packageitemid] [int] IDENTITY(1,1) NOT NULL,
      [packageid] [int] NOT NULL,
      [productid] [int] NOT NULL,
      [active] [int] NOT NULL,
 CONSTRAINT [PK__packageitems__0DAF0CB0] PRIMARY KEY CLUSTERED
(
      [packageitemid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]




CREATE TABLE [dbo].[ebaytitles](
      [titleid] [int] IDENTITY(1,1) NOT NULL,
      [title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [type] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [seller] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [packageid] [int] NULL,
      [gallery] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [price] [money] NOT NULL,
      [webprice] [money] NOT NULL,
      [buyitnowprice] [decimal](7, 2) NULL,
      [startprice] [decimal](7, 2) NULL,
      [oldstartprice] [decimal](7, 2) NULL,
      [itemid] [bigint] NULL,
      [date] [datetime] NULL,
      [oldbuyitnowprice] [decimal](7, 2) NULL,
      [s1] [smallint] NULL,
      [b1] [smallint] NULL,
      [site] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [site2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [oldstartprice2] [decimal](7, 2) NULL,
      [oldbuyitnowprice2] [decimal](7, 2) NULL,
      [u1] [decimal](7, 2) NULL,
      [ea] [smallint] NULL
) ON [PRIMARY]



query1 returns results in one column

select distinct pi.productid from ebaytitles et
inner join packageitems pi on pi.packageid=et.packageid
union
select distinct et.packageid from ebaytitles et
inner join products p on p.productid=et.packageid
order by 1




I want a query
select * from products where productid=(results of query1)
0
Comment
Question by:rgb192
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35151915
you can do with subquery
select * from products where productid IN ( 
select distinct pi.productid from ebaytitles et
inner join packageitems pi on pi.packageid=et.packageid
union
select distinct et.packageid from ebaytitles et
inner join products p on p.productid=et.packageid
)

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 35152024
thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Integration Management Part 2
Screencast - Getting to Know the Pipeline

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question