Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I want to combine these two queries because results are similiar.  I do not want any repeat int returned

Posted on 2011-03-16
5
Medium Priority
?
311 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]





I want to combine these two queries because results are similiar.  I do not want any repeat int returned

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


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



0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35151738
You can use UNION:

select distinct pi.productid from ebaytitles et
inner join packageitems pi on pi.packageid=et.packageid
order by pi.productid
union all
select distinct et.packageid from ebaytitles et
inner join products p on p.productid=et.packageid
order by et.packageid
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35151750
UNION ALL will select all values. To select only distinct values use union:

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

Author Comment

by:rgb192
ID: 35151762
select distinct pi.productid from ebaytitles et
inner join packageitems pi on pi.packageid=et.packageid
order by pi.productid
union
select distinct et.packageid from ebaytitles et
inner join products p on p.productid=et.packageid
order by et.packageid

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'union'.
0
 
LVL 24

Accepted Solution

by:
jimyX earned 2000 total points
ID: 35151809
It's because of the "order by", try this please:

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

Since the columns' names are different let's reference by the column number
0
 

Author Closing Comment

by:rgb192
ID: 35151876
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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