Solved

add products.internalsku1 to a working query

Posted on 2011-03-16
3
194 Views
Last Modified: 2012-05-11

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
)
order by productid desc

works but when I add products.internalsku1 (varchar) to last line i get error



Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'where'.


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
)
where internalsku1 is not null order by productid desc






0
Comment
Question by:rgb192
3 Comments
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 150 total points
Comment Utility
Use and


elect * from products where productid IN ( 
select distinct pi.pro
ductid 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
) 
and internalsku1 is not null 
order by productid desc

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 350 total points
Comment Utility
Replacing the WHERE with AND for filter on internalsku1 will solve the problem as ewangoya mentioned.You have UNION and DISTINCT in the sub-query. UNION will take care of distinct values so you can eliminate DISTINCT.
SELECT * 
    FROM products 
   WHERE productid IN (SELECT pi.productid 
                         FROM ebaytitles et 
                              INNER JOIN packageitems pi 
                                ON pi.packageid = et.packageid 
                       UNION 
                       SELECT et.packageid 
                         FROM ebaytitles et 
                              INNER JOIN products p 
                                ON p.productid = et.packageid) 
         AND internalsku1 IS NOT NULL 
ORDER BY productid DESC

Open in new window

0
 

Author Closing Comment

by:rgb192
Comment Utility
thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dataset not reading table data 12 41
SQL Agent Timeout 5 38
Isolation level in SQL server 3 43
CREATE DATABASE ENCRYPTION KEY 1 41
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now