Solved

add products.internalsku1 to a working query

Posted on 2011-03-16
3
215 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
ID: 35152084
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
ID: 35152183
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
ID: 35152458
thanks
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL joins 9 48
Order by but want it in specific order 2 33
Problem with SqlConnection 4 168
SQL Server Insert where not exists 24 41
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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