|
Thanks a lot. Its Sql Server 2000.
|
|
|
|
|
Hi,
Sorry for my ignorance but could someone give me a hand with this SQL query?
I have two tables in an access database. The first hold a list of titles and the second table holds a list of items belonging to these titles.
I need an SQL statement that will retrieve the titles from table a, and in the same row give me the number of items belonging to that title.
EG:
Table A:
Title 1
Title 2
Title 3
etc
Table b:
item: belongsto:
booksworms Title1
bread Title2
Mice Title3
Cheese Title2
Doorwedges Title2
So my statement needs to return the data as follows, although it needs to be based on the records encountered in table a, rather than simply counting/grouping the records in table b.
Title: Count:
Title1 1
Title2 3
Title3 1
etc
Also if anyone can recommend some useful tutorials id be really appreciative.
Thanks!
|
|
|
|
|
You could use something like,
Select a.*, (select count(*) from tableB b Where b.belongsTo = a.title) as WHATEVER
from TableA as a
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Obviously you have simplified the problem, because there is no need for TableA at all in this scenario; all of the information is contained in TableB.
From TableB only:
SELECT belongsto, COUNT(belongsto)
FROM TableB
GROUP BY belongsto
Assuming it needs both tables due to fields we are not seeing:
SELECT a.Title, COUNT(b.belongsto)
FROM TableA a
INNER JOIN TableB b ON a.Title = b.belongsto
GROUP BY a.Title
MS Access might not like the formatting I have used, and may need some parenthesis around some of that (it would work in SQL Server just the way it is formatted).
I like this book:
Guru's Guide to Trasact SQL by Ken Henderson[^]
But it is SQL Server specific (Transact SQL).
|
|
|
|
|
(Scott,)
Thanks for the help. I didnt use the real field names when i posted becuase i wasnt at my development PC. However ive adapted the code and got it part working, as follows:
SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, <br />
FROM tbl_Subforums a<br />
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum<br />
GROUP BY a.forumTitle, a.lastPost, a.lastUser
However i need to include a count from a third table, i figured it should look something like:
SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, COUNT(c.threadTitle) AS threads<br />
FROM tbl_Subforums a<br />
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum<br />
INNER JOIN tbl_topics c ON b.subforum = c.subforum<br />
GROUP BY a.forumTitle, a.lastPost, a.lastUser
Unfortunately this doesnt work. Could you please take a look and let me know where i am going wrong. The fields above are all correctly named and the c = b relationships are also correct.
The error i get within access is regarding the Syntaxt, so it would appear my formatting is wrong.
Thanks for any help you can offer!
munklefish
|
|
|
|
|
That looks like it would work in SQL Server, but Access might require paranthesis like:
munklefish wrote: SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, COUNT(c.threadTitle) AS threads
FROM ((tbl_Subforums a
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum)
INNER JOIN tbl_topics c ON b.subforum = c.subforum)
GROUP BY a.forumTitle, a.lastPost, a.lastUser
Scott
|
|
|
|
|
Thanks Scott!
That did the trick!
Muchos gracias!
munklefish
|
|
|
|
|
Good day everyone,
Please help if you're interested in.
I've got an Access database containing fitness club member profile. I need to make a login session for my webpage. How can I validate user name and password with the ones in database?
I don't have Microsoft SQl Server, so can I do it in VS.Net 2005?
How should I start?
Thanks so much
eric
|
|
|
|
|
|
I am having difficulty setting up my listbox to point to a SQL table (technicians table). I created a dataset that contains the table, but am unable to find enough help sources to create the code to make it work. I have created a Windows form to update a database, which displays the data from a (workorders table). I would like to have one of the fields as a picklist of names to allow the user to change the existing name and re-assign it to another from the list. I know this is basic stuff, but I am new to VB.NET.
Thank you,
LWhite
|
|
|
|
|
How will you take values for an insert statement
To insert the table values some values from one table and some values from open xml this will carried in a Stored Procedure
Help Please
Thanx
-
|
|
|
|
|
I am developing asp.net1.1 application
I have hug number of records in DB( SQLServer ) & want to show in a datagrid
There are thousands of records which need to transfer to client & client can sort & search
What is the best way to handle such type of situation
I am also using Ajax , when I use Ajax to call a function it lock the page until the all data is transfer to client , that’s not good
Did I use xml file to send on client & from xml file show data to client
or if there is any good solution plz....
I will be really thankful
Irfan
|
|
|
|
|
|
Hi,
I have one dataset, that contain one table now how can i retrieve one column in that table can you send me all possible approachments
|
|
|
|
|
if u confirm only one table means
ds.dt[tablename].columnname
-
|
|
|
|
|
Do you really mean 'one column', or do you mean 'one value from a particular row'?
If you really mean 'one column', then:
DataColumn column = dataSet.Tables[string tableName].Columns[columnName]<br />
or<br />
DataColumn column = dataSet.Tables[int tableIndex].Columns[int columnIndex]
If, however, you want the value stored for a particular column in a particular row in a table, then you need to index both the row and the column to get it:
object value = dataSet.Tables[string tableName].Rows[int rowIndex][string columnName (,DataRowVersion rowVersion)]
for example (the first method returns the current value in the described row's value list):
public object GetValueInRow(<br />
DataSet ds, <br />
int tableIndex, <br />
int rowIndex, <br />
string columnName)<br />
{<br />
return GetValueInRow(ds, tableIndex, rowIndex, columnName, DataRowVersion.Current);<br />
}<br />
<br />
public object GetValueInRow(<br />
DataSet ds, <br />
int tableIndex, <br />
int rowIndex, <br />
string columnName, <br />
DataRowVersion rowVersion)<br />
{<br />
return ds.Tables[tableIndex].Rows[rowIndex][columnName, rowVersion]<br />
}
Good luck.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-- modified at 2:16 Monday 22nd May, 2006
|
|
|
|
|
Good day everyone,
Please help if you're interested in.
I need to make a login session for my webpage. How can I validate user name and password with the ones in database?
How should I start?
Thanks so much
eric
|
|
|
|
|
What type of database are you talking about?
|
|
|
|
|
eric_tran wrote: How should I start?
See this "How To" document on MSDN: How To Use Forms Authentication With SQL Server 2000[^]
"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)
My: Website | Blog
|
|
|
|
|
|
Hi,
I have written a very simple application using VB6 and ADO that connects to a MySQL database. I want to be able to distribute the program to somebody who will not install DCOM (on windows XP), however I understand that DCOM is one of the file dependencies for MDAC to function.
At the moment the program references the 'Microsoft ActiveX Data Objects 2.8' library.
Here is the code I am using to connect to the database :
<code>Set gCnn = New ADODB.Connection
gCnn.ConnectionTimeout = 10
gCnn.CommandTimeout = 400
gCnn.CursorLocation = adUseClient
gCnn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
"user=" & txtUser & _
";password=" & txtPassword & _
";database=" & txtDatabase & _
";server=" & txtServer & _
";option=" & (1 + 2 + 8 + 32 + 2048 + 16384)</code>
To be more specific when the program is run on the machine without DCOM a message box is displayed with the message "-2147467259 Unspecified error".
I am curious to know if there is any way to make ADO work without first installing DCOM, or alternatively is there another method that I could use instead?
Thanks in advance for any help,
Mark
|
|
|
|
|
Hello community.
I have a problem and cannot solve it:
How can I measure the query execution time from my C# application?
I need to get a number or a time class to my app. from my database server, but
not measure the time when I got the informations from db but the internal time in db server. So I will display something like "Execution time: 0.0015 ms" in Sybase Interacive SQL.
I'm using Adatptive Server Anywhere v8 and v9
thanx lot
|
|
|
|
|
I have three tables
1) AssignProjectUser(ProjectID,UserID)
2) Task(ID,ProjectID)
3) AssingnTaskUser(UserID,TaskID)
I will create Trigers that after inserting a assignProjectUser
I will select all the task who have the ProjectID inserted in AssignProjectUser and insert list of AssignTaskUser that have the userID same assignProjectUser and TaskID that i have selected
Exemple:
suppose that I have
TASK:
1 1
2 1
3 2
when I insert a assignProjectUser (1,15)
the number of task for this Project is 1 and 2
so the trigers will add
(1,15)
(2,15)
I hope that you understand me
and really need help as soon
thanks
DADAX
|
|
|
|
|
CREATE TRIGGER trg_AssignProjectUser ON [dbo].[AssignProjectUser]
FOR INSERT
AS
INSERT INTO AssignTaskUser (UserID,TaskID)
SELECT inserted.userid,task.id
FROM task INNER JOIN inserted on task.projectid = inserted.projectid
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
