sub queries? - 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 2000 Forums

Transact-SQL (2000)

sub queries?

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-03-11 : 11:38:38

I have 4 tables used in a basic system to allow a business to select multiple types of software products (and respective quantities). The key issue is that I would like them to be able to come back and alter their selections if they wish to. I am trying to create a query which will display a grid with this info:

- all software types
- the total sum of selections for each software type (based on individual quantities entered by many users).
- the total quantity for each type software selected for the specific user that is logged in (given that they may or may not have any selections already. The table would look something like this:
[CODE]
SOFTWARE | PRICE | TOTAL REQUESTS(UNITS) | YOUR SELECTIONS(UNITS)
--------------------------------------------------------------------
ABC | $80.00 | 25 | NULL
--------------------------------------------------------------------
ZXY | $68.00 | 11 | 2
--------------------------------------------------------------------
RGL | $19.00 | 6 | 3
--------------------------------------------------------------------
MXR | $37.00 | 0 | NULL
--------------------------------------------------------------------
QBP | $49.00 | 44 | NULL
--------------------------------------------------------------------
LPD | $54.00 | 3 | 1
--------------------------------------------------------------------
[/CODE]

The query I have so far manages to return the results for the first three columns but I am not sure how to do a subquery to get the totals for the last column determined by the individual user business_id

Here is my query:

SELECT t1.software_id, t1.product, t1.price, t1.vendor_id, t2.SoftwareCount, t2.SoftwareSum
FROM tblSOFTWARE t1
join (SELECT software_id, COUNT(software_id) as SoftwareCount, SUM(qty) as SoftwareSum FROM
tblBUSINESSSOFTWARE GROUP BY software_id) t2 on
t1.software_id = t2.software_id

MY TABLES ARE:

-------- Create Business Table --------

CREATE TABLE tblBusiness
(
business_id INT NOT NULL IDENTITY(1,1),
business_name VARCHAR(25),
)

-------- Create Vendors Table --------

CREATE TABLE tblVendors
(
vendor_id INT NOT NULL IDENTITY(1,1),
vendor_name VARCHAR(25),
)

GO

-------- Create Software Table --------

CREATE TABLE tblSoftware
(
software_id INT NOT NULL IDENTITY(1,1),
product VARCHAR(40),
price SMALLMONEY DEFAULT(0.00),
vendor_id INT
)

GO

-------- Create Software Selection Table --------

CREATE TABLE tblSoftware_Business

(
business_id INT,
software_id INT,
qty SMALLINT
)

GO

Any ideas on how to get the count for the total items selected by the business (including the fact that they may have not selected anything, and thus must display a null). Should I used a sub-query?

JCamburn
Starting Member

31 Posts

Posted - 2005-03-11 : 16:43:22

I used a starting data set of:


SET IDENTITY_INSERT tblBusiness ON
GO
INSERT INTO tblBusiness (business_id, business_name) VALUES (1, 'Company 1')
INSERT INTO tblBusiness (business_id, business_name) VALUES (2, 'Company 2')
INSERT INTO tblBusiness (business_id, business_name) VALUES (3, 'Company 3')
INSERT INTO tblBusiness (business_id, business_name) VALUES (4, 'Company 4')
INSERT INTO tblBusiness (business_id, business_name) VALUES (5, 'Company 5')
GO
SET IDENTITY_INSERT tblBusiness OFF
GO

SET IDENTITY_INSERT tblVendors ON
GO
INSERT INTO tblVendors (vendor_id, vendor_name) VALUES (1, 'Vendor 1')
INSERT INTO tblVendors (vendor_id, vendor_name) VALUES (2, 'Vendor 2')
INSERT INTO tblVendors (vendor_id, vendor_name) VALUES (3, 'Vendor 3')
GO
SET IDENTITY_INSERT tblVendors OFF
GO

SET IDENTITY_INSERT tblSoftware ON
GO
INSERT INTO tblSoftware (software_id, product, price, vendor_id) VALUES (1, 'ABC', 80, 1)
INSERT INTO tblSoftware (software_id, product, price, vendor_id) VALUES (2, 'ZXY', 68, 1)
INSERT INTO tblSoftware (software_id, product, price, vendor_id) VALUES (3, 'RGL', 19, 2)
INSERT INTO tblSoftware (software_id, product, price, vendor_id) VALUES (4, 'MXR', 37, 2)
INSERT INTO tblSoftware (software_id, product, price, vendor_id) VALUES (5, 'QBP', 49, 3)
INSERT INTO tblSoftware (software_id, product, price, vendor_id) VALUES (6, 'LPD', 54, 3)
GO
SET IDENTITY_INSERT tblSoftware OFF
GO

INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (2, 1, 10)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (3, 1, 15)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (4, 2, 9)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (1, 2, 2)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (5, 1, 3)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (1, 3, 3)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (3, 5, 20)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (4, 5, 8)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (5, 5, 16)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (1, 6, 1)
INSERT INTO tblSoftware_Business (business_id, software_id, qty) VALUES (2, 6, 2)
GO

To get EXACTLY the output you requested, I used:


SELECT
	t3.product AS SOFTWARE,
	t3.PRICE,
	t3.SoftwareSum AS "TOTAL REQUESTS (UNITS)",
	t4.YourSelections AS "YOUR SELECTIONS (UNITS)"
FROM
	(
		SELECT 
			t1.software_id, 
			t1.product, 
			t1.price, 
			t1.vendor_id, 
			t2.SoftwareCount, 
			ISNULL(t2.SoftwareSum, 0) AS SoftwareSum
		FROM 
			tblSOFTWARE t1
			LEFT OUTER JOIN
			(
				SELECT 
					software_id, 
					COUNT(software_id) as SoftwareCount, 
					SUM(qty) as SoftwareSum 
				FROM 
					tblSoftware_Business 
				GROUP BY 
					software_id
			) t2 
			ON t1.software_id = t2.software_id
	) t3
	LEFT OUTER JOIN
	(
		SELECT 
			t1.software_id, 
			t2.SoftwareSum AS YourSelections
		FROM 
			tblSOFTWARE t1
			LEFT OUTER JOIN
			(
				SELECT 
					software_id, 
					COUNT(software_id) as SoftwareCount, 
					SUM(qty) as SoftwareSum 
				FROM 
					tblSoftware_Business 
				WHERE
					business_id = 1
				GROUP BY 
					software_id
			) t2 
			ON t1.software_id = t2.software_id
	) t4
	ON t3.software_id = t4.software_id

By the way, the query you had was referencing a table that did not exist. That is, your query referenced tblBUSINESSSOFTWARE, but your table was named tblSoftware_Business.

Hope this helps.

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-03-11 : 19:45:26

Hi, that works great. Thanks for the help.

Sorry about the bum steer with the referencing of the non-existent table. I updated the table name on the server earlier but forgot to change it in my base script (which I posted above). Thanks again!

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-03-11 : 22:30:06

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources