Help with SQL query

Hi,

Let's say I have a Customer table and an Order table. The latter is linked to the former through foreign key CustomerID. Now, I want to create a SQL statement which, given a CustomerID, returns the corresponding row in Customer table PLUS a field indicating the total number of orders this particular customer has. How can I achieve this with a single SQL statement?

Thanks in advance

--USE Northwind

SELECT Customers.CustomerID, SUM(Orders.OrderID) AS TotalOrders

FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

GROUP BY Customers.CustomerID

|||Thanks, this is exactly what I want, except I need to change the aggregate function to Count instead of Sum.

Help with simple insert, how to use primary key?

Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.

i want an unique identifier for each row.

heres my stored procedure:

CREATE PROCEDURE composeMessage

-- Add the parameters for the stored procedure here

@.username varchar(24),

@.sender varchar(24),

@.date dateTime,

@.subject varchar(255),

@.message varchar(2500)

AS

BEGIN

insert into Messages(

"Username",

"Sender",

"Date",

"Subject",

"Message"

)

values (

@.username,

@.sender,

@.date,

@.subject,

@.message

)

END

GO

heres my sqlcreate table:

USE [Messenger]

GO

/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Messages](

[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,

[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,

[Date] [datetime] NOT NULL,

[ID] [int] NOT NULL,

CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

As primary keycan't be null, what do i put for primary key for my insert to work?

hope you understand what i mean?

Am i right that i have to set the table designer/identity column to my primary key?

It generates an unique incresing number, so doi i use that?

|||

If you want to have an increasing value, you will have to switch on the identity property on your column.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Help with sample code for ssis surrogate key transform

I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.

Thanks

Basically - here is a surrogate key Transform Script - to generate image numbers for products
Input is ProdNum column - output ImgNo colum.
The idea is to get the result like this:
ProdNum ImgNo
1 1
1 2
2 1
3 1
3 2

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim imgno As Short, incr As Short, prevProdNum As String

Public Sub New()
imgno = 0
incr = 1
prevProdNum = ""
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.ProdNum <> prevProdNum Then
imgno = incr
Else
imgno += incr
End If
Row.ImgNo = imgno
prevProdNum = Row.ProdNum
End Sub
End Class|||Also - you can check out this article "SSIS Generating Surrogate Keys"

Help with query NOT IN

I have a view containing column X and column Y and a foreign key F. I
want to filter the view so that it does not contain any rows which are
in the foreign table, which also contain columns X and Y.
I want to do something like this:
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t ON t.f = v.f
WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
ThanksTry,
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t
ON v.x = t.x and v.y = t.y
WHERE t.X is null and t.Y is null
AMB
"larzeb" wrote:

> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
>|||larzeb wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
Not sure I understand youtr specs. Are you saying you want to see all
rows from the view that do not have a match of all columns (key, x, and
y) in the foregn key table? I don't understand what you mean by "which
also contain columns X and Y" - I assume you mean the same values in x
and y?
Select col1, col2, col3
From vView v
Where Not Exists (
Select *
From Table1 t
On v.f = t.f
and v.x = t.x
and v.y = t.y)
David Gugick
Imceda Software
www.imceda.com

Help with query

Hi, I would like some help with a query. I would like to delete all
rows when the key appears in one table and not the other. For example
the info in table 1 on my DB is my current work. I then import the upto
date info monthly into table 2. Currently I have to manually delete the
info from table 2, hence the need for the query to mismatch and leave
me with the current info.
Hope this makes sense
Thanks)It's ususally a good idea to start a new thread so that your request for he
lp doesn't get lost in someone else's responses. Also good to provide table
DDL and sample data in the form of INSERT statements.)
One way to do this is:
DELETE Table2
WHERE Table2.Key IN ( SELECT Table1.Key
FROM Table1
WHERE {criteria in Table1}
)
Or vice versa to delete from Table1
Let me know if this helps.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"John" <john_sarah_kerr@.hotmail.co.uk> wrote in message news:1151180034.679815.100870@.b68g2
000cwa.googlegroups.com...
> Hi, I would like some help with a query. I would like to delete all
> rows when the key appears in one table and not the other. For example
> the info in table 1 on my DB is my current work. I then import the upto
> date info monthly into table 2. Currently I have to manually delete the
> info from table 2, hence the need for the query to mismatch and leave
> me with the current info.
>
> Hope this makes sense
>
> Thanks
>

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
Chuck
Use the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
ChuckUse the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
ChuckUse the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

Help with primary keys

I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.

If I understand the problem correctly one way to do this is to use a FULL JOIN; maybe something like:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select coalesce (aKey, bKey) as [Key],
case when aKey is null then 'Table B' else 'Table A'
end as sourceTable
from @.tableA
full join @.tableB
on aKey = bKey
where aKey is null
or bKey is null
order by coalesce (aKey, bKey)

/*
Key sourceTable
-- --
2 Table A
4 Table B
5 Table A
7 Table B
*/

Now that I think about it, a better way to do this is probably to do this differently; hang on and I'll get you a better method. This might perform a little better:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select 'TableA' as SourceTable,
aKey as [Key]
from @.tableA a
where not exists
( select 0 from @.tableB b
where aKey = bKey
)
union all
select 'TableB' as SourceTable,
bKey as [Key]
from @.tableB a
where not exists
( select 0 from @.tableA b
where aKey = bKey
)
order by [Key]

/*
SourceTable Key
-- --
TableA 2
TableB 4
TableA 5
TableB 7
*/

|||

If I understand you correctly, you want to find rows in TableA that do not exist in TableB, and conversely, rows in TableB that do not exist in TableA.

Code Snippet

SELECT
'TableA',
PKColumn
FROM TABLEA
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableB
)
UNION

SELECT
'TableB',
PKColumn
FROM TABLEB
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableA
)

If you are using SQL 2005, you could use a EXCEPT JOIN.

|||As usual, Arnie makes good points. Be aware of the EXCEPT join. In many cases it will be slower than the query Arnie put together. I try to avoid using the EXCEPT join.|||

Our (Kent's and mine) suggested solutions are virtually identical -since you are seeking PKeys, there are no duplicates -so DISTINCT is not necessary. And the query processor has to read the entire index anyway, so EXISTS and NOT IN have the same effect for this circumstance.

And if you are using SQL 2005, you could use a FULL OUTER JOIN. Example:

Code Snippet

SET NOCOUNT ON

DECLARE @.TableA table
( PKColumn int )

DECLARE @.Tableb table
( PKColumn int )

INSERT INTO @.TableA VALUES ( 1 )
INSERT INTO @.TableA VALUES ( 2 )
INSERT INTO @.TableA VALUES ( 3 )
INSERT INTO @.TableA VALUES ( 4 )
INSERT INTO @.TableA VALUES ( 5 )

INSERT INTO @.TableB VALUES ( 3 )
INSERT INTO @.TableB VALUES ( 4 )
INSERT INTO @.TableB VALUES ( 5 )
INSERT INTO @.TableB VALUES ( 6 )
INSERT INTO @.TableB VALUES ( 7 )

SELECT
'TableA' = a.PKColumn,
'TableB' = b.PkColumn
FROM @.TableA a
FULL OUTER JOIN @.TableB b
ON a.PKColumn = b.PKColumn
WHERE ( a.PKColumn IS NULL
OR b.PKColumn IS NULL
)
ORDER BY ( isnull( a.PKColumn, 0 ) + isnull( b.PKColumn, 0 ))

TableA TableB
-- --
1 NULL
2 NULL
NULL 6
NULL 7

IF this is a regular process, you might wish to check which of the possiblities is most efficient. (I'm betting on the FOJ.)

Help with pivot table

Hi!

I`ve been struggling to find a solution to make a report from MS SQL, and I think pivot table is the key but I`m not sure on how to do this. Hope someone can help, here is the case :

I have one table "Products" and one table "Packages". Every product in the Products table has 1 - * Packages (unit which the product is sold by)

The Products table looks something like this :

Id | productText | productGroup | etc

And the Packages table like this :

Id | productId | packageType | weight | etc

What I want to do is to flat this out so I can get the Product and all it`s packages in one record. Something like this :

productText | productGroup | packageType | weight | packageType | weight | etc.

I would really appriciate if someone could help me out with this.

Regards,
KnutYou need to use DECODE for the purpose.

help with integer field

hi all,

i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.Hi, try this trigger...

CREATE TRIGGER trig_MyTableAddTrigger
ON MyTable
FOR INSERT
AS
-- Get the value to put in the second column
DECLARE @.NewValue integer
SELECT @.NewValue = (SELECT MyTableID FROM Inserted) + 1000

-- Update the same table [MyTable] with new value
UPDATE [MyTable] SET SecondColumName = @.NewValue
WHERE MyTableID = (SELECT MyTableID FROM INSERTED)|||thank you very,very much for your help.

help with Insert SQL Query

i want to implement something like let say i have 2 table...customer table and order table...order table has a foreign key of customer table (maybe the customer_id)...is there any way that let say, i want to insert a particular customer_id in the customer table. Then, it will insert the particular customer_id in the order table also. I want to makeone statement query that can solve that situation?

Hello,

if you create the id yourself then you can insert it into the second table. With SQL Server you can send two queries in one command, separated by a;. But if you have the first table set up with an auto incrementing identity then you will first have to find out the id that the database has created for you. This can be done with the SCOPE_IDENTITY() function in SQL Server.

Good luck!

|||

i create the id yourself...thanx for your help...anyway, is there any other way? Because actually i have to add the id from 1st table to many other tables...maybe 5 6 tables...i think a lot of sql query i have to execute if i have to add so many ids from 1st table...

|||

Hi,

As far as I know, there is no other ways. Multiple SQL Statement can be wrapped in a single SqlCommand. However, these muliple statements have to be written manually. The SQL Server itself will not do this for you. In this case, you may need to write 5-6 queries and wrap them in one SqlCommand.

HTH.

|||

I think that you have to look on the structure of your database if you have to insert the same ID to multiple data tables. Other tables should have it as foreign key so it should be inserted only when you add new data to table linked to you main table, and in this case you have to get your ID and insert it together with record data. The best way to do this is stored procedure with included transaction if you need it.

If it is true that you create ID yourself maybe you can use identity column in your main table to do it automatically?

Thanks

JPazgier

Help with INSERT query

I need to append records into a table with a two column primary key from a table that contains many records that already exist in the target table. How do I separate out the records in the source table that don't exist in the target?

When I used to do this in Access, I could write a simple append query that would automatically skip records in source that violated key constraints in the target. I'm trying to duplicate that funcionality.

Thanks.

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
WHERE Source.field NOT IN (SELECT Field FROM TARGET)

If you want to do a multi-field check...

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field = Source.Field
WHERE Target.field IS NULL

|||

Thanks so much for the quick reply.

I need to check both Source.field1 and Source.field2 against their counterparts in Target since it's the combination that provides the primary key in Target.

What I've tried that gets me a duplicate key error is

INSERT INTO Target (field1, field2)
SELECT field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field1 = Source.Field1 AND Target.field2 = Source.Field2
WHERE Target.field1 IS NULL AND Target.field2 IS NULL

It looks like it should work, but it doesn't.

Kato

|||

In your where clause, all you need to do is check the nullability of one field from the Target table. If the join fails, every field in your left table will be null.

If you get a duplicate key error, try using a SELECT DISTINCT instead of just a select.

If you still get the error, what's your primary key / unique index on in Target?