trying to match up two tables

Question

Post reply

trying to match up two tables

Oracle765

SSCrazy

Points: 2862
More actions
August 20, 2013 at 11:54 pm

#277176

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

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

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Oracle765 SSCrazy Points: 2862 More actions · Answer 3

hi there

i cant add an id column as the dbo.newtable is created dynamically from spreadsheets

please advise

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

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]

Oracle765 SSCrazy Points: 2862 More actions · Answer 5

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

And how many rows are there in the other table?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Oracle765 SSCrazy Points: 2862 More actions · Answer 7

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 8

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]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

Oracle765 (8/22/2013)
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

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/