|
Hi,
I am in Urgent need of query. I have 4 tables:
MeterSettings
MadeByID ConsumerTypeID AllocatedPowerID MeterID Date
1 2 3 22 5/11/2009
1 1 23
MeterMadeBy
ID Company TimeInterval
1 L&T 5
2 ABB 10
3 Rishab 15
ConsumerType
ID Type TimeInterval
1 SSI 5
2 MSI 20
3 LSI 30
PowerSanctioned
ID Power TimeInterval
1 5kw-10kw 5
2 10kw-15kw 10
3 15kw-20kw 20
4 25kw-30kw 25
now the requirement is I have to get MeterIds and corresponding Time Intervals in the following manner
if a Meter is having value for MadeByID in MeterSettings then we have to get TimeInterval of corresponding Meter.otherwise get the TimeInterval of
the meter based on value of ConsumerType. Otherwise go for PowerSanctioned.
I can't use Stored Procedures for this. The only option is query
|
|
|
|
|
Create a OUTER JOIN with all four answers and then use application logic to pick the correct one.
|
|
|
|
|
select MS.MadeByID, CT.TimeInterval, PS.TimeInterval
From MeterSettings AS MS INNER JOIN
ConsumerType AS CT
ON MS.ConsumerTypeID = CT.ID
INNER JOIN PowerSanctioned AS PS
ON MS.AllocatedPowerID = PS.ID
WHERE MeterID = enter meterID
This should be able to select
the MadeByID from the
MeterSettings table,
check and fetch the TimeInterval
with the same ID From ConsumerType table
and finally gets the TimeInterval
From the power sanction table
REad More on Joins
Jondo
|
|
|
|
|
select MS.MadeByID, CT.TimeInterval, PS.TimeInterval
From MeterSettings AS MS INNER JOIN
ConsumerType AS CT
ON MS.ConsumerTypeID = CT.ID
INNER JOIN PowerSanctioned AS PS
ON MS.AllocatedPowerID = PS.ID
WHERE MeterID = enter meterID
This should be able to select
the MadeByID from the
MeterSettings table,
check and fetch the TimeInterval
with the same ID From ConsumerType table
and finally gets the TimeInterval
From the power sanction table
|
|
|
|
|
|
How is this a database question? The answer is, name@domain, the same as all email addresses.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
hello sir,
i am new to sql and my problem is that how to find records having two selection criteria
for example
i have a table headname and month
Headname month fees
exam fee jan 100
tution fee jan 120
water fee feb 100
games fee march 50
now how can i select row having month=jan,feb and calculate the fees according to headname
thanks in advance 
|
|
|
|
|
Hi,
the WHERE clause supports logic operators AND, OR, NOT as well as parentheses, so you could do things like
SELECT ... FROM ... WHERE (field1='jan' OR field1='feb') AND NOT field2=12 ORDER BY ...
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
thanks for reply
i have done this by
select headname,frequency,sum(c1) from feeshead where months in('jan','feb','march')group by headname,frequency
|
|
|
|
|
Select SUM(fees) FROM TABLENAME
WHERE headname = 'jan' OR 'feb' OR 'march'
NOTE: The (fees) in the query
is the name of the column and
the "TABLENAME" should be replaced
with the table you want to perform
de query on.
NOTE ALSO: that SQL SERver is not case
sensitive
Jondo
|
|
|
|
|
hi
I have a Query, and I need to find the row number that the query return the answer
I do not have any counter field
how I do it ?
(i work with sql server 2005)
thank's in advance
|
|
|
|
|
Does COUNT function helps you?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Do you really need the row number or do you really mean the primary key ? Typically, you don't reference relational data by a "row number", each table should have a primary key defined which uniquely identifies the row.
Maybe I just don't understand the problem. 
|
|
|
|
|
hello all
,i am using the function Dataset.Merge(table, false, MissingSchemaAction.Ignore), to merge larg amount of table ..about 40 tables,the problem appear when some tables have large number of recordes...hence the performance become sooooooo bad,it take about 6...7 m to complete merging for that table to that data set...so does anyone know how to enhance the performance for it ??
|
|
|
|
|
Hi if anyone could help I would appreciate it. I have a database with a
Movies Table, Casting Table, and Actor Table.
Movie Table has MovieID
Casting Table has MovieID , and ActorID
Actor Table has ActorID
This code deletes a row from the movies table(using a datagrid in vb.net), I need all the rows in the Casting table with the same MovieID to also be deleted.
Any help would be appreciated!!
Dim result As String
Dim i As Integer
i = MovieDataGridView.CurrentRow.Index
result = MsgBox("Are you sure you want to delete this movie?", MsgBoxStyle.OkCancel)
If result = vbOK Then
If MovieDataGridView.Rows.Count = 1 Then
MsgBox("You must leave one movie in database!!")
Else
Me.MovieDataGridView.Rows.Remove(Me.MovieDataGridV iew.SelectedRows(0))
End If
Else
End If
Me.Validate()
Me.MovieBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.MovieProjectDa taSet)
|
|
|
|
|
I got it to work like this, I added a new query called GetDatabyDcast to my casting datatable that says
DELETE FROM Casting WHERE MovieID =@MovieID
then I added that into the delete button. This probably isnt the best way to do it but it got the job done. If you know a better way, then let me know please. Thanks
Dim result As String
Dim i As Integer
i = MovieDataGridView.CurrentRow.Index
result = MsgBox("Are you sure you want to delete this movie?", MsgBoxStyle.OkCancel)
If result = vbOK Then
If MovieDataGridView.Rows.Count = 1 Then
MsgBox("You must leave one movie in database!!")
Else
Dim s As Integer
s = Me.MovieDataGridView.CurrentRow.Index
Dim a As String = Me.MovieDataGridView.Item(0, s).Value.ToString
a = a
Dim ta As New MovieProjectDataSetTableAdapters.CastingTableAdapter
Dim tbl As MovieProjectDataSet.CastingDataTable
tbl = ta.GetDataByDcast(a)
Form3.CastingTableAdapter.Fill(Me.MovieProjectDataSet.Casting)
Form3.Validate()
Form3.CastingDataGridView.EndEdit()
Form3.TableAdapterManager.UpdateAll(Me.MovieProjectDataSet)
Me.MovieDataGridView.Rows.Remove(Me.MovieDataGridView.SelectedRows(0))
End If
|
|
|
|
|
JustinRiggs wrote: If MovieDataGridView.Rows.Count = 1 Then
MsgBox("You must leave one movie in database!!")
Why would you force the user to have one record at least???
Alexei Rodriguez
|
|
|
|
|
You can use a stored procedure that takes the MNovieID as a parameter and then something like this:
DELETE FROM Movies WHERE MovieID = @MovieID
DLEETE FROM Casting WHERE MovieID = @MovieID
You can put this code inside a transaction
But the easiest way is to set a foreign key in the Casting table with DELETE CASCADE, and when a movie is deleted, it will delete all the records in casting corresponding to the movie deleted
Alexei Rodriguez
|
|
|
|
|
hi guys
can anybody plz tell me what is difference between sqlexpr_adv.exe and sqlexpr_toolkit.exe?
thanks .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
|
I am working on a customized Excel VBA function. I have developed a two dimensional array consisting of two fields. The first element is a Date field and the second field is an Integer field. The problem is that now I need to search through the array using the first field, and when a match is found to return the second field as the result. The name of the array is arrArray. It is a static array with a set number of records. How should I go about searching the array?
As an example, let us assume that there are ten elements and that they are properly loaded like this:
Dim arrArray(10, 1)
arrArray(#1/1/2006#, 1)
arrArray(#1/2/2006#, 4)
How do I search through the first element field for a match, and when a match is found to return the second element?
|
|
|
|
|
Assuming you have filled the array with something like:
arrArray(1, 0) = "#1/1/2006#"
arrArray(1, 1) = 1
arrArray(2, 0) = "#1/2/2006#"
arrArray(2, 1) = 4
Then something like this should do it
Dim retVal
Dim match
Dim i
match = "#1/2/2006#"
For i = 1 To 10
If arrArray(i, 0) = match Then
retVal = arrArray(i, 1)
End If
Next i
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
Hello All,
Problem:
I've a table which has dynamic columns, I dnot know how many they are becasue they are created dynamically.
Now I've to insert some of columns to other table.
But I'm unable to find the way to fetch the columns by index.
I want to use like this,
<br />
insert into tbl(TableName.Columns[0].Name,TableName.Columns[2].Name,.....,TableName.Columns[n].Name)<br />
values (TableName.Columns[0].Value,TableName.Columns[1].Value,.....,TableName.Columns[n].Value)<br />
Plz Help
Thanks
Syed Shahid Hussain
|
|
|
|
|
The following query returning the following result:
SELECT count(*) FROM `table1`;
--------------------
Resilt 2000
But the following Query returning different result
SELECT
table_name,
table_rows AS rows
FROM information_schema.tables
WHERE table_schema = 'bakrol14' and table_name = `table1`
Order by table_name;
--------------------
Result 2143
What is the reason?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
