Query over 4 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query over 4 tables

Author

Topic

snoopy145
Starting Member

5 Posts

Posted - 2013-07-31 : 08:02:19

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 08:17:46

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

snoopy145
Starting Member

5 Posts

Posted - 2013-07-31 : 08:31:53

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-31 : 09:20:17

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

snoopy145
Starting Member

5 Posts

Posted - 2013-07-31 : 10:33:27

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 10:33:44

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

snoopy145
Starting Member

5 Posts

Posted - 2013-07-31 : 10:41:14

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

snoopy145
Starting Member

5 Posts

Posted - 2013-07-31 : 10:44:25

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 02:35:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources