making two sums in one query

Hi

Im new in this forum so don't if this is the right place to post these kind of questions..

Im using vb2005 and have made a program that deals with customers and payments.

I have a table called acount. Roughly it contains these columns

Number, amount, type..

type can be a payment or a charge

I would like to have a query that gives me this result:

Number, sum(payments), sum(charges) (grouped by number)

I think it is some type of merging these to querys

select number, sum(amount) where type = 0 group by number

and

select number, sum(amount) where type <> 0 group by number

Im using it to calculate a customers acount to see if they have paid to much or to little.

Any ideas?

Something like this

select number,
sum(case when type=0 then amount else 0 end) as payments,
sum(case when type<>0 then amount else 0 end) as charges
from mytable
group by number

|||

i just tried that, but get some kind of error from vbs error handling.. Its danish so the number is called nr and acount called konto. this is what i typped:

select nr,sum(case when type=0 then amount else 0 end) as pay, sum(case when type<>0 then amount else 0 end) as charges from konto group by nr

Can it have something to do with that ive tried it on an access database?

The program should be able to use both access and sql server..

|||Access doesn't support CASE expression so you cannot use the same SELECT statement if you want to run against Access & SQL Server. You have to use IIF in Access instead of CASE. Otherwise, the rest of the SELECT statement syntax should be fine. Alternatively, you can create a view in SQL Server and Access that contains the database specific query and query the view from the client side.|||

?v..

I cant get the iif sentence to work either.. Is this the query?

select nr,sum(iif when type=0 then amount else 0 end) as pay, sum(iif when type<>0 then amount else 0 end) as charges from konto group by nr

Am i missing something in the query?

|||

Please check the Access documentation for syntax on how to use IIF. It should be:

select nr,sum(iif(type=0, amount, 0)) as pay, sum(iif(type<>0, amount, 0)) as charges from konto group by nr

Also, this newsgroup is for SQL Server questions so please post only TSQL related questions or unless you are using SQL Server in some form or another. You need to post Access questions in the Access newsgroups to get better response.

Making SQL Keywords All Caps

Is there a way to make keywords such as SELECT, FROM, WHERE, HAVING, AND, ON etc. always appear in all caps when typing queries n the SQL Query Editor?

I have looked in the Options under Fonts and Colors but I dont see a way to make these keywords all caps. Any advice is appreciated.

Regards,

Randy J

Hi Randy,

holding the Shift Key during the typing of the keywords ? :-) Just kidding :-) I don′t think that there is an option for putting them all in caps. External tools can do this (like also providing Intellisense for the database), Query Editor AFAIK not.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for the reply. I now know about that odd key on the keyboard :)|||

Hi Randy,

just as a followup, PromptSQL does contain automatic uppercasing and other cool features, perhaps this is capable for you (and its not expensive)

http://promptsql.com/

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

sql

Making query in SQL server 2005 - What is the best way?

Hi i am making a web

application using SQL Server 2005. i am trying to make all my query

using stored procedure in database and just passing the parameter to

the database.

I am having some complex task that I can't do in

one query. So what is the best way to make the best performance? Using

complex stored procedure or using more code instead?

Thanks a lot for your help

It sounds like perhaps a temporary table or table variable would be useful. Are you needing to store the results of your 1st query and then process them in a 2nd query (i.e., get a resultset and, for each row, do a calculation/etc)? If so then the temp table solution is available. You can do it all in a single stored procedure.

/****************************************************

Check out our Sql Server 2005 and 2000 tutorials

****************************************************/

Making a T-SQL Query

I have a table like below(bolds are field names)

R_IdNameQ1Q2Q3Q4Q5

M001Mikeabcde

J001 Johnabcde

P001 Peterabcde

I want results based on above table as below. The columns (Q1 to Q5) are put as Question_id for each user like M001 and Question (a, b ,c,d,e ) as column Question exactly as below.

Could anyone help me please writing the script to achieve below from above table.

Result

R_idNameQuestion_idQuestion

M001MikeQ1a

M001MikeQ2b

M001MikeQ3c

M001MikeQ4d

M001MikeQ5e

J001JohnQ1a

J001JohnQ2b

J001JohnQ3c

J001JohnQ4d

J001JohnQ5e

........ ....... ....... ....

Thanks all

If you use sql server 2005,

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select

R_Id

,Name

,Question_id

,Question

From

#Data

UNPIVOT

(

Question For Question_idin

([Q1],[Q2],[Q3],[Q4],[Q5])

) UPVT

|||

Assuming you are using SQL 2005, you need to use the UNPIVOT operator.

The code will be something like this:

Code Snippet

DECLARE @.MyTable table
( R_Id varchar(10),
Name varchar(20),
Q1 char(1),
Q2 char(1),
Q3 char(1),
Q4 char(1),
Q5 char(1)
)

INSERT INTO @.MyTable VALUES ( 'M001', 'Mike', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'J001', 'John', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'P001', 'Peter', 'a', 'b', 'c', 'd', 'e' )

SELECT
R_ID,
Name,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

R_ID Name Question Response
- -- --
M001 Mike Q1 a
M001 Mike Q2 b
M001 Mike Q3 c
M001 Mike Q4 d
M001 Mike Q5 e
J001 John Q1 a
J001 John Q2 b
J001 John Q3 c
J001 John Q4 d
J001 John Q5 e
P001 Peter Q1 a
P001 Peter Q2 b
P001 Peter Q3 c
P001 Peter Q4 d
P001 Peter Q5 e

|||

If you use sql server 2000

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select R_Id,Name,'Q1' Question_id,[Q1] Question From #Data

Union All

Select R_Id,Name,'Q2' Question_id,[Q2] Question From #Data

Union All

Select R_Id,Name,'Q3' Question_id,[Q3] Question From #Data

Union All

Select R_Id,Name,'Q4' Question_id,[Q4] Question From #Data

Union All

Select R_Id,Name,'Q5' Question_id,[Q5] Question From #Data

|||

Arnie, Missed column...

SELECT
R_ID,
Name,
Response,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

Making a Table from a Query

I have a Query named qryQuery1 from which I would like to make a Table named tblTable1. I need to do this within a VBA Microsoft Access module.
Can anyone help me with the proper code?
Thank you for any help.
JimCreate table x as
Select columns from table2|||r123456,

Thank you for your response. Unfortunately, I am relatively new to this and do not understand what you mean by "Create Table x as select columns from Table 2". How do these relate to the qryQuery1 and Table1 that I am working with? Would it be too much trouble to ask for the actual code required?

Thank you again for your help.

Jim|||Dim str1 As String
st1 = "Select columns from table 2"
... "Create table x as " & str1|||R123456,

Thanks much. I will try working with your suggestion.

Jim|||USE Northwind
GO

--Uh No

CREATE TABLE myTable99 AS SELECT * FROM Orders
GO

--Uh Yes

SELECT * INTO myTable99 FROM Orders
Go

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Brett,

Thanks much for the suggestion. Actually I am trying to incorporate this into Microsoft Access VBA code and am coming to the conclusion that I am in the wrong forum. True?

Thanks again for your help.

Jim|||So you're not talking to SQL Server?

Try here

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||tsk, tsk, brett, sending someone elsewhere is definitely not nice when this site has a perfectly good Access forum (http://www.dbforums.com/f84/) itself|||I've never scrolled down that far...I just thought they was dissing Access...|||Thanks again Brett,

I guess I don't know enough to even know if I am "talking to SQL Server". I am just learning VBA for Access and that is where I am writing all my code. I have written a couple of SQL lines like:

SQL = "SELECT strTable1.Field1, strTable2.Field2 . . ."

followed by a line like:

Set qdef = CurrentDb.CreateQueryDef("qryTest", SQL)

But when I get what looks like "pure" SQL", I don't know what to do with it.

Thanks again.

Jim|||Yeah, it looks like your building Query objects on the fly in Access

If you set your variable to my syntax it will create the query that will create a table when it's executed...

But why do that?

Why not create the table ahead of time and just do inserts?|||Brett,

I believe that I need to create them on the fly because my two source tables are User-selected from a collection of existing tables. In addition, new tables may be added to these source tables.

I also let the User select a name for the table to be created that can then be stored along with other previously created tables. The created tables can than be retrieved as different scenarios.

Jim|||Originally posted by jpgalasso
Brett,

I believe that I need to create them on the fly because my two source tables are User-selected from a collection of existing tables. In addition, new tables may be added to these source tables.

I also let the User select a name for the table to be created that can then be stored along with other previously created tables. The created tables can than be retrieved as different scenarios.

Jim
If am not mistaken it is possible to run Make-table query in MS Access:

SELECT oldone.id, oldone.code INTO newone
FROM oldone;|||Originally posted by snail
If am not mistaken it is possible to run Make-table query in MS Access:

SELECT oldone.id, oldone.code INTO newone
FROM oldone;

I seem to need some kind of VBA "Create" or "Execute" statement following the above "SELECT" statement. Do you have any idea what the statement would look like? I tried the following but received an error message saying "Data type conversion error":

Set tbldef = CurrentDb.CreateTableDef("tblTable3", SQL)

Thanks again.

Jim|||Look at the vb help for command.execute

Execute, Requery, and Clear Methods Example (VB)

This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a Recordset, and the Clear method to clear the contents of the Errors collection. (The Errors collection is accessed via the Connection object of the ActiveConnection property of the Recordset.) The ExecuteCommand and PrintOutput procedures are required for this procedure to run.

Public Sub ExecuteX()

Dim strSQLChange As String
Dim strSQLRestore As String
Dim strCnn As String
Dim cnn1 As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim rstTitles As ADODB.Recordset
Dim errLoop As ADODB.Error

' Define two SQL statements to execute as command text.
strSQLChange = "UPDATE Titles SET Type = " & _
"'self_help' WHERE Type = 'psychology'"
strSQLRestore = "UPDATE Titles SET Type = " & _
"'psychology' WHERE Type = 'self_help'"

' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Create command object.
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = cnn1
cmdChange.CommandText = strSQLChange

' Open titles table.
Set rstTitles = New ADODB.Recordset
rstTitles.Open "titles", cnn1, , , adCmdTable

' Print report of original data.
Debug.Print _
"Data in Titles table before executing the query"
PrintOutput rstTitles

' Clear extraneous errors from the Errors collection.
cnn1.Errors.Clear

' Call the ExecuteCommand subroutine to execute cmdChange command.
ExecuteCommand cmdChange, rstTitles

' Print report of new data.
Debug.Print _
"Data in Titles table after executing the query"
PrintOutput rstTitles

' Use the Connection object's execute method to
' execute SQL statement to restore data. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cnn1.Execute strSQLRestore, , adExecuteNoRecords
On Error GoTo 0

' Retrieve the current data by requerying the recordset.
rstTitles.Requery

' Print report of restored data.
Debug.Print "Data after executing the query " & _
"to restore the original information"
PrintOutput rstTitles

rstTitles.Close
cnn1.Close

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If rstTitles.ActiveConnection.Errors.Count >= 0 Then
For Each errLoop In rstTitles.ActiveConnection.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume Next

End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _
rstTemp As ADODB.Recordset)

Dim errLoop As Error

' Run the specified Command object. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cmdTemp.Execute
On Error GoTo 0

' Retrieve the current data by requerying the recordset.
rstTemp.Requery

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If rstTemp.ActiveConnection.Errors.Count > 0 Then
For Each errLoop In Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

' Enumerate Recordset.
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & _
", " & rstTemp!Type
rstTemp.MoveNext
Loop

End Sub|||Originally posted by jpgalasso
I seem to need some kind of VBA "Create" or "Execute" statement following the above "SELECT" statement. Do you have any idea what the statement would look like? I tried the following but received an error message saying "Data type conversion error":

Set tbldef = CurrentDb.CreateTableDef("tblTable3", SQL)

Thanks again.

Jim

This works from Access and I guess will work with ADO (execute).

Public Sub DoSQL()
Dim SQL As String

SQL = "SELECT oldone.id, oldone.code INTO newone333 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Originally posted by Brett Kaiser
Look at the vb help for command.execute

Thank you very much Brett. This will be extremely helpful for both my existing and future projects.

Jim|||Originally posted by jpgalasso
Thank you very much Brett. This will be extremely helpful for both my existing and future projects.

Jim

For some reason the DoCmd.RunSQL statement, while OK for most SQL statements, does not work with a "SELECT" SQL - I guess it would just make too much sense.

Jim|||What do you mean?|||Originally posted by Brett Kaiser
What do you mean?

Sorry this was in response to "Snail" 's comment who suggested that I try DoCmd.RunSQL with the SQL "SELECT" statement. I do not believe that DoCmd.RunSQL works with the "SELECT" statement.

Jim|||Originally posted by jpgalasso
Sorry this was in response to "Snail" 's comment who suggested that I try DoCmd.RunSQL with the SQL "SELECT" statement. I do not believe that DoCmd.RunSQL works with the "SELECT" statement.

Jim

As you can notice there is 'select *** into from ***'. I tested it in Access modules/new and paste, run - it works.

Public Sub DoSQL()
Dim SQL As String
SQL = "SELECT oldone.id, oldone.code INTO newone333 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Snail,

You have my apologies and my thanks. I tried your code and it worked great. Since I am creating a Table from a Query, I do get a warning that I am about to add XXX records to a Table. Do you know how I might be able to turn off the warning message.

Thanks again.

Jim|||I think you want the docmd.RunSQL command.|||Originally posted by blindman
I think you want the docmd.RunSQL command.

blindman,

Thank you. Yes, as Snail also suggested, the docmd.RUNSQL command works fine. The only issue I have is that since I am creating a Table from a Query, I get a warning message that XXX records are about to be added to a Table. Do you know how I might turn off that warning?

Jim|||Originally posted by jpgalasso
blindman,

Thank you. Yes, as Snail also suggested, the docmd.RUNSQL command works fine. The only issue I have is that since I am creating a Table from a Query, I get a warning message that XXX records are about to be added to a Table. Do you know how I might turn off that warning?

Jim

Just add DoCmd.SetWarnings False:

Public Sub DoSQL()
Dim SQL As String
DoCmd.SetWarnings False
SQL = "SELECT oldone.id, oldone.code INTO newone555 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Originally posted by snail
Just add DoCmd.SetWarnings False:

Public Sub DoSQL()
Dim SQL As String
DoCmd.SetWarnings False
SQL = "SELECT oldone.id, oldone.code INTO newone555 FROM oldone; "
DoCmd.RunSQL SQL
End Sub

Snail,

Thanks so much. Very, very helpful!!

Jim|||Whoa! Remember to issue a Docmd.setwarnings True at the end of your procedure, otherwise you turn off all the warnings for Access. Change a form and it won't prompt you to save. Run an update query and it won't prompt you to verify. Bad.

As a matter of fact, I often put a macro in my Access applications that just set the setwarnings status to true, so that if my code crashes halfway through a procedure I can easily restore the setting.|||Originally posted by blindman
Whoa! Remember to issue a Docmd.setwarnings True at the end of your procedure, otherwise you turn off all the warnings for Access. Change a form and it won't prompt you to save. Run an update query and it won't prompt you to verify. Bad.

As a matter of fact, I often put a macro in my Access applications that just set the setwarnings status to true, so that if my code crashes halfway through a procedure I can easily restore the setting.

Thanks for the additional tip. Yes, I will reset to True right after I need the "False" setting.

Making a SQL Update Query run once

I have a datagrid in my file along with an Update Query.
My Update Query basically adds the numerical values in two columnstogether when the page is loaded. This means whenever the page isRefreshed the Update query is fired.
This is my Update Query (which is in an Stored Procedure):
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee
WHERE DaysOverdue >= 0
I have declared my query in the 'Page_Load' part of the coding, becauseI want the query to run automatically. Not manually by a button.
My main question is that how can I get the query to run only once a day, no matter how many times the page is loaded.
While I wouldn't recommend putting an update query in your page_Load,you can test for a postback before running the query by querying thePage.IsPostBack property: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemWebUIPageClassIsPostBackTopic.asp
|||The query will run whenever your page runs but you can force SQL Server to keep the compiled version of the query in the procedure cache by using the auto start option with sp_procoption system stored proc in the Master database. Run a search for sp_procotion in SQL Server BOL (books online). Hope this helps.|||

I would not have the query run by any ASP.NET page. What happens if no one visits the page in question some day? What happens when the site restarts?

Use DTS on the SQL Server and schedule a job to run once a day.

|||I think Doug is offering the best advice. It looks like you arecalculating late fees so you'd definitely want to run it every day.
If it doesn't matter whether or not it runs every day, and you simplywant it to run no more than once a day, I would add a datetime columnflagging the date on which it was last run. Then you could changeyour query like this:
DECLARE @.Today datetime
SELECT @.Today = GETDATE()
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee,
DateFlagged = @.Today
WHERE DaysOverdue >= 0 AND
DateFlagged < CONVERT(char(8),@.Today,112)
Another option is to calculate the TotalFee on the fly,something like this, rather than updating it every day. Thiswould be safer and not dependent upon a process running every day (andI am guessing that DaysOverDue is also being populated by a process...):
SELECT TotalFee = TotalFee + (ExtraFee * DaysOverDue)
FROM Rental

|||Thanks for all your input.
But I think the best solution to my problem would be using this query:
DECLARE @.Today datetime
SELECT @.Today = GETDATE()
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee,
DateFlagged = @.Today
WHERE DaysOverdue >= 0 AND
DateFlagged < CONVERT(char(8),@.Today,112)

But for some reason I cannot seem to get it to work. I have made anextra column in my table called 'DateFlagged'. I have tried to make itwork by creating this column data type as Date and char. But either wayit is not calculating the Total Fee.
I would appreciate any more info to make this query work.

|||DateFlagged should definitely be a datetime data type. Thiscolumn will initially contain a NULL if you do not set a default valuefor it, so any comparison against a value will return aFALSE. Therefore you'll need to check it for a NULL valueas well.
See if this helps:

WHERE DaysOverdue >= 0 AND
(DateFlagged < CONVERT(char(8),@.Today,112) OR DateFlagged IS NULL)

|||Yep! You solved my problem.
Thanks alot for all your help!

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!
http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>
|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>
|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.c..._duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.c..._duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>>I have an INTEGER column that I want to convert to a primary key. However,
>>some of the values currently in the dataset are not unique. What query
>>will delete any records with duplicate values of the intended index?
>> Many thanks!
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Make two query in too one

I have two SQL query that I would like to make in to one, if possible

Here is the first one: (Query4)
SELECT rooms.DESCRIPTIO, rooms.ID, bookings1.DATE
FROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms.ID
WHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));

And the second one:
SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMID
FROM rooms LEFT JOIN Query4 ON rooms.ID = Query4.ROOMID
WHERE (((Query4.ROOMID) Is Null));

Is it possible to make one query of this two?
With the same result.> Is it possible to make one query of this two?
> With the same result.

Please show an example of the result you want. The first query has columns
(descriptio, id, date) and the second one has (id, descriptio, roomid).
Possibly a UNION is what you are looking for but this requires that the
columns are of compatible data-types.

Is this a SQLServer question? The date format you used isn't valid in TSQL.

It would help make your requirement clearer if you could post DDL and sample
data.
http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--|||Please excuse me, I think I posted in a wrong group :)

The first query gives me a list of booked rooms and the second on a
list of available room.

Let's say I have 4 rooms:
1.Standard
2.Deluxe
3.Super deluxe
4.Suit

Query 1 give me the booked date from 4/3/2004 to 4/9/2004.
RoomIDDate
1 4/1/2004
1 4/2/2004
1 4/3/2004
1 4/4/2004
14/5/2004
24/4/2004
24/5/2004

Query 2 shows available room.
3Deluxe
4Suit

I have two tables.
1. Bookings1 with fields roomid and date booked
2. Rooms with two fields RoomID and description

Here is the first one: (Query4)
SELECT rooms.DESCRIPTIO, rooms. ROOMID, bookings1.DATE
FROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms. ROOMID
WHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));

And the second one:
SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMID
FROM rooms LEFT JOIN Query4 ON rooms. ROOMID = Query4.ROOMID
WHERE (((Query4.ROOMID) Is Null));

I really appreciate any help with this and sorry again for posting in
the wrong group;)

Bert

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<Ke6dnR318oYUuN_dRVn-hA@.giganews.com>...
> > Is it possible to make one query of this two?
> > With the same result.
> Please show an example of the result you want. The first query has columns
> (descriptio, id, date) and the second one has (id, descriptio, roomid).
> Possibly a UNION is what you are looking for but this requires that the
> columns are of compatible data-types.
> Is this a SQLServer question? The date format you used isn't valid in TSQL.
> It would help make your requirement clearer if you could post DDL and sample
> data.
> http://www.aspfaq.com/5006|||Bert Boye (sales@.a-o.biz) writes:
> I have two tables.
> 1. Bookings1 with fields roomid and date booked
> 2. Rooms with two fields RoomID and description
> Here is the first one: (Query4)
> SELECT rooms.DESCRIPTIO, rooms. ROOMID, bookings1.DATE
> FROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms. ROOMID
> WHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));
> And the second one:
> SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMID
> FROM rooms LEFT JOIN Query4 ON rooms. ROOMID = Query4.ROOMID
> WHERE (((Query4.ROOMID) Is Null));
> I really appreciate any help with this and sorry again for posting in
> the wrong group;)

A standard advice when we don't understand what is being asked for
(and I don't in this case) is that you should post:

o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired output from the sample data.

You are saying that you want to make one query out of two. However, I
have no idea on how you want to combine them.

Of course, if you are using Access, you should post to
comp.databases.ms-access instead. However, the advice about CREATE
TABLE etc still applies.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Make Table Query

Dear Sir/Madam,

I want to create a replica of another table with all the constraints except primary key.

when i tried the command

SELECT * INTO vch1 FROM Voucher

none of the constraints like DEFAULT, NOT NULL are defined in the new table.

pl. guide me.

with regards

wilfi

Hi Wilfi,

not sure if this is what you are looking for. In SQL 2005 if you right click the table in SSMS then select Script Table As, then CREATE To you can script the table to a new query window. This will provide all the SQL required to create the table as well as all constraints. You can then simply remove the primary key once the table is created.

Hope this helps,

Grant|||SELECT ... INTO does NOT transfer any table constraints -it only transfers data to the new table.|||Or you can just remove the primary key constraint from the DDL that you get from the Script Table As command. Generally speaking though, it is a best practice to build your objects from a file so you can maintain the table create in version control. Then you can just use that script to recreate the table.|||

hey why dont u just right click on the table in enterprise manager and select "COPY" option and then paste in in the Query Analyser window u will get all the constraints u can then change the table name and execute

RegarDs,

Jacx

|||

Thank u sir.

Yes I could do it that way. But i thought with the make table query there may be some option to carry the constraints.

thanks a lot.

with regards

wilfi

|||

Dear Jac,

Yes i could do that & i did that at last. First i thought there may be some option in the

make query to carry the constraints.

thank u.

with regards

wilfi

Make Table Query

Is there the equivalent of the MS Access "Make Table" query?
--
Thanks in advance,
StevenTry
CREATE TABLE
:-)
>--Original Message--
>Is there the equivalent of the MS Access "Make Table"
query?
>--
>Thanks in advance,
>Steven
>
>.
>||||
| Is there the equivalent of the MS Access "Make Table" query?
|
--
Hi Steven,
Action queries in MS Access can be replicated using SQL Server stored
procedures. You can copy and paste the SQL statement, modify it a bit so it
complies to ANSI and it should work.
Hope this helps,
--
Eric Cárdenas
SQL Server support

Make SQL Queries Case Sensitive

Hi,

I have an SQL Server 2000 database. There is a table for User Logins. I want the query to be executed as case sensitive. I mean if the database has a username like "USER1" and the user enters his username as "user1" then the login should fail.

So, Is there some way I can make SQL case sensitive? Please help

Regards

Vineed

This is a collation issue. Assuming your table name is UserTable and the field that holds username is UserName (varchar(50)), you can alter your table as follows:

Code Snippet

Alter Table UserTable Alter Column UserName (varchar(50) collate latin1_general_CS_AS)

If you want case insensitive, change CS to CI.

|||

Hi,

Thanks for your time. Really helped.

Thanks again

Regards

Vineed

make selection of your query result

i will try to explain what i want with an example

if have a query that returns

user code amount

user1 A 10

user1 B 100

user1 C 10

user2 B 50

user2 D 10

user3 A 10

user3 C 20

what i want in my report is the following as result

Sum of code A and C Sum of code B and D

user1 20 100

user2 60

user3 30

so can you make variables on the scope user that do a selection on the 'code'

in words like a variable that give me the sum of the amount where code in A and C in the scope user

Maybe this is not the best choice but it works.

We have:

Usuario Code Amount
--

Alejandro C 200
Erika A 50
Erika B 50
Karla D 100
Marcos A 100
Marcos B 50
Marcos C 25
Marcos D 70
Pedro A 100
Pedro C 70

and the code Returns

usuario F_Group S_Group
- - -
Alejandro 200 0
Erika 50 50
Karla 0 100
Marcos 125 120
Pedro 170 0

Code Snippet

--declare the parameters
declare @.F_group_1 varchar(2)
declare @.F_group_2 varchar(2)
declare @.S_group_1 varchar(2)
declare @.S_group_2 varchar(2)
--set value to parameters or define scoupe
select @.F_group_1='A',
@.F_group_2='C',
@.S_group_1='B',
@.S_group_2='D'
--sum both result
select qry.usuario, sum(qry.F_group) F_Group, sum(qry.S_group) S_Group
from (
-- sum the first group and in the 2nd we put 0
select usuario, sum(amount) F_group , 0 as S_group
from tabla
where code in (@.F_group_1, @.F_group_2)
group by usuario
union
-- sum the 2nd group and in the 1st we put 0
select usuario, 0 as F_group, sum(amount) S_group
from tabla
where code in (@.S_group_1, @.S_group_2)
group by usuario
) as qry
group by qry.usuario

Regards,

Marcos

|||

your solution is done at/in the query / datasource and i thought about that too

but is there a solution possible in report itself ?

like in businesobject you can make variables who are sum of an amount in a scope and with a where condition

on the report

|||

Krisje,

If I understand you correctly the following should work.

1. Use a table with 3 columns

2. Insert a group and set it to group on user

3. On the group row set column 1 expesression to fields!user

4. On the group row set column 2 to

=sum(iif(Fields!code.Value="A" or Fields!code.Value="C",Fields!amount.Value,0))

5. On the group row set column 2 to

=sum(iif(Fields!code.Value="A" or Fields!code.Value="C",0,Fields!amount.Value))

make a record display the numbers

I am looking for a query that counts the amount of pictures the user has in
the table, then select the 1st 20 pictures and create a record number. e.g.,
results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
autonumber from the count and ~/pix/1.jpg is the image location and mypic is
the caption. the table select script is below with all the colums to be
returned.
SELECT Pic, Cap
FROM Pics
WHERE UName = @.UName
there are only two columns in the table except for the key which is pId.
If anyone can help I would be grateful.Wow, this has come up a half dozen times day.
First off, how do you define the "first" 20 pictures?
SELECT TOP 20 Pic, Cap
FROM Pics
WHERE UName = @.UName
ORDER BY ...'...
Next, why can't these rankings be generated by the presentation layer (e.g.
ASP code or what have you), which has to loop through each row anyway,
instead of forcing the database to /also/ do this iteration?
http://www.aspfaq.com/2427
"Eamon" <eamon@.nuvola.co.uk> wrote in message
news:%23weQ$ppoFHA.3408@.tk2msftngp13.phx.gbl...
>I am looking for a query that counts the amount of pictures the user has in
> the table, then select the 1st 20 pictures and create a record number.
> e.g.,
> results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
> autonumber from the count and ~/pix/1.jpg is the image location and mypic
> is
> the caption. the table select script is below with all the colums to be
> returned.
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> there are only two columns in the table except for the key which is pId.
> If anyone can help I would be grateful.
>|||Eamon, what determines "first"?
If it's pId, then:
DECLARE @.T TABLE
(
rn INT NOT NULL IDENTITY,
Pic <datatype>,
Cap <datatype>
);
INSERT INTO @.T
SELECT Pic, Cap
FROM Pics
WHERE UName = @.UName
ORDER BY pId;
SELECT * FROM @.T;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Eamon" wrote:

> I am looking for a query that counts the amount of pictures the user has i
n
> the table, then select the 1st 20 pictures and create a record number. e.g
.,
> results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the
> autonumber from the count and ~/pix/1.jpg is the image location and mypic
is
> the caption. the table select script is below with all the colums to be
> returned.
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> there are only two columns in the table except for the key which is pId.
> If anyone can help I would be grateful.
>
>|||I meant, SELECT TOP 20 Pic, Cap...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Itzik Ben-Gan" wrote:
> Eamon, what determines "first"?
> If it's pId, then:
> DECLARE @.T TABLE
> (
> rn INT NOT NULL IDENTITY,
> Pic <datatype>,
> Cap <datatype>
> );
> INSERT INTO @.T
> SELECT Pic, Cap
> FROM Pics
> WHERE UName = @.UName
> ORDER BY pId;
> SELECT * FROM @.T;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Eamon" wrote:
>

Make a query to different database?

Hello:
I want make a query to different database, but that query
must be a transation, the problem is that I have common
data in one database and other database that utils the
common database, but I dont add some information in the
one data base without check first the common database, to
check existing data on creation and enforce relatioshipd
for replication and so on.
Best Regards.
Owen.Owen
I don't actually see a question here?
If you are getting at how do you tell the difference when
you have the same tables in different databases, you need
to give it a table name that includes the database. You
need to do this anyway if you are accessing multiple
databases even if they are on the same server. (You should
also include table owner.)
Select * from mydatabase.dbo.mytable
Hope this helps
John

Make a Query in VB and pass to CR

Hello all, I'm new to this crystal reports mumbo-jumbo and need some help. Im using VB 6 and the crystal that comes with free with Visual Studio 6. I have to create a report, and I would like to be able to do it from my VB app. I have the user select some criteria from my app. I would then like to construct an SQL query based on that input and output a report. The SQL isnt the problem. I've searched the web for help, but the most help I got was finding out how to install CR and add it to my toolbox.

Where do I go from here? I figure I need to at least layout the report in CR, cause that won't be changing, only the data that goes in the report.

I know I'm asking a lot in this post, but I haven't been able to find answers anywhere else. If one of you gentelmen (or ladies?) could point me in the right direction, I would appreciate it more than you could possibly know.

P.S. I'm using a FoxPro DB if that makes a diffrence. I'm connected with Jet driver.sql query : like only shown record with lastname = erick?
if it is u could use selection formula..

cRt.selectionformula = " lastname = '" & text1.text & "'"

regards|||Thats kind of what I'm looking for. What I have is a table that has some test information, test id, name, number of questions, etc. Then i have a test taker table that has all the ususal info, ie, name, ssn, ... and also the test they took. what I want to do is populate my report with only the test takers who scored above a certain number, who took the selected test. I know how to structure the query, but I don't know what to do with it once I have it. I saw there is a sqlquery property so i set it equal to my query, then showed my report, but nothing happend.|||cRt.selectionformula = " lastname = '" & text1.text & "'"

is "lastname" a field in the report?|||I tried something similar to what just got posted but nothing happend, my report didn't even print to the screen. I think that that might just add 1 record to the report, would I have to loop through an entire recordset to populate the report, or is there another way?|||There are 2 ways (that I know of) that you can do your query and get the results within VB, then pass only the data to Crystal Reports.

The first way is use ADO or RDO and pass the recordset to the report.

The second way is to use CDO (Crystal Data Objects) to pass the data to the report. With CDO, you populate an array with your data and stuff the array into a CDO Rowset (which is similar to an RDO resultset or an ADO Recordset).

Using either of these 2 methods, the only Crystal does is display the data.

For more info on CDO, see this link (http://www.dev-archive.com/forum/showthread.php?s=&threadid=281658&highlight=cdo)

Also, have a look at this (http://www.dev-archive.com/forum/showthread.php?t=298564&highlight=rdc). It gives code on 2 different ways of passing the data to the report. (rsData would be an ADO recordset since that's what I use, but you can replace that with an RDO resultset or a CDO rowset)

I personally prefer to use CDO and the second method from the second post (called RDC), since it seems to give you more control (ie, with RDC you can move fields and rename column headers, etc, that are in the report.)

One last thing, keep in mind that I use Crystal Reports 8.5, so if you have a different version, you may not be able to use the code I have posted word-for-word.

And check out Crystal's Support Website (http://support.businessobjects.com/search/advsearch.asp) and Crystal's Suport Forum (http://support.businessobjects.com/forums/default.asp)|||is "lastname" a field in the report?
yes it is..that is the field that user want to retrieve..
text1.text is the value that user choose..is this case
filed (lastname) with value = erick
cRt is ur crystal report control name

would I have to loop through an entire recordset to populate the report, or is there another way?
u don't have to loop entire record..if the value that user enter is in the table then the report will shown..the other way maybe what malleyo do..right now i'm using the method above..

regards

Make a Extra COUNT Coulmn on result

Hi Champs!
This is probably simple for you champs, but!
I hava tricky SQL query I cannot get to work.
I have a Query from two tables A and B:
I Make a SELECT
A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
This “TransactionNumber” is in table A and B
So a readout looks like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber
14:00 -- toy1 -- 23423 -- 1 -- 15889
14:00 -- toy2 -- 23488 -- 2 -- 15889
14:04 -- toy8 -- 11423 -- 5 -- 15890
14:10 -- toy2 -- 23488 -- 10 -- 15891
14:10 -- toy3 -- 23473 -- 1 -- 15891
14:10 -- toy6 -- 11342 -- 17 -- 15891
14:10 -- toy9 -- 23563 -- 2 -- 15891
14:12 -- toy0 -- 23423 -- 1 -- 15892
14:12 -- toy4 -- 23423 -- 3 -- 15892
So I need to make a one New Extra column after A.transactionNumber, that
counts the that similar numbers; like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
column
14:00 -- toy1 -- 23423 -- 1 -- 15889
-- 1 --
14:00 -- toy2 -- 23488 -- 2 -- 15889
-- 2 --
14:04 -- toy8 -- 11423 -- 5 -- 15890
-- 1 --
14:10 -- toy2 -- 23488 -- 10 -- 15891
-- 1 --
14:10 -- toy3 -- 23473 -- 1 -- 15891
-- 2 --14:10 -- toy6 -- 11342 -- 17
-- 15891 -- 3 --
14:10 -- toy9 -- 23563 -- 2 -- 15891
-- 4 --
14:12 -- toy0 -- 23423 -- 1 -- 15892
-- 1 --
14:12 -- toy4 -- 23423 -- 3 -- 15892
-- 2 --
I’ve tried with the a Count (*) , but cannot seem to get it to work.
Any help would be much appreciated.
/Many Thanks
Kurlan"Kutlan" <Kutlan@.discussions.microsoft.com> wrote in message
news:B7791534-DEBC-48CD-AD63-F6F167897B60@.microsoft.com...
> Hi Champs!
> This is probably simple for you champs, but!
> I hava tricky SQL query I cannot get to work.
> I have a Query from two tables A and B:
> I Make a SELECT
> A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
> This "TransactionNumber" is in table A and B
> So a readout looks like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> 14:10 -- toy6 -- 11342 -- 17 -- 15891
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
>
> So I need to make a one New Extra column after A.transactionNumber, that
> counts the that similar numbers; like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
> column
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> -- 1 --
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> -- 2 --
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> -- 1 --
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> -- 1 --
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> -- 2 --14:10 -- toy6 -- 11342 -- 17
> -- 15891 -- 3 --
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> -- 4 --
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> -- 1 --
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
> -- 2 --
>
> I've tried with the a Count (*) , but cannot seem to get it to work.
> Any help would be much appreciated.
>
> /Many Thanks
> Kurlan
>
Would a GROUP BY WITH ROLLUP work for you? Check it out in the BOL.
Your query would then look something like: (note untested).
SELECT A.time ,
B.name ,
B.number ,
B.ammount ,
A.date ,
A.transactionNumber
COUNT(A.transactionNumber)
FROM TableA a
JOIN TableB b ON a.TransactionNumber = b.TransactionNumber
GROUP BY a.time, b.name, b.number, b.ammount, a.date, a.transactionNumber
WITH ROLLUP
Rick|||What is the primary key? Please post DDL for the table otherwise any
answers you get will just be guesswork. It's also more helpful to
include sample data as INSERT statements rather than tabular text.
Here's an example from the Pubs database that may give you a clue.
SELECT A1.au_id, A1.au_lname, A1.au_fname, A1.state,
COUNT(*) AS num
FROM Authors AS A1
JOIN Authors AS A2
ON A1.state = A2.state
AND A1.au_id >= A2.au_id
GROUP BY A1.au_id, A1.au_lname, A1.au_fname, A1.state
ORDER BY A1.state, A1.au_id ;
David Portas
SQL Server MVP
--

Make 1 query from 2 tables

Hi,
Is it possible with SQL to create 1 query from 2 tables?
Table1 and Table2 have a few corresponding columns but mostly different
ones. Now I need 2 of these columns and get them in a query. Can I get the
values from table in there together with table? The tables have the same
column.
How can this be done?
Thanks
JorisJoris De Groote,
Please, provide tabls schema, sample data and expected results.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Joris De Groote" wrote:

> Hi,
> Is it possible with SQL to create 1 query from 2 tables?
> Table1 and Table2 have a few corresponding columns but mostly different
> ones. Now I need 2 of these columns and get them in a query. Can I get the
> values from table in there together with table? The tables have the same
> column.
> How can this be done?
> Thanks
> Joris
>
>|||Joris De Groote wrote:
> Hi,
> Is it possible with SQL to create 1 query from 2 tables?
> Table1 and Table2 have a few corresponding columns but mostly different
> ones. Now I need 2 of these columns and get them in a query. Can I get the
> values from table in there together with table? The tables have the same
> column.
> How can this be done?
> Thanks
> Joris
Begin by referring to Books Online, look up the SELECT statement, in
particular the JOIN clause.|||Check out these links and read up on SQL a bit...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
"Joris De Groote" <joris.degroote@.skynet.be> wrote in message
news:u$f9iUWiGHA.4144@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is it possible with SQL to create 1 query from 2 tables?
> Table1 and Table2 have a few corresponding columns but mostly different
> ones. Now I need 2 of these columns and get them in a query. Can I get the
> values from table in there together with table? The tables have the same
> column.
> How can this be done?
> Thanks
> Joris
>

Major Search Problem

Hi,
I got a problem on searching a keyword with '&'.
I run this query and I got all records with '&'.
select * from freetexttable(table1, *, 'AT&T');
I run this and I got records with AT&T.
select * from table1 where fieldname1 like '%AT&T%'
Since 'AT' and 'T' are considered noise words, how can I search for
'AT&T' as a whole word?
Thanks in advance.
Baldwin
remove t and at from your noise word list, rebuild your catalogs and it
should work.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Baldwin" <ibaldwinjr@.gmail.com> wrote in message
news:1194974569.682896.215800@.v2g2000hsf.googlegro ups.com...
> Hi,
> I got a problem on searching a keyword with '&'.
> I run this query and I got all records with '&'.
> select * from freetexttable(table1, *, 'AT&T');
> I run this and I got records with AT&T.
> select * from table1 where fieldname1 like '%AT&T%'
> Since 'AT' and 'T' are considered noise words, how can I search for
> 'AT&T' as a whole word?
> Thanks in advance.
> Baldwin
>