Searching within two 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

General SQL Server Forums

New to SQL Server Programming

Searching within two tables.

Author

Topic

sambrown180
Starting Member

38 Posts

Posted - 2008-11-20 : 09:31:52

Hi i need to run a search that can return data from two tables.

I have an order table and a card details table.

I need to find customerID (foreign key) that appear in the order table and the card details table. Once this is done i need the same query to find any cards that expiry dates have passed.

Would this be possible or is it asking the system to do to much.

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-20 : 09:45:10

SELECT * FROM [ORDER TABLE] INNER JOIN [CARD DETAIL] ON [ORDER TABLE].CUSTOMERID=[CARD DETAIL].CUSTOMERID
WHERE [FIELDNAME]=[VALUE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:18:01

conditons should be

WHERE [CARD_DETAIL].CustomerID=@CustomerID AND [CARD_DETAIL].ExpiryDate < GETDATE()

@CustomerID is passed value.

sambrown180
Starting Member

38 Posts

Posted - 2008-11-26 : 04:21:19

Thanks for this what does the @customerID value do? it came up with the error "Must declare the scalar variable "@CustomerID"."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 04:33:15

quote:
Originally posted by sambrown180

Thanks for this what does the @customerID value do? it came up with the error "Must declare the scalar variable "@CustomerID"."

thats the variable/parameter through which you send the value

you should declare it before using either in sp definition if paramaeter or using DECLARE if its a variable.

sambrown180
Starting Member

38 Posts

Posted - 2008-11-26 : 04:38:52

Should it just be "DECLARE @customerID is passed vale"
Sorry I am completely lost with this

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-26 : 04:46:48

DECLARE @customerID INT --(datatype)
SET @customerID = 123

sambrown180
Starting Member

38 Posts

Posted - 2008-11-26 : 04:51:41

SELECT * FROM [ORDERS] INNER JOIN [CARDDETAILS] ON [ORDERS].CUSTOMERID=[CARDDETAILS].CUSTOMERID

WHERE [cardDetails].CustomerID=@CustomerID AND [CARDDETAILS].ExpiryDate < GETDATE()

DECLARE @customerID INT
SET @customerID=123

I am entering this in bust still recieving the "Must declare the scalar variable "@CustomerID"." error.

When i try:

SELECT * FROM [ORDERS] INNER JOIN [CARDDETAILS] ON [ORDERS].CUSTOMERID=[CARDDETAILS].CUSTOMERID

DECLARE @customerID INT
SET @customerID=123

WHERE [cardDetails].CustomerID=@CustomerID AND [CARDDETAILS].ExpiryDate < GETDATE()

It finds an error in the "WHERE" statement

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-26 : 05:02:06

Try with stmts in the below order. First declaration part of a variable and assigning a value to it and after that use that variable

DECLARE @customerID INT
SET @customerID=123

SELECT * FROM [ORDERS] INNER JOIN [CARDDETAILS] ON [ORDERS].CUSTOMERID=[CARDDETAILS].CUSTOMERID

WHERE [cardDetails].CustomerID=@CustomerID AND [CARDDETAILS].ExpiryDate < GETDATE()

sambrown180
Starting Member

38 Posts

Posted - 2008-11-26 : 05:05:30

Brilliant works fine thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-26 : 05:10:54

welcome....

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-26 : 11:26:13

sambrown180 - BOL is a great resource for these types of questions. While I understand we're in the newbies forum, these are fairly basic and attempting to resolve your issues first will go a long way in your development - IMO

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources