|
Hi,
How can merge two tables? the select command for the 2 tables are as follows
select groupid, staff.name[created] from groups,staff where groups.created=staff.staffid
groupid created
------ --------
1 A
2 B
select groupid,staff.name[modified] from groups,staff where groups.modified=staff.staffid
groupid modified
------- --------
1 C
2 D
i have to merge these two output as
groupid created modified
------- ------- --------
1 A C
2 B D
please send me the sql query or please tell me how can i merge these using the dataset or any other solution. I have to implement this in my final year project.
Thanks in advance.
|
|
|
|
|
select groupid
,staff1.name created
,staff2.name modified
from groups
,staff staff1
,staff staff2
where groups.created = staff1.staffid
and groups.modified = staff2.staffid
order by groupid;
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Chris Meech wrote: select groupid ,staff1.name created ,staff2.name modified from groups ,staff staff1 ,staff staff2 where groups.created = staff1.staffid and groups.modified = staff2.staffid order by groupid;
Hi, Chris Meech
Your query is working fine.
Thanks.
|
|
|
|
|
Hi friends:
How i can specify the max and min size of a connection pooling in a OldbConnection ?????
I hope you can helpme
Thanks in advance
|
|
|
|
|
Documentation says that connection pooling is handled by provider for OleDbConnection. If you are using SQL Server, you can use SqlConnection class, and use the Pooling , Max/Min Pool Size entries in the connection string.
--------
"I say no to drugs, but they don't listen."
- Marilyn Manson
|
|
|
|
|
Hi, I'am trying to save a jpeg image from a file via vb6.0 ADODB to MySQL 5.0 Table i've named the datafield 'Photo ' and the datatype BLOB. If i do this Vb gives me een error message or no message at all.
The picture will not be saved.
Can anyone help me????please
Greetz Job
|
|
|
|
|
I am writing a stored proc. Currently doing a "SELECT INTO" to copy data from one table to a new one. But eventually
a. I want to do it most optimally as the data is gonna be huge
b. Also it should be able to insert data in an existing table.
What SQL stmt/function should I use?
Salil Khedkar [^]
|
|
|
|
|
INSERT INTO [table]
SELECT [col1], [col2], [col3], ...
FROM ...
Stability. What an interesting concept. -- Chris Maunder
-- modified at 8:32 Thursday 2nd February, 2006
|
|
|
|
|
Hi Guys,
I have not known so much about the SQL Server and MSDE, so just rush to here to get help.
Can any one tell me When records are deleted, is the space immediately compacted or when is it compacted?
and Is the compacting configurable? How to set it up? Since I am using the SQL buddy
For large number of records deleted (eg. Archival), is the database compacted immediately!
Many thanks in advance! I am really appreciate your help.
|
|
|
|
|
SQL Server does not generally compact free space. To do so, use the Shrink feature (in T-SQL, DBCC SHRINKDATABASE ).
To have this occur automatically, set the 'Auto Shrink' option. You can do this in T-SQL with ALTER DATABASE db SET AUTO_SHRINK ON . MSDE has this option turned on by default.
If Auto Shrink is set, the shrinking operation occurs when there is more than 25% free space in the file. The resulting file will have 25% space free or be the size it was when originally created, if that is larger.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi Mike,
Thanks very much for your reply. So the compacting is a default option which already be turned on in MSDE? How about SQL Server, the option is also turned on by default?
Is there any way I can configure this option so that it can be performed when there is 10% free space?
Many thanks for your help!!
|
|
|
|
|
I have a problem in restoring sql server database in asp.net
I have first written code for full backup and differential backup of database, it is working properly.
For full backup code is :
-----------------
-----------------
objBackUp.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
objBackUp.Database = "tempbakup"
objBackUp.Devices = ""
objBackUp.Files = Server.MapPath("tempbakupFull.zip")
objBackUp.MediaName = Server.MapPath("tempbakupFull.zip")
objBackUp.MediaDescription = "TempBakup Database Backup Full" & Now
-------------------
------------------
For Differential backup :
objBackUp.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Differential
objBackUp.Database = "tempbakup"
objBackUp.Devices = ""
objBackUp.Files = Server.MapPath("tempbakupDiff.zip")
objBackUp.MediaName = Server.MapPath("tempbakupDiff.zip")
objBackUp.MediaDescription = "TempBakup Database Backup Differential" & Now
But when I used wrote following code for restore databse, I got problem
"The volume on device 'c:\inetpub\wwwroot\TestApp\tempbakupfull.zip' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set."
CODE is
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
oRestore.Database = "TempBakUp"
oRestore.Files = Server.MapPath("tempbakupfull.zip,tempbakupdiff.zip")
oRestore.FileNumber = 1
oRestore.ReplaceDatabase = True
oRestore.LastRestore = False
oRestore.SQLRestore(srv)
oRestore.FileNumber = 2
oRestore.LastRestore = true
oRestore.SQLRestore(srv)
|
|
|
|
|
hi all,
i want to select all the jobcode from jobmaster
my query is
SELECT txtjcode,convert(smalldatetime,dtlastdate)as last ,getdate()as tday FROM jobmast
it will work fine
it select entries like
test 2006-01-25 00:00:00 2006-02-01 09:51:05.107
US001 2006-01-25 00:00:00 2006-02-01 09:51:05.107
US002 2006-02-10 00:00:00 2006-02-01 09:51:05.107
but when use the following querry,
SELECT txtjcode,convert(smalldatetime,dtlastdate)as last ,getdate()as tday FROM jobmast WHERE convert(smalldatetime,dtlastdate)>= getdate()
it will select only this data
US002 2006-02-10 00:00:00 2006-02-01 09:51:05.107
and didnt select
US001 2006-01-25 00:00:00 2006-02-01 09:51:05.107
actually my query select both
why happens this so?
any soln to this
"i want to select all data where last date >= today date"
plz help urgent
CrazySanker
All I ever wanted is what others have....
|
|
|
|
|
One solution is that u should only save DATE portion in the datetime field u can send it as
Format(dtPicker.Value,"mm-dd-yyyy")
then ur query will work fine.
Regards,
Javed
|
|
|
|
|
What type is dtlastdate and why are you converting it? Shouldn't it already be in a datatime (or smalldatetime) format already?
crazysanker wrote: WHERE convert(smalldatetime,dtlastdate)>= getdate()
It didn't select the row with dtlastdate as 2006-01-25 because that is before now. You asked to select rows with a dtlastdate after now.
>= Greater than or equal to: WHERE dtlastdate is greater than or equal to getdate()
The query is working correctly.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
I know you can view the diagram icon, highlight it and click Cut or Copy, but if I go to another database with the same exact schema, how do I "paste" that diagram?
Thanks!
|
|
|
|
|
Here's how:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320125
|
|
|
|
|
I am new in database, somebody has sent me a small database via mail its a file, how can i generate a database using it.
i just know that i need to create a database through Enterprize Manager but how to fill it using some file
please guide me step by step
|
|
|
|
|
It depends on what they've sent. If it is a backup file then just restore it. If it is an actual detached database then just attach it.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
|
In Enterprise Manager right-click the server, All Tasks, Attach Database... (or somesuch menu option - I don't have EM up right now so I can't check)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
date acessing pblm
SELECT txtjcode,dtlastdate,getdate() FROM jobmast
it will select
test 2006-01-25 00:00:00 2006-01-25 11:00:35.903
US001 2006-02-01 00:00:00 2006-01-25 11:00:35.903
when i query
SELECT txtjcode,dtlastdate,getdate() FROM jobmast WHERE intstatus=1 and dtlastdate <= getdate()
US001 2006-02-01 00:00:00 2006-01-25 11:00:35.903
that do not select txtjcode "test"
actually time difference is there. ineed to select all upto todays date independend of todays time
what i do
CrazySanker
All I ever wanted is what others have....
|
|
|
|
|
What is the value of intstatus for the test row? You don't select that, but you do filter on it.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
|
Hi.
I posted this on the C# forum, but probablly this is a better place.
I'm having a little trouble getting this thing to work.
I have two tables Houses and People
Every "Person" belongs to a House, but only one per house is the "OwnerOfHouse".
So I made a colum named "HouseOwner" in the "House" table, which has the PersonID. And every person has a "HouseID_FK" which tells them to which house they belog.
SO here is the thing. I'm working with ADO.NET 2.0, and using BindingSource. So I have a BindingSource that has my Houses in a nice DataGridView, and when I click on any of them, I have another DataGridView which displays the people that live there.
That works Fine.
Now, when I chose one of the inhabitants, and press a button, His ID is updated on the Houses table, and I have a reference that tells me he is the Owner. So far so good.
What I want to do is that when I choose a House in my first DataGridView, The Owner is selected automaticlly in the People DataGridView. So far I've tried everything I could thinf of, but it allways selects the first element (the first person on the list).
Thanks in advance.
|
|
|
|