Get value From Multple 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

Other Forums

MS Access

Get value From Multple Tables

Author

Topic

sirmilt
Starting Member

49 Posts

Posted - 2011-01-10 : 12:47:11

My database is an inventory forBooks, CDs and DVDs and contains three tables.

I need to capture one field from each of the tables. I tried the following query unsuccessfully, as well as a number of others. Does anyone have any suggestions?

Heres my latest "try":

SELECT Price as BookPrice FROM tblBooks, Price AS CDPrice FROM tblCDs, Price AS DVDPrice FROM tblDVDs WHERE tblBooks.BookLoanStatus='YES' or tblCDs.CDLoanStatus='YES' or tblDVDs.DVDLoanStatus='YES';

Any suggestions, or am I trying something not possible?

Milt

Milt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 12:54:53

[code]
SELECT SUM(BookPrice) AS BookPrice,
SUM(CDPrice) AS CDPrice,
SUM(DVDPrice) AS DVDPrice
FROM
(
SELECT Price as BookPrice ,NULL,NULL
FROM tblBooks
WHERE tblBooks.BookLoanStatus='YES'
UNION
SELECT NULL,Price as CDPrice,NULL
FROM tblCDs
WHERE tblCDs.CDLoanStatus='YES'
UNION
SELECT NULL,NULL,Price as DVDPrice
FROM tblDVDs
WHERE tblDVDs.DVDLoanStatus='YES'
)t
[/code]

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

sirmilt
Starting Member

49 Posts

Posted - 2011-01-10 : 13:31:24

viaskh16

Thank you for the very quick reply. I tried it out and only get the following error'Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '='. I also am curious as to the reason for the letter t at the end of the statement.

Milt

Milt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 13:48:10

are you sure you used posted query? post the query used if its different from what i gave

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

sirmilt
Starting Member

49 Posts

Posted - 2011-01-10 : 14:25:57

Thanks again for trying to help an old novice. I copied your post and inserted it. Here's a full copy of the stored prcedure in SSMS.

CREATE PROCEDURE qryOnLoanValue
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

--------------------------------------------------------------------------------

SELECT SUM(BookPrice) AS BookPrice,
SUM(CDPrice) AS CDPrice,
SUM(DVDPrice) AS DVDPrice
FROM
(
SELECT Price as BookPrice ,NULL,NULL
FROM tblBooks
WHERE tblBooks.BookLoanStatus='YES'
UNION
SELECT NULL,Price as CDPrice,NULL
FROM tblCDs
WHERE tblCDs.CDLoanStatus='YES'
UNION
SELECT NULL,NULL,Price as DVDPrice
FROM tblDVDs
WHERE tblDVDs.DVDLoanStatus='YES'
)t

END
GO

Milt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources