August 20, 2013 at 11:54 pm
Hi Professionals
I have two tables
table 1 which is dbo.newtable
software_manufacturer,productname, productversion
microsoft,excel,11.2
and table2 which is dbo.datalookuptable
software_manufacturer,productname, productversion, licensable
microsoft,excel,11.2,licensable
how do i query these two tables so I can find all the licensable information for dbo.newtable
August 20, 2013 at 11:58 pm
This article might be a good start:
Introduction to JOINs – Basic of JOINs[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2013 at 1:02 am
Your database desing could be normalized. In the current design there is a lot of duplication. Consider to design the tables as follow:
Add a ID column to dbo.newtable:
newtable_ID, software_manufacturer,productname, productversion
Add a ID column to dbo.datalookuptable and replace the fields "software_manufacturer,productname, productversion" with the ID column referencing the dbo.newtable:
datalookuptable_ID, newtable_ID, licensable
You can query both tables by joining them on the newtable_ID field:
select *
from newtable
inner join datalookuptable
on newtable.newtable_ID = datalookuptable.newtable_ID
August 22, 2013 at 3:44 pm
hi there
i cant add an id column as the dbo.newtable is created dynamically from spreadsheets
please advise
August 22, 2013 at 4:03 pm
SELECT n.software_manufacturer, n.productname, n.productversion, d.licensable
FROM dbo.newtable n
JOIN dbo.datalookuptable d ON d.software_manufacturer = n.software_manufacturer
AND d.productname = n.productversion
AND d.productversion = n.productversion
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 22, 2013 at 4:11 pm
Hi Errland
I have just actually posted a query regarding this.
I have just done that but my query seems to return to many rows eg
select count(*) from dbo.newtable
returns 600,00 + rows
************************************
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
and d.category in ('Licensable','Non Licensable')
order by d.category
returns almost 3 million rows
am i going wrong somewhere
August 22, 2013 at 4:21 pm
And how many rows are there in the other table?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 22, 2013 at 5:53 pm
in the datalookuptable there are 291406 rows
so when i search for licensable products I get
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
and d.category = 'Licensable'
order by d.category
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
when i put in distinct I get
select distinct 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
and d.category = 'Licensable'
order by d.category
Microsoft CorporationOffice Access 200311.x22/04/200530/04/2008Licensable
would the distinct be the right approach
thanks again
August 23, 2013 at 12:38 am
The correct approach is to model your data correctly and enforce primary keys. It seems that you have a lot of duplicate rows. DISTINCT is only a band-aid to fix that problem.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 23, 2013 at 12:51 am
Oracle765 (8/22/2013)
Hi ErrlandI have just actually posted a query regarding this.
I have just done that but my query seems to return to many rows eg
select count(*) from dbo.newtable
returns 600,00 + rows
************************************
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
and d.category in ('Licensable','Non Licensable')
order by d.category
returns almost 3 million rows
am i going wrong somewhere
I think you have already posted this problem to another forum:
http://www.sqlservercentral.com/Forums/Topic1487557-391-1.aspx
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy