|
Hi ! I'm a beginner at transact sql and I have a question.
I have a table named person and a table named event.
person has columns personID, personName
event table has columns: eventID, eventName, date
My situation is that I want to know which persons that will go to which event so i have a table called
eventPerson
eventPerson has columns: eventID, personID
How do I construct a question in Transact SQL that returns a table over which persons that goes to wich event
The result would look someting like this
EventNo1 EventNo2 EventNo3
peter 1 0 1
lucy 1 0 1
john 0 1 1
So what I want is a matrix with boolean values. Columns is event. rows is person.
|
|
|
|
|
your query is something like this:
select persons.*,event.*,eventPerson.*<br />
from person,event,eventperson<br />
where person.id = eventperson.id and event.id = eventperson.id
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.
|
|
|
|
|
I think, what you're looking for is PIVOT operator. Have a look at Using PIVOT and UNPIVOT[^]
The need to optimize rises from a bad design
|
|
|
|
|
How can we do:
insert into table (name) values ('nick's')
in sql server.
I tried using :
insert into table (name) values ('nick\'s')
insert into table (name) values ("nick's")
but still could not find the solution. I need to execute the query not from stored procedure. Thanks.
|
|
|
|
|
If you are coding in c# this might help you:
insert into table (name) values (@"nick's")
|
|
|
|
|
do it like this
insert into table (name) values ('Nick''s')
I know it will Help
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
If you are doing this from code you should use named parameters, otherwise you will likely be vulnerable to SQL injection attacks.
|
|
|
|
|
As I've understood, it's not possible to have full-text search indexing on columns larger than 900 bytes. I know I can overcome this problem by adding large columns as non-key included columns to non-clustered indexes. But still I'm not able to use the powerful search capabilities of full-text search. I can only use queries like "select c from t where c like '%find%'". That's, I can not easily look for combination of words. Is there any known solution for searching large fields that can not be easily indexed in SQL Server? As an example, how has it been implemented to search multiple words in codeproject forums? Is it something simple like this?
SELECT * FROM Messages WHERE Title LIKE '%word1%' OR Title LIKE '%word2%'
Thanks in advance for any help.
|
|
|
|
|
Full-text index doesn't have a limit of 900 bytes on indexed columns. The limit is only for key index. So if you have a good primary key, you should not have any problems using full-text indexes.
The need to optimize rises from a bad design
|
|
|
|
|
What do you mean by key index Mika? Primary clustered index? Should I necessarily add a column as a non-key included column to a non-clustered index if I want to have full-text search on it?
Thanks
|
|
|
|
|
Not necessarily clustered index, but primary key or unique key of the table.
See KEY INDEX argument in CREATE FULLTEXT INDEX (Transact-SQL)[^]
The need to optimize rises from a bad design
|
|
|
|
|
Thank you. So there should be no problem. I don't know why somebody told me it's not possible to use it on fields larger than 900 bytes! Maybe I misunderstood the point.
_
modified on Sunday, October 5, 2008 4:47 PM
|
|
|
|
|
i need to know which is the best about from dataset and coding database connection by your self.i need brief description who is better on performance.
|
|
|
|
|
Peformance in database access should be more or less the same, because basically you will have the same queries in both scenarios.
But at client side, a designed dataset will produce lots of souce code, not all really needed. I think a manually created database access will produce far less code than the designed one.
The advantage of the dataset is that is easier to mantain, add and modify the queries and views.
Best regards,
Jaime.
|
|
|
|
|
Hi all,
I am migrating a C++ application to C#. One of the classes can read/write DBF (dBase) files.
The question is:
Does it worth to migrate this class?
What are all the ready-made .net alternatives?
Do these alternatives have some deployment requirements?
Thanks in advance.
Best regards,
Jaime.
|
|
|
|
|
Only thing that comes in mind is an ODBC-driver, which can be used with OdbcConnection Class. I think Microsoft may have free drivers against dBase.
The need to optimize rises from a bad design
|
|
|
|
|
Yeah, I know about the ODBC alternative, but:
* Is it always pre-installed in common client Windows OSs? (XP home/pro, Vista home/pro) ?
* If not, how easy or complex is to configure it when deploying an application that need to use DBF files?
Best regards,
Jaime.
|
|
|
|
|
Don't know if it's pre-installed. I would believe that not. I think that MDAC installs also dBase drivers, but I'm not sure.
The installation of a single driver is basically copying the dll files and then registering the dll's. After that a DSN can be created programmatically or using Control Panel.
The need to optimize rises from a bad design
|
|
|
|
|
Recently I installed MDAC to have a look to an old VB6 application. It causes me some troubles while installing.
So, I think maybe would be a good idea to use my own DBF class, so I won't have deployment problems...
Best regards,
Jaime.
|
|
|
|
|
Hi , i need all dates of the current month . m using sql server 2000 .
Thanx in Advance .
|
|
|
|
|
Thanx i got it . And the sol is :
declare @current varchar(100)
set @current =(select convert(varchar,getdate(),101))
declare @firstdate varchar(100)
declare @lastdate varchar(100)
set @firstdate=(SELECT convert( varchar,DATEADD(dd,-(DAY(DATEADD(mm,1,@current))-1),DATEADD(mm,0,@current)),101))
set @lastdate=(SELECT convert(varchar, DATEADD(dd, -DAY(DATEADD(m,1,@current)), DATEADD(m,1,@current)),101))
print @firstdate
print @lastdate
declare @i int
declare @k varchar(100)
select @i = datediff(dd,@firstdate,@lastdate)
print @i
declare @j int
set @j=0
while(@j<@i)
begin
select @k = dateadd( dd,1,@firstdate)
print @k
set @firstdate=@k
set @j =@j + 1
end
|
|
|
|
|
I have a table named test in SQL 2005 database. It has three columns (ourKey, ourData1, ourData2). Its first column (ourKey) is primary key column and it updates itself. It has one entry (1,45,56).
I would like to have another entry based on any other entry. I would like to insert entry which should produce (2,46,56)
I would like to read first entry from ourData1 then add one and rest of the table should be same.
Any help will be very much appreciated.
in previous thread
Re: delete duplicate rows by Ashfield
#table is used. What is #table?
Agha Khan
modified on Friday, October 3, 2008 11:42 PM
|
|
|
|
|
Do yo mean something like:
INSERT INTO test (ourData1, ourData2) SELECT MAX(ourData1) + 1, MAX(ourData2) FROM test;
Tables prefixed with # are local temporary tables.
The need to optimize rises from a bad design
|
|
|
|
|
Thank you. but I was looking to copy of Nth row not the Max of colum. i.e. I want to copy 3rd row or 2nd row.
My table has three entries.
1,45,30
2,3,40
3,9,80
the 4th should be if 2 row (2,3,40) is selected
4,3,40
if 1st row (1,45,30) is selected
4,46,30
This means answer depends on selection.
But your answer helped me to understand.
this works
INSERT INTO test (ourData1, ourData2) SELECT (select ourData1 from test where ourkey = 3 ) + 1 , (select ourData2 from test where ourkey = 3 );
but is there short syntex? 
I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1
insert into test select * from #table
But I cant not insert identity column, it is my primey key and it is default. I not have to supply it.
Otherwise it looks it works.
Kind regards
Agha
modified on Saturday, October 4, 2008 11:05 AM
|
|
|
|
|
If I understood you correctly, you could modify the insert statement to following:
INSERT INTO test (ourData1, ourData2)
SELECT ourData1 + 1 , ourData2
FROM test
WHERE ourkey = 3 ;
The need to optimize rises from a bad design
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
