SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query over 4 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

snoopy145
Starting Member

5 Posts

Posted - 07/31/2013 :  08:02:19  Show Profile  Reply with Quote
Hi

I have a software database that I need to create a unique query for.

There are 5 tables (NONSTANDARD, NOTALLOWED, PEACYNONSTANDARD, STANDARD, software)

The report table is called software this has a list of all userids, PC's, and software and version installed on each computer.

I need a query that basically matches the values in the software table under columns Software and Version to the other tables then print which table it matched those entries in.

So for example if the software table said userid jim5bt PC bt55ss software adobe acrobat version 6 print non standard software.


Would it start like this:-

SELECT

software.UserID, software.Name, software.Assetname, software.Software, software.Version, NOTALLOWED$.Software AS Expr1, NOTALLOWED$.Version AS Expr2,STANDARD$.Software AS Expr3, STANDARD$.Version AS Expr4, PEACYNONSTANDARD$.Software AS Expr5, PEACYNONSTANDARD$.Version AS Expr6, NONSTANDARD$.Software AS Expr7, NONSTANDARD$.Version AS Expr8

FROM NONSTANDARD$ CROSS JOIN
NOTALLOWED$ CROSS JOIN
PEACYNONSTANDARD$ CROSS JOIN
software CROSS JOIN
STANDARD$



Hope this makes sense

Edited by - snoopy145 on 07/31/2013 08:05:38

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  08:17:46  Show Profile  Reply with Quote
Nope. what you're doing now is a cartersian product among table data.
Ideally you will have relationship between tables represented by common columns and you use inner/left/full join among tables on these columns to get related data depending on your output scenario

suggest you to refer more on joins here

http://www.w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

snoopy145
Starting Member

5 Posts

Posted - 07/31/2013 :  08:31:53  Show Profile  Reply with Quote
Hi Thanks for the info

The 4 category tables (nonstandnard, standard, notallowed, PEACYNONSTANDARD) all have only 2 columns called software and version this should be related to the software table which has 2 columns in it called software and version.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 07/31/2013 :  09:20:17  Show Profile  Reply with Quote
What you need is probably something like this. This assumes a 1-0 or 1-1 relationship between the software table and other tables
SELECT  s.UserID ,
        s.Name ,
        s.Assetname ,
        s.Software ,
        s.Version ,
        ns.Software AS Expr1 ,
        na.Version AS Expr2 ,
        st.Software AS Expr3 ,
        st.Version AS Expr4 ,
        pn.Software AS Expr5 ,
        pn.Version AS Expr6 ,
        ns.Software AS Expr7 ,
        ns.Version AS Expr8
FROM    software s
		LEFT JOIN NONSTANDARD$ ns ON ns.software = s.software AND ns.version = s.version
        LEFT JOIN NOTALLOWED$ na ON na.software = s.software AND na.version = s.version
        LEFT JOIN PEACYNONSTANDARD$ pn ON pn.software = s.software AND pn.version = s.version
        LEFT JOIN STANDARD$ st ON st.software = s.software AND st.version = s.version
Go to Top of Page

snoopy145
Starting Member

5 Posts

Posted - 07/31/2013 :  10:33:27  Show Profile  Reply with Quote
Thanks for the above tip but when running the query I get the folowing error:-

Msg 8114, Level 16, State 5, Line 3
Error converting data type nvarchar to float.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  10:33:44  Show Profile  Reply with Quote
quote:
Originally posted by snoopy145

Hi Thanks for the info

The 4 category tables (nonstandnard, standard, notallowed, PEACYNONSTANDARD) all have only 2 columns called software and version this should be related to the software table which has 2 columns in it called software and version.




Again question is whether relationship is 1 to many? If that being case, do you want all combination or just one among them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

snoopy145
Starting Member

5 Posts

Posted - 07/31/2013 :  10:41:14  Show Profile  Reply with Quote
Hi

I suppose from the software table the software and version should only relate to one entry in one of the 4 category tables:-

Software table
Software = Adobe acrobat
Version = 9

Nonstandard software table
Software = Adobe acrobat
Version = 9
Go to Top of Page

snoopy145
Starting Member

5 Posts

Posted - 07/31/2013 :  10:44:25  Show Profile  Reply with Quote
Would be ideal if I could use the CASE statement to create a temp column in the software table that has the category value of where the table the software was found in.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/01/2013 :  02:35:13  Show Profile  Reply with Quote
quote:
Originally posted by snoopy145

Would be ideal if I could use the CASE statement to create a temp column in the software table that has the category value of where the table the software was found in.


you can use condition like


..
CASE WHEN t2.IDField IS NOT NULL THEN 'Table 2 Name'
 WHEN t3.IDField IS NOT NULL THEN 'Table 3 Name'
 WHEN t4.IDField IS NOT NULL THEN 'Table 4 Name'
..
END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000