|
Hey everyone! I have been scouring the internet for help on an SQL query i need to run and have been trying for a day or two now to no avail! Can any SQL heroes please help me???
My problem seems simple but may not be so! In this example i have two tables, with their respective columns beside in brackets!
1. SERVERS (ID, NAME, LOCATION, OS)
2. SERVERS_VM (PHYSICAL_ID, VIRTUAL_ID)
The columns ID, PHYSICAL_ID and VIRTUAL_ID are all primary keys!
Now consider that the SERVERS table contains lots of servers both physical and virtual each with their own unique primary key labelled ID. While the SERVERS_VM table contains a list of all physical servers that have virtual servers installed on them! So each PHYSICAL_ID in SERVERS_VM might have one or more VIRTUAL_ID's associated with it.
Below i will try to give an example;
SERVERS
-------------------------------------------
ID Name Location o/s
-------------------------------------------
1 Bob12 BobOffice Win Server
23 Ash14 AshOffice Virtual
7 Hank89 Hankoffice Win Server
14 Tom98 Tomoffice Virtual
8 Mark45 Markoffice Win Server
93 Bar123 Baroffice Virtual
SERVERS_VM
---------------------------
PHYSICAL_ID VIRTUAL_ID
---------------------------
7 23
8 14
7 93
Ok so, now not trying to confuse people, but if we see above, Ash14 tom98 and bar123 are virtual servers (os=virtual) located on physical servers (shown in servers_vm)
From SERVERS_VM we can see that server number 7 houses two virtual servers given by their VIRTUAL_ID which is the same as the ID i the SERVERS table! (23 & 93)
So heres is the big question? How can i run an SQL query to list all the physical servers that have virtual servers alone installed on them??
The results should be something like;
---------------------------------------------
ID NAME VIRTUAL_ID
---------------------------------------------
7 Hank89 23
7 Hank89 93
8 Mark45 14
the problem is the fact that there are two primary keys in the SERVERS_VM table, well even though the PHYSICAL_ID repeats in the SERVERS_VM table
this is what i have so far;
SELECT PHYSICAL_ID, VIRTUAL_ID FROM (SELECT ID, NAME FROM SERVERS WHERE OS like '%Virtual%') WHERE SERVERS.ID=SERVERS_VM.VIRTUAL_ID
i have gone to much more depth but dont want to post it up because it is really messy.
Can anyone please Help??????
|
|
|
|
|
I can give you the answer but it sounds like a homework kind of question. So, in my attempt to help you but not give you the answer, here is the magic hint, use Group By, Having and count(*). 
Farhan Noor Qureshi
|
|
|
|
|
Thanks Sir,
Actually im new in a Job and am learnin SQL on my own, so my knowledge of these different commands is limited. Thanks though, anymore hints would be great!
|
|
|
|
|
bazpaul wrote: So heres is the big question? How can i run an SQL query to list all the physical servers that have virtual servers alone installed on them??
select * from servers where id in(select physical_id from servers_vm)
But, the sql to answer your question is different from the results you wanted. To get this:
---------------------------------------------<br />
ID NAME VIRTUAL_ID<br />
---------------------------------------------<br />
7 Hank89 23<br />
7 Hank89 93<br />
8 Mark45 14
You would use something like the following sql:
select s.ID,s.name,p.virtual_id from servers s inner join servers_vm p on s.id = p.physical_id
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I want to use a Gridview for a standard CRUD app.
So far I have my delete event wired up but I have a problem with my stored proc. I receive this error everytime i run my debugger and click the delete event. I have a catch event to catch this error.
The SqlParameterCollection only accepts non-null SqlParameter type objects, not Parameter objects.
Here is the code for my deleterow event. Any help is appreciated.
protected void DeleteRow(object sender, GridViewDeleteEventArgs e)
{
try
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager.ConnectionStrings["PokerTraxConnectionString"].ToString();
connection.Open();
SqlCommand delCommand = new SqlCommand();
delCommand.Connection = connection;
delCommand.CommandText = "usp_Delete_Loan";
delCommand.CommandType = CommandType.StoredProcedure;
delCommand.Parameters.Add(new Parameter("LoanId"));
delCommand.ExecuteNonQuery();
SqlDataAdapter adapter = new SqlDataAdapter(delCommand);
DataSet ds = new DataSet();
GridView1.DataSource = ds;
adapter.Fill(ds);
Cache["Loan"] = ds;
BindPlayerNames();
connection.Close();
connection.Dispose();
delCommand.Dispose();
}
catch (Exception ex)
{
errorMessage.Text = ex.Message;
}
}
|
|
|
|
|
This could be that the parameter was not created completey. In code that I have used I set other values for parameter object.
Generic stored proc calling code:
Dim command As New System.Data.SqlClient.SqlCommand
command.Connection = m_sqlConnection
command.CommandText = strStoredProc
command.CommandType = CommandType.StoredProcedure
' declare the parameter object
Dim param As System.Data.SqlClient.SqlParameter
Dim myEnumerator As System.Collections.IEnumerator = _
parameterArray.GetEnumerator()
Dim pItem As ParameterItem
While myEnumerator.MoveNext()
pItem = CType(myEnumerator.Current, ParameterItem)
' Add a new parameter, get back a reference to it
param = command.Parameters.Add(pItem.Name, pItem.Type)
' set the parameter's direction and value
param.Direction = pItem.Direction
param.Value = pItem.Value
End While
Dim myDataAdapter As New SqlClient.SqlDataAdapter
command.CommandTimeout = iTimeOut
myDataAdapter.SelectCommand = command
myDataAdapter.Fill(ds, "ResultSet")
|
|
|
|
|
Perhaps I should have mentioned I wrote this in C#.net. Thanks for the help though I can kindve determine how this is supposed to work.
|
|
|
|
|
Dear sir,
i have created a form with 3 textbox controls and a button called transfer
I have a table named CustId with the following records:
CID BAL
1 30000
2 50000
Here when i enter any amount (i.e, 3000) in TextBox1 and
enter the CID number(2) in the TextBox3 and click on the transfer button
The record bearing CID as 2 should be updated (the balance in this case will be 50000-3000=47000)
similarly,
if i enter any amount (i.e, 2000) in TextBox2 and
enter the CID number(1) in the TextBox3 and click on the transfer button
The record bearing CID as 1 should be updated (the balance in this case will be 30000-2000=32000)
i have already written the coding for that . But when i try to execute it
it gives a error , that is
"Line 1 Incorrect sytax near = "
Pl see the code below
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace data1
{
///
/// Summary description for Form1.
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.TextBox textBox3;
private System.Windows.Forms.Button button1;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
SqlConnection cn;
SqlCommand dbcm,crcm;
string dbcmstr,crcmstr;
int i1,i2;
SqlTransaction tr;
private void button1_Click(object sender, System.EventArgs e)
{
try
{
cn = new SqlConnection("user id = sa;initial catalog=pubs");
dbcmstr = string.Format("update custdb set bal=bal-{0}where cid={1}",textBox3.Text,textBox1.Text);
crcmstr = string.Format("update custdb set bal=bal+{0}where cid={1}",textBox3.Text,textBox2.Text);
dbcm=new SqlCommand(dbcmstr,cn);
crcm=new SqlCommand(crcmstr,cn);
cn.Open();
tr=cn.BeginTransaction();
dbcm.Transaction=tr;
i1=dbcm.ExecuteNonQuery();
crcm.Transaction=tr;
i2=crcm.ExecuteNonQuery();
if(i1==1 && i2==1)
{
tr.Commit();
MessageBox.Show("sucsesfull");
}
else
{
tr.Rollback();
MessageBox.Show("Failure");
}
cn.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
}
}
}
pl help me out with this
Thank u very much
arun
|
|
|
|
|
Arun Hegde wrote: TextBox1
Arun Hegde wrote: TextBox3
You might want to give more meaningful names to your text boxes. Your code is not maintainable.
dbcmstr = string.Format("update custdb set bal=bal-{0}where cid={1}",textBox3.Text,textBox1.Text);
There is no space betweeen the injected value at {0} and where so the SQL statement will expand to:
update custdb set bal=bal-3000where cid=2
I strongly suggest you look into the subject of SQL Injection Attacks because your code is highly susceptable. SQL Injection Attacks and Some Tips on How to Prevent Them[^]
|
|
|
|
|
If somebody have the form or code pls.send me because I am new in this.
|
|
|
|
|
lopa12 wrote: have the form or code
Sorry, don't have any. Shouldn't be that difficult to do. Use some creativity 
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
Hello there, I'm currently creating an application that will work as a workout journal. One part of this will be reporting your exercices. I will have a list of avaliable exercices which the user can choose one from and fill in the necessary information.
Now, at first I thought it was easy with just two tables. One for each filled in exercice connected to a user and one for all avaliable exercices. However the problem is that while all exercices have a name they differ in other variables. Pushups for example are just repetitions, benchpress is repetition and weight, running is distance and length. Etc etc. How would I solve this in the best way?
Having one table with all avaliable exercices and variables and just having NULL when the field is supposed to be empty? Splitting them up to different tables might be difficult as I would like to add new exercices without having to remake the database if I happened to miss one combination.
Thank you in advance and hope you understood my rambling.
|
|
|
|
|
Christer Claesson wrote: Having one table with all avaliable exercices and variables and just having NULL when the field is supposed to be empty? Splitting them up to different tables might be difficult as I would like to add new exercices without having to remake the database if I happened to miss one combination.
Splitting the information into tables is probably the best way - one table detailing all the variables, one table containg the excercises and another table with the link between the two (one to many relationship).
Hope this helps
Chandra Ram
|
|
|
|
|
Samuel G wrote: trying to connect to mysql database on my hosting account godaddy
Do they allow you to connect remotely to the database?
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
No they don't,
but it is a test page that I have uploaded to my hosting server
Samuelg
-- modified at 2:22 Wednesday 22nd November, 2006
|
|
|
|
|
Samuel G wrote: it is a test page that I have uploaded to my hosting server
Might be an issue on their part. Have you tried contacting their support people?
|
|
|
|
|
I manage to get it right so godaddy does support mysql for asp.net
my connection string was wrong I'v used there IP Address not there server name
ooops my mistake!!
SamuelG
|
|
|
|
|
 The OLE DB provider "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\OLEDB\Microsoft.Jet.OL
EDB.4.0" has not been registered.
I've tried this also,
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\viswanatha_c\Desktop\Book1.xls',
NULL,
'Excel 5.0'
Error:
User does not have permission to perform this action.
srinivasintouch
|
|
|
|
|
srinivasintouch wrote: The OLE DB provider "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\OLEDB\Microsoft.Jet.OL
EDB.4.0" has not been registered.
Have you fixed this problem?
If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa
|
|
|
|
|
Hello everyone,
In my C# program I load an XML file to a dataset. It has elements called "page". Three of its nodes are year, month and day.. I want to write a query that whenever a user gives two different dates, the query should select the "page"s which's date fall between the given dates.
How can I achieve this. I tried a lot but while I am a rookie I couldn't find a solution. Also google doesn't help.
Thanks for your helps and best regards.
.:: Something is Wrong ::.
|
|
|
|
|
SELECT * FROM MyTable WHERE DateColumn > @startDate AND DateColumn <@endDate
If that is not the answer you are looking for, then perhaps you could explain which part you are stuck on.
|
|
|
|
|
Well, the problem is thatI haven't got any DateColumn.. There are three columns named year, month and date..
Anyway, I have solved it. I wrote a query like this :
<br />
dateQuery = "((year > '" + date.Year.ToString() + "') AND (year < '" + date2.Year.ToString() + "')) "<br />
+ "OR ((year = '" + date.Year.ToString() + "') AND (month > '" + date.Month.ToString() + "')) " <br />
+ "OR ((year = '" + date.Year.ToString() + "') AND (month = '" + date.Month.ToString() + "') AND (day > '" + date.Day.ToString() + "')) "<br />
+ "OR ((year = '" + date2.Year.ToString() + "') AND (month < '" + date2.Month.ToString() + "')) " <br />
+ "OR ((year = '" + date2.Year.ToString() + "') AND (month = '" + date2.Month.ToString() + "') AND (day < '" + date2.Day.ToString() + "')) ";<br />
It seems a bit confusing, but it do the job 
Thanks a lot for your helps..
.:: Something is Wrong ::.
|
|
|
|
|
I havent really gone thru your query. Why not just do something simpler, so that your query is more readable in the future or to someone who is seeing your code.
<br />
WHERE Convert(datetime,day + '/' + month + '/' + year,103) >= '"+ date.ToString() +"' AND <br />
Convert(datetime,day + '/' + month + '/' + year,103) < '"+ date2.ToString() +"' .......<br />
Just an example, you may have to rewrite the query to your need.
Tarakeshwar Reddy
MCP, CCIE Q(R&S)
Experience is like a comb that life gives you when you are bald - Navjot Singh Sidhu
|
|
|
|
|
That is a better solution - but still potentially suscepatable to a SQL Injection Attack. Consider the use of parameters rather than injecting values into the string.
|
|
|
|
|
Colin Angus Mackay wrote: Consider the use of parameters
And of course, read your article on SQL Injection Attacks
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
