August 22, 2013 at 4:08 pm
Hi All
I am confused in trying to find all licensable and non licensable products that match my datalookuptable but it seems to be returning to many rows
My queries are as follows
select count(*) from dbo.newtable
returns 629019 rows
**************************************************
select n.*,d.category
from newtable n,datalookuptable d
where n.softwaremanufacturer = d.amended_sw_manufacturer
and n.productname = d.amended_product_name
and n.productversion = d.amended_product_version
and d.category in ('Licensable','Non Licensable')
order by d.category
returns almost 3 million rows
am i missing something
August 23, 2013 at 12:30 am
you are getting so much rows because you are doing CROSS JOIN...
I have modified your query, execute it and lemme know if you still find that issue-
select n.*,d.category
from newtable n
JOIN datalookuptable d
ON n.softwaremanufacturer = d.amended_sw_manufacturer
and n.productname = d.amended_product_name
and n.productversion = d.amended_product_version
WHERE d.category in ('Licensable','Non Licensable')
order by d.category
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 23, 2013 at 3:13 am
Actually, the OPs query is not a CROSS JOIN. It is an ANSI-89 style inner join. Why it is returning too many rows may have more to do with the data in the lookup table. If one record in the newtable matches more than one row in the lookup table, you will end up with more rows returned than are in the newtable to start with.
August 23, 2013 at 5:09 am
-- try this
SELECT n.*, d.category, d.n
FROM newtable n
INNER JOIN (
SELECT
category,
amended_sw_manufacturer,
amended_product_name,
amended_product_version,
n = COUNT(*)
FROM datalookuptable
WHERE category IN ('Licensable','Non Licensable')
GROUP BY category, amended_sw_manufacturer, amended_product_name, amended_product_version
) d
ON n.softwaremanufacturer = d.amended_sw_manufacturer
AND n.productname = d.amended_product_name
AND n.productversion = d.amended_product_version
ORDER BY d.category
-- and this
SELECT n.*, d.category, d.n
FROM newtable n
LEFT JOIN (
SELECT
category = MAX(category),
amended_sw_manufacturer,
amended_product_name,
amended_product_version,
n = COUNT(*)
FROM datalookuptable
WHERE category IN ('Licensable','Non Licensable')
GROUP BY amended_sw_manufacturer, amended_product_name, amended_product_version
) d
ON n.softwaremanufacturer = d.amended_sw_manufacturer
AND n.productname = d.amended_product_name
AND n.productversion = d.amended_product_version
ORDER BY d.category
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 25, 2013 at 3:41 pm
that last query returns results even if the category is null
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply