How to make this query...

raindm

Hi all. I have tried making this query for a while and i didn't manage to. I know it is something related to JOIN, but i didn't manage to make it work :\ I have two tables. Table1- db_stockprices Table2- db_stockSymbols db_stockprices is getting updated every day with stock daily prices. db_stockprices looks like: ID, SymbolID, Price,Date 1 1 33.21 1/1/2007 2 1 33.14 1/2/2007 3 1 34.21 1/3/2007 4 2 11.42 1/1/2007 5 2 11.73 1/2/2007 6 3 18.32 1/1/2007 7 3 19.11 1/2/2007 8 3 18.41 1/3/2007 9 4 52.44 1/1/2007 and so on for all the stocks. ID is Primary Key SymbolID is related to SymbolID in db_stockSymbols which contains all the stock information. What i am trying to do is an SQL QUERY that will delete all same SymbolID Rows if a specific INPUT date do not exist within that SymbolID. Example: If the INPUT date for the query is 1/3/2007 all rows with SymbolID "2" abd "4" will be deleted since 1/3/2007 does not exist in SymbolID "2" and "4". Moreover, the row in db_stockSymbols with SymbolID "2" and "4" will also be deleted. Is this possible within 1 SQL Query I would really appreciate a good query example for this example..

ggciubuc

I'm away from a SQL Server computer and i did not test the following but try it on a test table:

delete from db_stockprices

where symbolid in (select Symbolid from db_stockprices where Date<>@yourdatavalue)

hth

raindm

Hi ggciubuc

This wont do it right.. This query will delete the whole table.

Code Snippet

select Symbolid from db_stockprices where Date<>@yourdatavalue

this will select all symbolIDs that does not have @yourdatevalue which is most cases is the whole SynbolIDs..

Am i correct

ggciubuc

No, let's examine the select I proposed :

delete from db_stockprices

where symbolid in (select Symbolid from db_stockprices where Date<>@yourdatavalue)

let's say

"select Symbolid from db_stockprices where Date<>'1/3/2007' " return "2" and "4" so then select for deleting will be transformed in

delete from db_stockprices where symbolid in ("2","4")

so will be deleted all rows you desired.

So, this solution I think will work.

raindm

I see what you mean.

I thought by writing "select Symbolid from db_stockprices where Date<>'1/3/2007' "

it will also return SymbolID "1" and "3" because "1" and "3" contains dates which are different from "1/3/2007" (the 1/2/2007 and the 1/1/2007)

ID, SymbolID, Price,Date

1 1 33.21 1/1/2007

2 1 33.14 1/2/2007

3 1 34.21 1/3/2007

4 2 11.42 1/1/2007

5 2 11.73 1/2/2007

6 3 18.32 1/1/2007

7 3 19.11 1/2/2007

8 3 18.41 1/3/2007

9 4 52.44 1/1/2007

Isaac Kunen

Hi Folks,

Unfortunately, the answer Gigi gave won't work. The problem is that

select Symbolid from db_stockprices where Date<>@yourdatavalue

will return the symbols from all rows whose dates don't match your date. I.e., if a stock has a price row on a date other than your date, then it will be returned. The only symbols that won't be returned will be those that only had a price for the date entered.

You can build up a query that will do what you want. There may be other, better ways to do this, but here's one way to think through the problem. Start by selecting all symbols that do have a trade on the date you want:

SELECT SymbolID FROM db_stockprices WHERE Date = @date

If @date is 1/3/2007, this will return the symbols 1 and 3. It may seem counterintutive to do this, but now you can find all symbols that are not in this list:

SELECT SymbolID FROM db_stockprices WHERE SymbolID NOT IN (

SELECT SymbolID FROM db_stockprices WHERE Date = @date

)

Again, with @date = 1/3/2007, this will return symbols 2 and 4. Now you can build the delete:

DELETE FROM db_symbols WHERE SymbolID IN (

SELECT SymbolID FROM db_stockprices WHERE SymbolID NOT IN (

SELECT SymbolID FROM db_stockprices WHERE Date = @date

)

)

Hope this helps!

-Isaac

ggciubuc

Yes Isaac but to have a full answer for this post raindm have to associate DELETE CASCADE option to the relationship between db_stockSymbols and db_stockprices; in this way all rows from db_stockprices will be deleted and deleteing operation can be made in 1 SQL.

hth

Isaac Kunen

Gigi,

Good point---I missed that from the original post.

Cheers,

-Isaac

raindm

Hi, now it makes more sense

About the " DELETE CASCADE " I do it from SQL Server Management (2005) If so where exactly

raindm

I found where to change the "Delete Cascade"

In db_stockprices i select SymbolID (its a forien key) and press relationship. I have a relationship i already created -"FK_db_stockprices_db_stockSymbols" . there i have INSERT and UPDATE specifications.. I see the options inside "INSERT and UPDATE specifications.." and i see DELETE RULE and there is "CASCADE". Is this correct does it effect each other no matter from what table i delete a row

I understand I can use "Update rule" so every update to the SymbolID in table1 will affect table2 and the opposite

ggciubuc

When you delete a row in table1 that supply foreign key for table2 the correspondent rows in table2 will be deleted (DELETE CASCADE) when modify the key in the table1 the values for foreign keys in table2 will be modified too (UPDATE CASCADE).

raindm

Okey,

thanks a lot you guys.