|
Actually, I don't want to create a view to get this result.. I think that it is possible to get the result with one line (maybe..big line) of SQL
Thanks and Regards,
Michael Sync ( Blog: http://michaelsync.net)
"Please vote to let me (and others) know if this answer helped you or not. A 5 vote tells people that your question has been answered successfully and that I've pitched it at just the right level. Thanks."
|
|
|
|
|
One way of doing this would be:
1) CROSS JOIN Customers with Stocks to get every possible combination of customers and stocks.
2) Do a LEFT JOIN between 1) and CustomersStocks. Get a DISTINCT list of the Customer IDs where there is no matching record on the RHS of the join. These are the customers who have not bought at least 1 of the available stocks.
3) Return the records from Customers where the Customer ID is NOT IN the set of records returned in 2) These will be the customers who have purchased all available stocks.
Hope this helps.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks... I will try and will let you know..
pmarfleet wrote: 1) CROSS JOIN Customers with Stocks to get every possible combination of customers and stocks.
This line will be the same as all data from CustomersStocks.
pmarfleet wrote: RHS
RHS stand for?
Thanks and Regards,
Michael Sync ( Blog: http://michaelsync.net)
"Please vote to let me (and others) know if this answer helped you or not. A 5 vote tells people that your question has been answered successfully and that I've pitched it at just the right level. Thanks."
|
|
|
|
|
Michael Sync wrote: This line will be the same as all data from CustomersStocks.
No it won't. CustomersStocks contains combinations of customers and stocks where the customer has bought the stock. Cross joining Customers and Stocks returns the cartesian product of the two tables, ie. every possible combination of customers and stocks regardless of whether a customer has actually bought the stock. The purpose of this is to compare the difference between this set and CustomersStocks to find which customers have not bought at least one of the available stocks. Once you have identified these customers, by process of elimination you can find the customers who have bought all available stocks.
Michael Sync wrote: RHS stand for?
Right-hand side.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
i want to write stored procedure insuch a way where in it has to return the table name used stored procedure.
for example
create procedure proc1
as
begin
select * from student
end
once u exit exec proc1 it has return the table name used i.e "Student"
could you please help in doing this.
or it is possible in sql.
reply me soon at suhailquadri@gmail.com
Suhail Ahmed Mohammed, Application
Engineer,
Cybernet Slash Support
|
|
|
|
|
I don't think there is any way to do this. Besides, is it necessary? Stored procedure are usually coded to select data from a particular table or tables. You would know which table(s) each stored procedure used, so you could use an OUTPUT parameter to return a varchar value which was hard-coded to that table name for each stored procedure.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
well using sp_depends storedprocedurename , it will list all the details of a stored procedure.
|
|
|
|
|
This returns the query plus a table with 1 row - 1 column that contains the table name. Seems like a waste of time since the developer already knows the table name.
CREATE PROCEURE proc1
AS
SELECT
*
FROM
student
SELECT
'student'
|
|
|
|
|
Michael Potter wrote: Seems like a waste of time since the developer already knows the table name.
It is.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
you need to access the information about stored procedure dependencies written by another dba.
I m displaying the information of stored procedure dependencies using sp_depends procedurename.
It was requested by one of my client.
Now programmetically i dispalyed in table and displayed accordingly.
|
|
|
|
|
Thanks i got the solution.
|
|
|
|
|
Please help me in tuning stored procedures. I want step by step explanation in detail
Pavan Kumar
|
|
|
|
|
Hi,
Pavan, it is not a pre-defined process. All you need to do is to take care of code that is not required, or that can me optimized. Though DBMS will optimize it, but only you can tell if the task being done is required or not.
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
This [^]and this[^] article might help.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am very new to database programming.
I am now working on an MFC application that use ADO to connect to ACCESS database.
Right now I alway close connection after finish database task.
It goes something like this.
After user click update button , I open the connection and then execute
some query (via Execute method of connection pointer).
After the work is done I alway immediately close the connection(via Close method of connection pointer).
The problem is,when connection is not open user may delete .mdb file as they want
Is there a way I can prevent the user from delete database file while the program is running ? , like when you open database with MS ACCESS .
Thank for answer
|
|
|
|
|
hi there,
i copied my database file (.mdf, .ldf and 2 .sdf)
and rename all
then i go back to sql and right-click 'Databases' and then on 'Attach...'
next i selected mine renamed database .mdf file but there was i problem that it was showing previous name of file in "Original File Name" column
can someone tell how can i change original filename?
thanks
Becoming Programmer...
|
|
|
|
|
Hi,
Changing the filename that way, as if DBMS files are simple text files, is not a right thing to do. It stores the data itself in the file, definitly encrypted!
So, you will have to do some magic to do that. One approach may be as follows
1. Create new db of your desired names and settings
2. Create the tables
3. Copy from current to the new tables using insert ... select ...
Hope that helps!
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
hahaha, wow, what the way you tell.....
nice joke
Becoming Programmer...
|
|
|
|
|
The way i showed was as simple and as easy to follow as possible! Specialy for someone who is renaming DB files the way you follow.
And atleast show some respect, it was surely not the best solution but atleast do-able and easy, which i thought was the best for renaming DB files without getting into much details, instead of the way you were following.
Thanks & Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
And with all due respect, if you are familiar with stored procedures, you may use "sp_renamedb" for renaming database. But before doing that do change it to the original name, so no issue arises.
Regards,
Adeel
Being polite will not harm you.
|
|
|
|
|
sorry boss if you hurt with what i said
that problem is making me crazy since last weak now you can think that what will happen when someone reply something like you did
i just want to copy my database and use it something like clone
i will try "sp_renamedb" too
-- modified at 8:01 Monday 12th November, 2007
yeah renaming work, now i got a trick that will help me out
thanks dude
Becoming Programmer...
|
|
|
|
|
Welcome my friend!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
madeelch1986 wrote: It stores the data itself in the file, definitly encrypted!
What has encryption got to do with anything?
|
|
|
|
|
Can you not backup your database completely (to a .bak file), create a new empty database, and then restore in your backup to the new database?
Seems a little more simple.
|
|
|
|
|
my problem solved
first deattach database then copy it, attach previous one, change its name to new then attach again(another one)
its my trick that solved my problem
anyway thanks for trying to help
Becoming Programmer...
|
|
|
|