add products.internalsku1 to a working query


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






LVL 1
rgb192Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
Ephraim WangoyaConnect With a Mentor Commented:
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
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.