|
Could someone help me construct an SQL query which will conditionally select records from multiple tables which share the same schema, without joining every compatible record onto the end of each (creating a total mess) like "select * from <table1>, <table2> where 1" does. Placing the name of the table as a field for each would be a bonus (Im using mySQL)
The database I have is quite unusual as it contains a lot of binary-serialised data which has been categorised into a lot of different tables (for instance one table for records beginning with A, B, C etc), for (I expect) high-performance and easier management. If there is any other tweak you can think of that'll allow better querying on a collection of similar tables, your input would be welcome.
|
|
|
|
|
Hello,
I've got a table called samples. This table has three fields called PicID1, PicID2 and PicID3 for simplicity. My second table is called Pics. Pics has an ID field (key) and a PicName field. The select query I'd like to write selects all records from the Samples table and the PicName value for each of the PicIDs (1,2 and 3):
[Samples]
ID|PicID1|PicID2|PicID3
1|345|664|733
[Pics]
ID|PicName
..
345|Himalaya
664|Sahara
733|New York
The result should return a recordset containing all records from the samples table and "instead of the numeric PicID*" the Pics.PicName.
Can this be done in one query? If so, how?
Thanks in advance,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
SELECT Samples.ID, P1.PicName AS PicName1, P2.PicName AS PicName2, P3.PicName AS PicName3
FROM
Samples
INNER JOIN
Pics P1 ON Samples.PicID1 = P1.ID
INNER JOIN
Pics P2 ON Samples.PicID2 = P2.ID
INNER JOIN
Pics P3 ON Samples.PicID3 = P3.ID
-- insert WHERE clause here Basically, all you have to do is to alias the repeated table name.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, that's exactly what I was looking for!
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
hi
use subqueries.
Select Samples.ID
,(Select Pics.PicName where ID=Samples.PicID1) as FirstPic
,(Select Pics.PicName where ID=Samples.PicID2) as SecondPic
,(Select Pics.PicName where ID=Samples.PicID3) as ThirdPic
but i prefere that you re-design the database and create a link table that holds the SampleId and the PicID
Table:Sample_Pics
SampleID | PicID
1 |1
1 |2
1 |3
2 |4
------------------
etc.
this will allow you to have a variable number of pics per sample not just 3.
|
|
|
|
|
I've been thinking about this, but if fact, there will never ever be a situation when there is more then three 
Thanks anyway,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Hello everybody ...
Well, I have a problem in something with SQL Server 2000 ...
I want to write a procedure in stored procedure in SQL Server 2000, this stored procedure should output a variable holding the number of records in the table.
ex
select count(*) from tbl
where tblAccount = 2828;
I want this procedure to output the number of records retrieved, and i want to get it from ASP page. Actually i used a command object in ASP to execute my procedure, and with a recordset.
rs.open cmd
but how can i get the output from this procedure???? and how can i write like this procedure in SQL Server 2000 ????
Best Regards
Sam
Sam2004
|
|
|
|
|
If you've used SELECT and opened a Recordset, the results will be in the Recordset object. The recordset has the concept of the current row - the Value property of the Field objects in the Recordset's Fields collection will contain the retrieved value of that field in the current row.
Straight after opening, if any rows were retrieved, the Recordset object's current row will be the first row returned. If no rows were retrieved, the EOF property will be False.
Since you haven't given the field a name in the result set, you will only be able to access it by position, i.e. as rs.Fields(0). To give it a name, use
SELECT COUNT(*) AS RowCount
FROM tbl
WHERE tblAccount = 2828; As for the procedure, the easiest way is to send the SQL explicitly to the server, by setting the command object's CommandText property. This isn't the best performing method, though; look up the documentation on stored procedures and parameters.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi
how to know the sapce used in the data file or log files ?
the sp_spaceused stored proc gets the space used by a database or object.But what i need is the space used by in each data or log file.
how to get this info ?
|
|
|
|
|
You can use your programming language to get size of your database and its log file.If its C# you can use FileInfo.Length and if it is C++ you can use GetFileSize().
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
thank you .. but seems that my question wasn't clear enough.
I can get the file size (OS file size) using sp_helpFile. But this is the total space allocated for the file..
What i need is to know how much actual space consumed by data or log within the file..
for example you can allocate a 50MB file while the data actually use 20MB. In this case i want to get 30MB.
|
|
|
|
|
USE database
EXEC sp_spaceused I can't find a way of specifying which database you want to look at, other than just selecting the database with USE.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hello,
DB Admin is not one of my stong points so I'm a bit stuck. I have a development database that has almost filled the disk. I want to reduce the file sizes so I can continue development (a process I left running over the weekend has increased the log file by 10Gb - It didn't work so I have to fix and rerun it and I only have 5Gb left)
Can anyone let me know how to reduce the file sizes back to something sensible (the operation I was running modified about 17% of the records and deleted 83% of the records it was working on). I tried Shrink Database but it reports that it has shrunk the database by a few hundred Mb but the files are physically the same size. 
Any help would be much appreciated.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
(This applies to SQL Server 2000)
If you have recovery model for the database set to Full, everything you do is permanently recorded in the transaction log until you back it up. Only then will SQL Server truncate the log to the backup point (you can disable this behaviour by specifying WITH NO_TRUNCATE).
Full recovery allows you to restore the database to any point in time, which SQL Server performs by undoing all the operations in the transaction log after that point (and any operations in transactions that hadn't committed before that point). If you don't want this capability, select the Simple recovery model. In this model, you can only restore a backup of the database - you can't rewind to a particular point in time.
A compromise candidate is Bulk-Logged mode. Here, any bulk operations (BULK INSERT, bcp, CREATE INDEX, SELECT INTO, WRITETEXT and UPDATETEXT) are only marked as having happened; the actual operations are not recorded. However, SQL Server does indicate which regions of the database have been affected by a bulk operation, and backs those up with a full database backup or a transaction log backup. If you aren't performing bulk operations, this option saves no space at all.
The default for Standard or Enterprise Edition is Full, while the default for Personal Edition or MSDE is Simple. Remember that the settings for any new databases are copied from the model database (since a new database simply is a copy of model, which is how SQL Server manages to create a new database in a few seconds).
For more horrible detail, my reference book is Inside SQL Server 2000 by Kalen Delaney (MS Press).
If you're just developing, rather than working on a production server, I suggest using the Simple model. If you deploy to your production server with a backup/restore model, remember to change the recovery model afterwards.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you for quite a comprehensive answer.
I did get my problem solved by other means. I detached the database, threw away the log file and reattached it. (Well, I was a bit more careful than that but that was essentially the result - which got me back more disk space that I had when I started)
Curiously, the process appears to be working now, and the log file isn't filling up quite so fast!
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
Dear all
I have some problems while connection oracle 10g
i have tryed oracle connect ,ole connction and ODBC conection and i could not connect
does any body know any information about this problem
Regards
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
Does anyone know how to join two datatables in ADO.NET (VB.NET)
i want to basicly do an outer join of two tables with the same primary keys
note: i can not connect to the database to do an sql command.
|
|
|
|
|
babydogman wrote:
Does anyone know how to join two datatables in ADO.NET (VB.NET)
See DataRelation class.
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
Is there any way (besides triggers) to have an auto updating column in SQL Server? I'm looking for something like the "timestamp" column type from MySQL that automatically updates to the current date/time whenever an update/insert is performed. Is there anything like this type in SQL Server? Thanks Muchly.
- Aaron
|
|
|
|
|
It doesn't update to the current date and time, but SQL Server 2000 does have the rowversion type which is automatically updating. IIRC, the value for a changed row is only guaranteed to be different from the old value, not necessarily greater than the old value.
If you want date/time behaviour, you might want to consider using a trigger:
CREATE TRIGGER tg_timestamp ON MyTable
FOR INSERT, UPDATE
AS
UPDATE MyTable
SET TimeStampField = GETUTCDATE()
WHERE PrimaryKey IN
( SELECT PrimaryKey FROM inserted ) inserted is a pseudo-table (constructed from the transaction log) containing the new data for all the rows that were inserted or modified (there's also a deleted pseudo-table for the old version of modified rows, for an UPDATE trigger). You'll want to ensure that recursive triggers are not enabled, or you'll just end up going recursive.
I suggest using the UTC date and time so you don't get Daylight Savings Time effects.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
If there's no date/time updating field I guess I'll go with a trigger. Much thanks for the reply and the example.
- Aaron
|
|
|
|
|
Hi,
I'm using a little code to insert data in a Database.
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd
mStrSql = "INSERT INTO ZAP_CATEGORIE (CAT_Description, INS_USER, INS_DATTME) VALUES (@Cat_Desc, 'cde', getdate())"
' A SqlCommand object is used to execute the SQL statement.
mCmd.CommandText = mStrSql
With mCmd
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
End With
Try
mCmd.ExecuteNonQuery()
Catch exec As Exception
Return exec.message()
End Try
The first time I call this method, no problem.
The second time I got the following error message in the Excpetion :
The variable name '@Cat_Desc' has already been declared. Variable names must be unique within a query batch or stored procedure."
Can someone tell me where I'm wrong.
Thx
Sybux
|
|
|
|
|
SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
YOU MISSTYPED @ WITH LETTER a it should be @CAT_DESC 
ForgetAboutIt
|
|
|
|
|
You're caching the command object between runs, right? The first time you run through, the line
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Descadds the parameter to the command. When you run it again, the parameter @Cat_Desc is already there, so you get the exception.
There are two ways you can go: separate out setting the value from creating the parameter, and only creating the parameter once, or creating a new command object every time. I'd actually go for the second option, unless you need to run this command inside a tight, performance-critical block of code. In the long run, using less memory tends to perform better than caching lots of objects.
To do this, simply change
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd<pre>to<pre>Dim mCmd As New System.Data.SqlClient.SqlCommand
Secondly, never catch System.Exception. You can end up catching all sorts of critical errors other than those coming from the SQL layer. If you want to catch those, catch SqlException. From a design standpoint, don't catch an exception if all you're going to do is return a return code. Instead, allow the exception to propogate to the caller, and catch it there. It's OK to catch an exception and throw a new one of a different type to simplify the caller's exception code, but only if you can add more meaning (for example, throwing a user-defined BadFormatException if a file operation raised an IOException when you expected more data when reading a file). If you do this, remember to set the InnerException property.
The CLR will propogate some critical exceptions (such as ThreadAbortException or, IIRC, StackOverflowException) even if you do catch them.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thx for your answer but I will a third method becaus my SQLCommand is issue from my Database class so I can't make a new each time.
Here is my solution :
<code>
Dim mParDesc As New SqlParameter("@CAT_Desc", SqlDbType.NVarChar, 250)
With mCmd
.Parameters.Add(mParDesc).Value = Me.aCAT_Desc
End With
mCmd.ExecuteNonQuery()
mCmd.Parameters.Remove(mParDesc)
</code>
I don't know what is ths impact on perf. but I don't need an answer in the last 0.0001 seconds
Regards
Sybux
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
