Query problem - 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

General SQL Server Forums

New to SQL Server Programming

Query problem

Author

Topic

Leonius
Starting Member

7 Posts

Posted - 2011-08-23 : 10:57:22

Im using ms sql 2008,

I have two tables, table 1 has two values out of 5 I want to return, one of the columns contains values that appear in table two, not all of the values appear in table two, I only want to return the values for the two columns in table one that correspond with the value not appearing in table two, and also return a sum value.

The tables are as follows,

table 1 is console games, columns are, GameNumber(PK), GameDetails, RetailPrice, ConsoleFormat, GamesStock,

table two is,
InvoiceNumber(PK), GameNumber(FK-from table1), GameQTYOrdered, TransactionCompleted,

I need to return the following from the query,

ConsoleFormat, GameNumber and total cash value of games not appearing in table 2, so that would be games never processed in an order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 11:20:39

you mean this?


SELECT ConsoleFormat,GameNumber,SUM(RetailPrice) as Total
FROM [console games] c
LEFT JOIN [table two] t
ON t.GameNumber = c.GameNumber
WHERE t.GameNumber IS NULL
GROUP BY ConsoleFormat,GameNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Leonius
Starting Member

7 Posts

Posted - 2011-08-23 : 11:49:45

Thats something like what I was looking at, I was trying to do it this way, obviously wrong as I was getting all rows insted of just the one I wanted that was not a value in table 2.

SELECT ConsoleFormat,GameNumber,SUM(RetailPrice)
FROM INNER JOIN
t ON t.GameNumber = c.GameNumber
WHERE ('t.GameNumber <> c.GameNumber')

Ill give that a try thanks,

the sum, i need to times the sale price by the number of stock items, how should I write the sum part to do this, thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 11:57:56

sorry if you're looking at ones not present in [table two] then how do you get qty?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Leonius
Starting Member

7 Posts

Posted - 2011-08-23 : 12:01:53

To get the qty using the GameStock value from table 1, so the premise is, find the game not sold then multiply the RetailPrice by the Gamestock value,

sorry if I was vague.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 12:18:40

then try:-


SELECT ConsoleFormat,GameNumber,SUM(RetailPrice * GameStock) as Total
FROM [console games] c
LEFT JOIN [table two] t
ON t.GameNumber = c.GameNumber
WHERE t.GameNumber IS NULL
GROUP BY ConsoleFormat,GameNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Leonius
Starting Member

7 Posts

Posted - 2011-08-24 : 09:50:25

Many thanks, just what I was looking for, very many thanks indeed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources