|
Hi Colin
Michael Potter has just posted a solution which uses GROUP BY instead of a JOIN. Would you possibly take a look at it and tell us your valuable opinion about it? I guess it should do better in terms of performance due to not using a JOIN.
|
|
|
|
|
I think you can also handle this with an aggregate:
SELECT
title
FROM
table
WHERE
(keyId=1 AND value=10) OR (keyId=2 AND value=11)
GROUP BY title
HAVING COUNT(title) = 2
|
|
|
|
|
Thank you very much for your reply. You've used GROUP BY in a way I could never imagine!
Let's see what Colin Angus Mackay's opinion is about.
|
|
|
|
|
There is often more than one way to achieve the same result. The way I see it is that go with the one that is easier to understand unless it is causing a performance problem. Remember, in the future the requirements might change and someone is going to have to maintain it.
I don't know if that was the answer you were looking for - I was totally non-commital on which solution you should use.
|
|
|
|
|
Colin Angus Mackay wrote: go with the one that is easier to understand unless it is causing a performance problem
I think the same here!
Colin Angus Mackay wrote: I don't know if that was the answer you were looking for
Kind of! It's obvious that using GROUP BY is an easier-to-understand solution, but I just wanted you to say whether it is any better in performance than using a JOIN. You may say that is not necessarily better because GROUP BY is actually using JOINs internally.
|
|
|
|
|
Hi there .
I have cursor that navigate on sysusers table , and get the name of user for drop it ,via exec sp_dropuser @name . but when i want to execute this function give me a error .
Error : Select statements included within a function can not return data to a client
I don't know how can i solve this problem .
My Function :
<br />
CREATE FUNCTION fDropAllSystemUsers()<br />
<br />
returns int<br />
as<br />
begin<br />
declare @result int<br />
declare @name char(20)<br />
<br />
<br />
declare UserNavigator cursor for <br />
select [name] from sysusers where [name] <> 'dbo' and islogin = 1<br />
<br />
open UserNavigator <br />
<br />
if (@@FETCH_STATUS <> 0)<br />
return 0<br />
<br />
while(@@FETCH_STATUS = 0) <br />
begin<br />
exec sp_dropuser @name <br />
fetch next from UserNavigator into @name <br />
end<br />
fetch next from UserNavigator<br />
<br />
close UserNavigator<br />
deallocate UserNavigator<br />
<br />
set @result = 0;<br />
return @result<br />
end <br />
Please help me .
DMASTER
|
|
|
|
|
you are checking for the @@FETCH_STATUS without fetching the record from the cursor...
I suppose the return statement in the middle and the fetch statement at the last are not neccessary...
check with this...
CREATE FUNCTION fDropAllSystemUsers()<br />
<br />
returns int<br />
as<br />
begin<br />
declare @result int<br />
declare @name char(20)<br />
<br />
declare UserNavigator cursor for <br />
select [name] from sysusers where [name] <> 'dbo' and islogin = 1<br />
<br />
open UserNavigator <br />
fetch next from UserNavigator into @name <br />
<br />
while(@@FETCH_STATUS = 0) <br />
begin<br />
exec sp_dropuser @name <br />
fetch next from UserNavigator into @name <br />
end<br />
<br />
close UserNavigator<br />
deallocate UserNavigator<br />
<br />
set @result = 0<br />
return @result<br />
end
Regards John
|
|
|
|
|
I have no clue why you want to delete all the user account and I don't think that you can delete the Builtin Administrator account...
Regards John
|
|
|
|
|
hi everyone,
i want to connect my application to MYSQL database which is located on web.Can anyone provide some code or link which guide me to connect to MYSQL database?
one more thing whether it is essential to install MYSQL server on my system ?
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
My Company
|
|
|
|
|
Hi there
I want to drop 'builtin\administrators' from sql server with a SQL query or stored procedure.
I logged in with sa user and i don't know how to drop administrators account.
In fact i want to remove windows authentication security login.
How can i do that?
Thanx.
|
|
|
|
|
I'm pretty sure the authentication modes for SQL 2005 are 'Windows Authentication only' and 'Mixed mode' (both). There should only be a couple of built-in accounts - can you just remove their permissions in the management console?
|
|
|
|
|
Yes .
I can remove the login 'BUILTIN\Administrator' or other built-in accounts with management console but i can't remove it with SQL query .
My problem is remove with sql query .
How can I do this ?
DMASTER
|
|
|
|
|
sp_revokelogin 'BUILTIN\Administrator'
|
|
|
|
|
hi all! im not sure but i guess i should have posted my question in this forum since it concerns sql...
my question is regarding querying the database which results in multiple records which should be mapped to struct. can someone please show me how to do this?
i have written vc++ code which queries the database but have only done this with 1 result which contains a few number of fiels which i have assigned to individual variables
now what i would like is to create an array of structs based on multiple records which was selected based on the query.
thanks for any help!
newbie
|
|
|
|
|
You do this in C++ code. Create a vector of structs, and for each row, read the values, build an instance of the struct, and push it into the vector.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I have this stored procedure on which I would like to apply if statements at the where clause. But it seems that sql is finding errors associated with the if statements. Can anyone please guide me on what is wrong. Maybe it is not possible to use if statemnts in where clauses?
@category int,@brand int ---> parameters
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select equipment_id,equipment_modelNumber,equipment_description,category,brand,link,equipment_small_image,equipment_largr_image
FROM Gen_Equipment
INNER JOIN Gen_Equipment_Categories
INNER JOIN Gen_Equipment_Brand
INNER JOIN Gen_Equipment_Link
ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand)
ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id)
WHERE
if (NOT(@category=0) AND @brand=0) THEN
BEGIN
Gen_Equipment.equipment_category=@category order by Gen_Equipment.equipment_category
END
ELSE IF (@category=0 AND NOT(@brand=0)) THEN
BEGIN
equipment_brand=@brand
END
END
Thank you very much for your help.
Chris
|
|
|
|
|
ChrisFarrugia wrote: I have this stored procedure on which I would like to apply if statements at the where clause. But it seems that sql is finding errors associated with the if statements. Can anyone please guide me on what is wrong. Maybe it is not possible to use if statemnts in where clauses?
You can't use IF statements in WHERE clauses. IF statements stand alone and do not form part of another statement (in this case the SELECT statement)
Your best bet is to put the IF statement around the SELECT
IF ((@category<>0) AND @brand=0)
BEGIN
SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand,
link, equipment_small_image, equipment_largr_image
FROM Gen_Equipment
INNER JOIN Gen_Equipment_Categories
INNER JOIN Gen_Equipment_Brand
INNER JOIN Gen_Equipment_Link
ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand)
ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id)
WHERE Gen_Equipment.equipment_category=@category
ORDER BY Gen_Equipment.equipment_category
END
ELSE
BEGIN
SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand,
link, equipment_small_image, equipment_largr_image
FROM Gen_Equipment
INNER JOIN Gen_Equipment_Categories
INNER JOIN Gen_Equipment_Brand
INNER JOIN Gen_Equipment_Link
ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand)
ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id)
WHERE equipment_brand=@brand
END
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
dear every one
in vb 6 i used listbox.itemdata property to recognize the selected item and its reference data into other tables to fill my controls when i click on one.
please help me that in vb .net how i can deal with this.
i mean that i want to click on a list item for example student name and the rest record should be filled in text boxes on the form.
help plz
|
|
|
|
|
post the quetion in the appropriate forum...
Regards John
|
|
|
|
|
hi,
create following class in your application
Public Class ListItemData<br />
<br />
Dim mstrText As String<br />
Dim mintIndex As Integer<br />
<br />
Public Sub New(ByVal Text As String, ByVal Index As Integer)<br />
mstrText = Text<br />
mintIndex = Index<br />
End Sub<br />
<br />
Public ReadOnly Property Index() As Integer<br />
Get<br />
Return mintIndex<br />
End Get<br />
End Property<br />
<br />
Public Overrides Function ToString() As String<br />
Return mstrText<br />
End Function<br />
<br />
End Class<br />
<br />
now use following code to fill listbox in your app to use above class
str = "select f_id,f_name from facultylist"<br />
result = mdGlobalInstance.GetResultFromQuery(str)<br />
'here result is dataset<br />
For i = 0 To result.Tables(0).Rows.Count - 1<br />
lstFaculty.Items.Add(New ListItemData(result.Tables(0).Rows(i).Item(1), result.Tables(0).Rows(i).Item(0)))<br />
Next
now get selected item of list box as following
dim id as integer<br />
id=CType(LstFaculty.SelectedItem, ListItemData).Index
hope this helps
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
My Company
|
|
|
|
|
Dear every one...
Hope you all will be fine and read this in good of your health.
I have a problem that what is the best way to save, edit, insert, update etc. my record using a complex form.
PICTURE IS THIS;
I have a form Students.VB
I have many controls on the form as under
Listboxes
Textboxes
Checkboxes
buttons
now i want to know following things....
1. How i will fill my listbox with Student Names using data reader object (Database School is in MS ACCESS and table Student with fields ID,NAME,AGE)
2. how i will fill my comboboxes with CITIES, STATES, COUNTRIES etc.
3. what is the alternate of listbox.itemdata property in vb.net
etc. etc. etc..
please give me a detailed help that can clear my concept.
|
|
|
|
|
in all cases, the answer is the datasource property of hte controls, which will take a datatable, a dataview, an array, you name it
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
dear friend.
thanks for information.
its ok in all senses i have build. then tell me that if i have a combo box filled with Brand names and behind the scene i am saving the Brand ID for each brand name.
in vb 6, i used dataitem property of combobox to save a brandid with every brand name,
now what i should do with the same contol
thanks
|
|
|
|
|
Use the DisplayProperty and DataProperty properties ( from memory ) to set a displayed and a hidden value. This will not store them in the DB, but it will load them from the DB for you to use with the control.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
You may also set your combobox datasource to datable... and set its displaymember and valuemember....
ComboBox1.Datasource = Dataset.Table("DataTable Name")
ComboBox1.ValueMember = "Field Name" -----> Hidden Value
ComboBox1.DisplayMember = "Field Name" -----> Visible Value
|
|
|
|