Help with SQL Query

Given the following two tables below, I need help in writing a query that would retreive only 3 or less distinct values of BDesc from tbB table for every row found in tbA.

for example:

I expect result to be:

Aid Bdesc
100 1st Desc for 100
100 2nd Desc for 100
100 3rd Desc for 100
200 1st Desc for 200
200 2nd Desc for 200
200 3rd Desc for 200
300 1st Desc for 300
300 2nd Desc for 300
300 3rd Desc for 300
400 1st Desc for 400
500 1st Desc for 500
500 3rd Desc for 500

The tables are:

use tempdb

go

set nocount on

if exists (select name from sysobjects where name = 'TbA')

Drop table TbA

Create Table TbA ( Aid int )

Insert into TbA values(100)

Insert into TbA values(200)

Insert into TbA values(300)

Insert into TbA values(400)

--select * from TbA

if exists (select name from sysobjects where name = 'TbB')

Drop table TbB

Create Table TbB ( Bid int , BDesc varchar(50) )

INSERT INTO TbB Values(100, '1st Desc for 100')

INSERT INTO TbB Values(100, '2nd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(200, '2nd Desc for 200')

INSERT INTO TbB Values(200, '3rd Desc for 200')

INSERT INTO TbB Values(200, '4th Desc for 200')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(300, '1st Desc for 300')

INSERT INTO TbB Values(300, '2nd Desc for 300')

INSERT INTO TbB Values(300, '3rd Desc for 300')

INSERT INTO TbB Values(300, '4th Desc for 300')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '3rd Desc for 500')

--select * from TbB

Thanks for your help with this...

Here ya go

Code Snippet

selectdistinct TbB.*

from(selectdistinct aid from TbA)as TbA

innerjoin TbB

on TbA.Aid = TbB.Bid

and TbB.BDesc in

(selectdistincttop 3 BDesc from TbB where Bid = TbA.Aid orderby BDesc)

|||Thanks|||

How about if I wanted to get the result like this:

ColA ColB

100 1st Descfor 100, 2nd Descfor 100, 3rd Descfor 100

200 1st Descfor 200, 2nd Descfor 200, 3rd Descfor 200

300 1st Descfor 300, 2nd Descfor 300, 3rd Descfor 300

400 1st Descfor 400

500 1st Descfor 500, 3rd Descfor 500

|||hi, you can try using a udf

CREATE FUNCTION dbo.GetBDesc
(
@.AID int
)
RETURNS varchar(800)
AS
BEGIN
DECLARE @.BDesc varchar(100)
SET @.BDesc = ''
SELECT
@.BDesc = @.BDesc + BDesc + ','
FROM (SELECT DISTINCT TOP 3 * FROM TbB a WHERE a.BID = @.AID) b
WHERE BID = @.AID
ORDER BY
BDesc

IF @.BDesc <> '' SET @.BDesc = LEFT(@.BDesc, LEN(@.BDesc) - 1)

RETURN @.BDesc

END

GO

select *
, dbo.GetBDesc(AID)
from tba|||

If you use SQL Server 2005 you dont need a function...

Here the sample,

Code Snippet

Create Table #TableA(

Aid int );

Insert into #TableA values(100)

Insert into #TableA values(200)

Insert into #TableA values(300)

Insert into #TableA values(400)

Insert into #TableA values(500)

Create Table #TableB(

Bid int

,BDesc varchar(50)

)

INSERT INTO #TableB Values(100, '1st Desc for 100')

INSERT INTO #TableB Values(100, '2nd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(200, '2nd Desc for 200')

INSERT INTO #TableB Values(200, '3rd Desc for 200')

INSERT INTO #TableB Values(200, '4th Desc for 200')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(300, '1st Desc for 300')

INSERT INTO #TableB Values(300, '2nd Desc for 300')

INSERT INTO #TableB Values(300, '3rd Desc for 300')

INSERT INTO #TableB Values(300, '4th Desc for 300')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '3rd Desc for 500')

;With DistinctData

as

(

Select Distinct A.Aid,B.BDesc from #TableA A Join #TableB B On A.Aid =B.Bid

),

RowData

as

(

Select Aid,Bdesc,Row_Number() Over(Partition By Aid Order By BDesc) RowID From DistinctData

)

/*

Select

Aid,

BDesc

From

RowData

Where

RowID <=3

*/

Select Distinct

Aid

,Substring((Select ',' + BDesc as [text()] From RowData Sub Where Sub.Aid=Main.Aid And Sub.RowId<=3 For XML Path(''), Elements),2,8000) as Descs

From

RowData Main

|||MG,

How about these two queries (both require SQL 2005)

select a.aid, b.bdesc
from tbA a
cross apply
(select distinct top (3) bdesc from tbB b where b.bid = a.aid) b
;

select a.aid as ColA, stuff((select distinct top (3) ', ' + bdesc from tbB b where b.bid = a.aid order by 1 for xml path('')),1,2,'') as ColB
from tbA a
;

The second one puts them into a single column for you.

Rob|||I should have mentioned that this is for SQL 2000 and for an OLTP environment. The procedure processes approx. 20,000 rows and right now it's using cursor logic which is slowing things down, so I was looking for ways to use set based processing. The function idea is good, but again its going to be row by row processing.|||

The function approach should be a significant improvement over any cursor processing.

Is there something we're not understanding about what you want to accomplish?

|||

Hi Rhamille Golimlim,

There is an issue when using "order by" during an aggregate concatenation query.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

http://support.microsoft.com/default.aspx/kb/287515

AMB

|||thanks for the tip hunchback. would it still show a different execution plan if we put the order by inside the subquery?|||

Hi Rhamille Golimlim,

If you put the "order by" clause inside the derived table, then how are you going to be sure that the result is sorted if the only way to asure a sorted resultset is using the "order by" clause in the statement that pull the data?. It is like sorting inside a view and not using "order by" clause when you pull from the view.

Concatenating row values in T-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB

|||hi hunchback,

cool, would the xml path approach be the best work around for this scenario? or are there other alternatives or tsql hacks?

/rhamille

Help with simple query involving 3 tables

Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:

create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go

create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go

create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go

I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @.MinDate - @.MaxDate)

Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--- ------ ----- ------ --
HR 5 7 12 .84
Finance 10 15 25 1.75

So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.

One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!SELECT U.name, sum(J.NumberPrintedPages), sum(J.NumberPrintedPages * P.PaperCost)
FROM Jobs J INNER JOIN User_Tbl U ON J.User_ID = U.ID
INNER JOIN Printer_Tbl P ON J.Printer_ID = P.ID
GROUP BY U.Name

This example shows, how to join your tables, and how to return two of your fields. Since a user may have used 0 to n printers in his jobs, there isn't a clear indication of your fields #PagesPrinter1 and #PagesPrinter2.|||He wants a crosstab query.

Nicomachus, look up CROSSTAB in Books Online and you will see an excelent example of how to accomplish this using CASE statements. Unfortunately, it requires considerable programming to make your crosstab queries dynamic as the number of columns (printers) changes.
Supposedly this feature will be built into the next version of SQL Server, but in any case when you make your output dynamic you are going to have a hard time building reports around it, because the output format will not be consistent.

You are really best served by outputting your data in a standard normalized format and then letting your reporting application (Crystal, Access, whatever...) handle formatting as a crosstab.

Help with simple join?

I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:

I have 3 tables I want to join:

Sale Table:
Sale_No Cus_No Sale_Qty
1 Joe01 250

Order Table:
Ord_No Sale_No Order_Qty ShipToCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
4 1 20 DestB
5 1 20 DestB

ShipTo Table:

Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston

A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.

I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).

What I came up with is this, but is this correct?:

FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_noI built and populated the tables and used this query:
SELECT Sale.Sale_No, Sale.Cus_No, Sale.Sale_Qty, [Order].Order_Qty, ShipTo.ShipToName
FROM Sale INNER JOIN
[Order] ON Sale.Sale_No = [Order].Sale_No INNER JOIN
ShipTo ON [Order].ShiptoCode = ShipTo.ShipToCode

It returned this result:

Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Chicago
1 Joe01 250 20 Chicago

Not sure if this is what you want?

best regards
mkal|||Hmmm, did you include the last row of the ShipTo Table:
Bob01 DestA Boston

Wouldn't your query bring up a row for orders 1, 2, and 3 for both Boston and Philadelphia?

My issue is that I need to get to the ShipToName in the ShipTo table from the ShipToCode in the Orders table, BUT I need to include Cus_No in the join because the PK in the ShipTo table is both ShipToCode AND Cus_No.

Thank you for your help.|||It would if Bob01 was in the Sales table but he is not. Sales joins to Orders on the Sale_No, no Sale no bob

hope this helps
mkal|||How does it know that?

You're just joining on ShipToCode, and for orders 1,2, and 3, the ShipToCode is 'DestA' and for Boston and Philadelphia, the ShipToCode is 'DestA'.

Where in your join does it know to match on cus_no also?|||In the Sales table we have Joe01 and in the orders table we have Joe01(numerous times) so the first join between Sales and Orders (on the Sales_No) returns 5 rows, these five rows are then in turn joined to the ShipTo table, but since bob01 is not in the Sales or Orders table the first join returns no rows, with no row returned there is nothing to join on to the ShipTo table.

hope this helps
mkal|||Cus_no isn't in the order's table, that's my problem, so Joe01 isn't in the orders table, only in the Sales table. I still am missing any connection by cus_no between the orders and the ShipTo table.

so for example, if the sample data were:

Sale Table:
Sale_No Cus_No Sale_Qty
1 Joe01 250
2 Bob01 250

Order Table:
Ord_No Sale_No Order_Qty ShipToCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
4 1 20 DestB
5 1 20 DestB
6 1 20 DestA
7 1 20 DestA
8 1 20 DestA
9 1 20 DestB
10 1 20 DestB

ShipTo Table:

Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston
Bob01 DestB Spokane

how does the join know to differentiate between DestA = Philadelphia for Joe01 and DestA = Boston for Bob01 when there are no joins on Cus_No?

...I don't think i'm missing anything|||Your problem lies in the data in the Orders table it should look like this
Ord_No Sale_No Order_Qty ShiptoCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
6 2 20 DestA
7 2 20 DestA
8 2 20 DestA
9 2 20 DestB
10 2 20 DestB
4 1 20 DestB
5 1 20 DestB

Then the query returns
Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
2 Bob01 250 20 Philadelphia
2 Bob01 250 20 Philadelphia
2 Bob01 250 20 Philadelphia
2 Bob01 250 20 Chicago
2 Bob01 250 20 Chicago
1 Joe01 250 20 Chicago
1 Joe01 250 20 Chicago|||Sorry about that, you are correct about the Orders Table, I forgot to change the Sale_No when I added the addtional orders.

Assuming this data:
Sale Table (I changed the Qty just to differentiate):
Sale_No Cus_No Sale_Qty
1 Joe01 250
2 Bob01 100

Order Table(I changed the Qty just to differentiate):
Ord_No Sale_No Order_Qty ShipToCode
1 1 50 DestA
2 1 50 DestA
3 1 50 DestA
4 1 50 DestB
5 1 50 DestB
6 2 20 DestA
7 2 20 DestA
8 2 20 DestA
9 2 20 DestB
10 2 20 DestB

ShipTo Table:
Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston
Bob01 DestB Spokane

I want this returned:
Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Chicago
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Spokane
2 Bob01 100 20 Spokane
2 Bob01 100 20 SpokaneAnd unless I am mistaken, the query that you used does not distinguish between the ShipToNames for Bob01 and Joe01 because it doesn't join on cus_no.

But I think this one does:

FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_no
I'm just curious if you can even do what I'm doign here, which is joining on fields that aren't in the two tables being joined, I think that makes sense.

Thanks for your help.|||So here's my query:
SELECT Sale.Sale_No, Sale.Cus_No, Sale.Sale_Qty, [Order].Order_Qty, ShipTo.ShipToName
FROM ShipTo INNER JOIN
Sale ON ShipTo.Cus_No = Sale.Cus_No INNER JOIN
[Order] ON Sale.Sale_No = [Order].Sale_No

and here are the results:
Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago

I may be mistaken but I think this is what you want.
best regards.
mkal|||No, what I am looking for is really just the orders table and then I'm going to the other tables to lookup names from the codes. Maybe this will help:

Ord_No Sale_No Cus_No Sale_Qty Order_Qty ShipToCode ShipToName
1 1 Joe01 250 50 DestA Philadelphia
2 1 Joe01 250 50 DestA Philadelphia
3 1 Joe01 250 50 DestA Philadelphia
4 1 Joe01 250 50 DestB Chicago
5 1 Joe01 250 50 DestB Chicago
6 2 Bob01 100 20 DestA Boston
7 2 Bob01 100 20 DestA Boston
8 2 Bob01 100 20 DestA Boston
9 2 Bob01 100 20 DestB Spokane
10 2 Bob01 100 20 DestB Spokane

which I think I get from:

FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_no

I'm just wondering if what I'm getting is coincidental or if I can legitimately use tables in the ON clause that aren't in that line of the JOIN.|||I guess now I'm the one who is confused. Why would you use values from a single query on the Orders table to be used in a lookup query. The select statement that joins the three tables together gives you all of what you're looking for. If you want to see the value of the ShipToCode & Ord_No (yours has it mine didn't) just add it to the select statement like below.

SELECT [Order].Ord_No, Sale.Sale_No, Sale.Cus_No, Sale.Sale_Qty, [Order].Order_Qty, [Order].ShipToCode, ShipTo.ShipToName
FROM ShipTo INNER JOIN
Sale ON ShipTo.Cus_No = Sale.Cus_No INNER JOIN
[Order] ON Sale.Sale_No = [Order].Sale_No

If for some reason you need to lookup values in the Sales & ShipTo tables based on what is in the Orders table then you will probably need to use a cursor.

And as far as the data your returning, I think its legitimate, meaning it will return the same values each and everytime the query is run.|||I only added the Ord_No and ShipToCode to the query output to show which rows were generating which values.

Anyway...I think we've beaten this to death, this has been helpful in examining the table structure and query design, but I think we can both move on with our productive lives now.

Thank you. This place is great.

Help with ServerAgent Job syntax

Hi,

Hopefully someone can help me. I'm having difficulty with the syntax to delete a record from 4 joined tables when creating a job.

I have an 'Applicants' table linked to four other tables 'Courses', 'EmploymentHistory', 'Qualifications', and 'References' using the field 'ApplicantID'.

I want to create a job to delete all the records where the Finalised field = '0' and the record was created more than 3 days ago.

The syntax I have been using on just one of the joined tables to start with doesn't delete from the joined table:

USE OnlineApplications
DELETE Applicants
FROM Applicants
INNER JOIN Courses
ON Applicants.ApplicantID = Courses.ApplicantID
WHERE Finalised = 0 AND Created < DATEADD(d, 3, Created)

How can I delete the records from the other four tables?

ThanksHave you defined foreign key relationships?|||Darnit Poots, you beat me to it again!
I was going to ask for the joins between the tables :'(|||joins <> relationships ;)|||*shifty look*
I knew that ;)|||Have you defined foreign key relationships?

Nope, makes sense to do that I suppose. :o

For the projects I do, I merely create the tables then use the tables to store the data, haven't needed to create relationships in the past, bad development I know.

But I haven't really looked into the features of SQL Server yet.

I take it the syntax should work then if I create relationships?
Can I do this by creating a diagram?
Then I take it I need to set the joins to cascade to delete from other tables?|||Can I do this by creating a diagram?Yeah - but script it out.
Basic sample code. Check BoL for more options.

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE t_name
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'other_t_name') BEGIN
DROP TABLE other_t_name
END
CREATE TABLE dbo.other_t_name
(
c_name INT NOT NULL CONSTRAINT df_other_t_name_c_name DEFAULT 0
, CONSTRAINT pk_other_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_other_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_other_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
)
GO
CREATE TABLE dbo.t_name
(
c_name INT NOT NULL CONSTRAINT df_t_name_c_name DEFAULT 0
, CONSTRAINT pk_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
, CONSTRAINT fk_t_name_other_t_name FOREIGN KEY (c_name) REFERENCES other_t_name (c_name) ON DELETE CASCADE
)
GO

HTH

Help with SELECT statement

Hi,
I have two tables with a UserID column and need to construct a query that
lists all UserIDs from Table A that is not present in Table B.
Any help with this select statement would be appreciated
NiclasSelect A.* from TableA as A where Not Exists (select * from TableB as B
where B.UserId = A.UserId)
You can also go with a Left Outer Join but it's a little more complicated to
understand:
Select A.* from TableA as A Left Outer Join TableB as B on A.UserId =
B.UserId
Where B.UserId is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||A third possibility would be to use the IN clause:
Select A.* from TableA as A where A.UserId Not IN (select UserId from TableB
Where UserId is not Null)
The condition Where B.UserId is Not Null is a necessity if there is a
possibility that B.UserId can be Null; otherwise the result won't be good if
the IN clause encounter a Null value.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||Many thanks !
Niclas
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:emU3YG65FHA.2888@.tk2msftngp13.phx.gbl...
>A third possibility would be to use the IN clause:
> Select A.* from TableA as A where A.UserId Not IN (select UserId from
> TableB Where UserId is not Null)
> The condition Where B.UserId is Not Null is a necessity if there is a
> possibility that B.UserId can be Null; otherwise the result won't be good
> if the IN clause encounter a Null value.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Niclas" <lindblom_niclas@.hotmail.com> wrote in message
> news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
>

Help with Select query

This should be simple I think but I am no expert so maybe one of you will have the kindness to help me a bit. I have two tables(System, NAIC) and both have the primary key SystemId.

I need to gell all the rows from the table system and anything that correspond from the table NAIC, if no correspondant systemId the return "" or nothing in the fields of NAIC

Thank you,

Table:
System
-SystemId*
-Company
-Reseller
-SystemType
...

NAIC
-SystemId*
-NAIC_1
-NAIC_2
-NAIC_3
-NAIC_4

I think what you need is a basic LEFT JOIN.

SELECT

SystemId
Company
Reseller
SystemType

...

FROM

[system]

LEFT OUTER JOIN NAIC

ON [System].Systemid = NAIC.SystemId

sql

Help with Select - into

Hi everybody,

I have two tables 'tab1' and 'tab2'. 'tab2' contains the same columns
as 'tab1'. 'tab2' does NOT contain any of the constraints of 'tab1',
just the fields. When I create this 'tab2' table using CREATE TABLE,
it gets created fine. Then I use a stored procedure which has a SELECT
INTO statement to copy all data from 'tab1' into 'tab2'. Now If I want
to append more data to 'tab2', I find that it tells me IDENTITY INSERT
on 'tab2' SHOULD BE SET TO ON. I have not defined any identity columns
in the CREATE TABLE, but after executing the SELECT INTO, I found that
it made one column the IDENTITY. Why is this so and How do I just copy
the data from 'tab1' without the frills ? All i want is a dump of one
table into another. How do I do this with an SQL query ?

Thanks in advance.

Best Regards.If you do SELECT INTO it will copy all the columns, including the IDENTITY
property but not including any CHECK, UNIQUE, FK or PK constraints. SELECT
INTO will fail if the target table already exists.

To re-create the table without the identity column:

CREATE TABLE Tab1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, ...)

INSERT INTO Tab2 (X,Y,...)
SELECT X,Y,...
FROM Tab1

--
David Portas
----
Please reply only to the newsgroup
--|||You could try to confuse SQL Server by adding a bogus expression to the
identity column's select to break the connection.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<rdadnS_XYP16OyGi4p2dnA@.giganews.com>...
> If you do SELECT INTO it will copy all the columns, including the IDENTITY
> property but not including any CHECK, UNIQUE, FK or PK constraints. SELECT
> INTO will fail if the target table already exists.
> To re-create the table without the identity column:
> CREATE TABLE Tab1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, ...)
> INSERT INTO Tab2 (X,Y,...)
> SELECT X,Y,...
> FROM Tab1

Thank you for your help. It worked.

Best Regards.

Help with SELECT

Hi all,
I have the following tables: Member, Dependent, Doctor.
I am trying to get, based on the name entered, all members and dependents
that have seen a certain doctor.
For example, I supply the last name SMITH and the docotor ID 1234567890. I
need to get all of the members with the last name of SMITH and all of the
dependents with the last name of SMITH who have records in the docotor table
for the doctor with the ID 1234567890.
I can do this seperatley for the Member and Dependent, but I need to wind up
with one set of results with both the members and dependents sorted by name.
I am writing this for a web site in vbscript, so I did not think that
creating tables would be the best solutions.
Any ideas on how to wind up with the data needed?
Thanks in advance,
GeorgeWoudl be good to provide some more sample data / DDL.
http://www.aspfaq.com/5006
HTH, Jens Suessmeyer.|||If I understand your issue correctly, you just need to use a union.
Select memberName as name, DoctorID
from tblmember
inner join tbldoctor on tblmember.doctorid = tbldoctor.doctorid
union all
Select DependentName as name, DoctorID
from tblDependent
inner join tbldoctor on tblDependent.doctorid = tbldoctor.doctorid
You could also create a view that contains all records from both the
dependent and the member table, then join to that. This is ideal if you
will be doing this sort of thing often (which you probably will).
Select memberName as name, DoctorID [any other applicable fields] from
tblmember
union all
Select DependentName as name, DoctorID [any other applicable fields] from
tblDependent
Either way you will probably want to make the query into a view which you
can then select from and pass the name in without caring about the
underlying SQL of the joins and unions.
"George" <George@.discussions.microsoft.com> wrote in message
news:8B8F8254-73D8-4C4E-97BE-BB35179EFACF@.microsoft.com...
> Hi all,
> I have the following tables: Member, Dependent, Doctor.
> I am trying to get, based on the name entered, all members and dependents
> that have seen a certain doctor.
> For example, I supply the last name SMITH and the docotor ID 1234567890.
I
> need to get all of the members with the last name of SMITH and all of the
> dependents with the last name of SMITH who have records in the docotor
table
> for the doctor with the ID 1234567890.
> I can do this seperatley for the Member and Dependent, but I need to wind
up
> with one set of results with both the members and dependents sorted by
name.
> I am writing this for a web site in vbscript, so I did not think that
> creating tables would be the best solutions.
> Any ideas on how to wind up with the data needed?
> Thanks in advance,
> George

Help with security setup on SQL

Hi - I have a simple database (sql 2000) on a dedicated server - I only,
at the moment, use SPs and tables. All of these currently have the
owner set to DBO. Is this ok?
When using .net to allow people to access the database via the web,
should I first setup a User within sql server, and then amend my
connection string in the .config file to use that user only (and
reserver the SA login for myself - as I need to administer the database
via the web too).
What do I need to be careful of when setting permissions for users? eg.
the users will need to be able to add/amend to many tables, and to be
able to run the SPs. In some tables, they will also have to be able to
run delete queries from the DB.
Are there any 'idiots' guides to this to help me get started?
Thanks for any help,
Mark
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!I would start with BooksOnLine. There is a lot of good information on
Security that should get you going in the right direction.
security-SQL Server, overview
Andrew J. Kelly SQL MVP
"Mark" <anonymous@.devdex.com> wrote in message
news:Oiht6wAuEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi - I have a simple database (sql 2000) on a dedicated server - I only,
> at the moment, use SPs and tables. All of these currently have the
> owner set to DBO. Is this ok?
> When using .net to allow people to access the database via the web,
> should I first setup a User within sql server, and then amend my
> connection string in the .config file to use that user only (and
> reserver the SA login for myself - as I need to administer the database
> via the web too).
> What do I need to be careful of when setting permissions for users? eg.
> the users will need to be able to add/amend to many tables, and to be
> able to run the SPs. In some tables, they will also have to be able to
> run delete queries from the DB.
> Are there any 'idiots' guides to this to help me get started?
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Mark
In addition to Andrew's advice I'd also recommend you to read some stuff
about SQL Server injection which may hurt your SQL Server database.
http://www.dbazine.com/cook8.shtml
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$FaeoDuEHA.1400@.TK2MSFTNGP11.phx.gbl...
> I would start with BooksOnLine. There is a lot of good information on
> Security that should get you going in the right direction.
> security-SQL Server, overview
>
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <anonymous@.devdex.com> wrote in message
> news:Oiht6wAuEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Help with relational query - many to many

Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused.
I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item.
A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID IN (10, 13, 17)
But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull outONLYitems that have a AttributeID of 10 AND 13 AND 17.
Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :S
I've also tried:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID = 10 AND AttributeID = 13 etc
But obviously that won't work! :sSelect *
from items i
where exists (select null from linking where link_itemID = i.item_id and link_attributeID = 10) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 13) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 17)

Nick|||Sorry for the late reply! Thanks for your post nick!
i got it working :)

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>sql

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>

Help with query required...

I have two related tables in my SQL database that I wish to join as follows:

-----------

tblCustomers
ID (pk)
Name
etc.

tblCustomerManagers
ID (pk)
CustomerID (fk)
Manager (this *is* an fk but for the purposes of demonstration is
not)
StartDate (indicates the date upon which the manager took / is taking
control of the company)

-----------

Example entries are:

tblCustomers
1 Microsoft
2 Symantec
3 Borland

tblCustomerManagers
1 1 Barry 01/01/03
2 1 Peter 01/07/03
3 2 Norman 01/02/03
4 3 Terry 01/01/03
5 3 Peter 01/07/05

-----------

What I want to do is extract, in one query, a list of all customers and
their *current* associated manager, so the result set today would be:

Microsoft Peter 01/07/03
Symantec Norman 01/02/03
Borland Terry 01/01/03

Currently I have:
SELECT [Name], [Manager], [StartDate]
FROM tblCustomers
INNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =
tblCustomers.[ID]
WHERE [StartDate] <= GETDATE()
ORDER BY [Name], [StartDate] DESC

but this obviously returns multiple entries for customers having managers
prior to today eg:

...
Microsoft Peter 01/07/03
Microsoft Barry 01/01/03
...

I know this is a simple question but I cannot think of a way of doing it
without making the query extremely complicated.

Any help is appreciated,
Thanks,
dfPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Please read ISO-11179 and a book on data modeling;
what you did post is wrong. Name data elements for what they mean in
the model, NOT for how they are PHYSICALLY stored! If the first table
is really a model of the customers who bought tables and you have a
"chairCustomers", "stoolCusotmers", etc. table I apologize :)

There is no such thing as a global, magical, universal ID in the
RDBMS. To be is to be something in particular; to be nothing in
particular or everything in general is to be nothing.

Entities have duration, not a point in time, so your design is wrong.
Look up a column I did in INTELLIGENT ENTERPRISE website on the topic
of time. You also avoided any natural keys, so the schema has no data
integrity. Try this:

CREATE TABLE Customers
(cust_nbr INTEGER NOT NULL PRIMARY KEY,
cust_name CHAR(35) NOT NULL,
..);

CustomerManagers
(cust_nbr INTEGER NOT NULL
REFERENCES Customers(cust_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
manager_nbr INTEGER NOT NULL
REFERENCES Managers(manager_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
end_date DATETIME, -- null means current
CHECK (start_date <= end_date),
PRIMARY KEY (cust_nbr, manager_nbr, start_date));

>> What I want to do is extract, in one query, a list of all customers
and their *current* associated manager, <<

Put this in a VIEW:

SELECT cust_name, manager_nbr, start_date, CURRENT_TIMESTAMP
FROM Customers AS C1,
CustomerManagers AS M1
WHERE M1.cust_id = C1.cust_id
AND M1.end_date IS NULL;

Much easier with the right data model!|||"digitalfish" <digital.fish@.ntlworld.com> wrote in message
news:Gqn1b.2773847$mA4.379928@.news.easynews.com...
> I have two related tables in my SQL database that I wish to join as follows:
> -----------
> tblCustomers
> ID (pk)
> Name
> etc.
> tblCustomerManagers
> ID (pk)
> CustomerID (fk)
> Manager (this *is* an fk but for the purposes of demonstration is
> not)
> StartDate (indicates the date upon which the manager took / is taking
> control of the company)
> -----------
> Example entries are:
> tblCustomers
> 1 Microsoft
> 2 Symantec
> 3 Borland
> tblCustomerManagers
> 1 1 Barry 01/01/03
> 2 1 Peter 01/07/03
> 3 2 Norman 01/02/03
> 4 3 Terry 01/01/03
> 5 3 Peter 01/07/05
> -----------
> What I want to do is extract, in one query, a list of all customers and
> their *current* associated manager, so the result set today would be:
> Microsoft Peter 01/07/03
> Symantec Norman 01/02/03
> Borland Terry 01/01/03
>
> Currently I have:
> SELECT [Name], [Manager], [StartDate]
> FROM tblCustomers
> INNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =
> tblCustomers.[ID]
> WHERE [StartDate] <= GETDATE()
> ORDER BY [Name], [StartDate] DESC
> but this obviously returns multiple entries for customers having managers
> prior to today eg:
> ...
> Microsoft Peter 01/07/03
> Microsoft Barry 01/01/03
> ...
> I know this is a simple question but I cannot think of a way of doing it
> without making the query extremely complicated.
> Any help is appreciated,
> Thanks,
> df

CREATE TABLE tblCustomers
(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL
)

INSERT INTO tblCustomers (id, name)
VALUES (1, 'Microsoft')
INSERT INTO tblCustomers (id, name)
VALUES (2, 'Symantec')
INSERT INTO tblCustomers (id, name)
VALUES (3, 'Borland')

CREATE TABLE tblCustomerManagers
(
id INT NOT NULL PRIMARY KEY,
customerid INT NOT NULL REFERENCES tblCustomers (id),
manager VARCHAR(20) NOT NULL,
startdate DATETIME NOT NULL
)

INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (1, 1, 'Barry', '20030101')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (2, 1, 'Peter', '20030701')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (3, 2, 'Norman', '20030201')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (4, 3, 'Terry', '20030101')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (5, 3, 'Peter', '20050701')

SELECT C.name, M.manager, M.startdate
FROM tblCustomers AS C
INNER JOIN
tblCustomerManagers AS M
ON M.startdate <= CURRENT_TIMESTAMP AND
M.customerid = C.id AND
NOT EXISTS (SELECT *
FROM tblCustomerManagers AS M2
WHERE M2.startdate > M.startdate AND
M2.startdate < CURRENT_TIMESTAMP AND
M2.customerid = M.customerid)

name manager startdate
Microsoft Peter 2003-07-01 00:00:00.000
Symantec Norman 2003-02-01 00:00:00.000
Borland Terry 2003-01-01 00:00:00.000

Regards,
jag

Help with Query ?

have two tables with the following strcuture :

Test Type
ID
GroupID
Type

Type Group
GroupID
Description

Now type group has lot of data in it, I want only
GroupID which has specific name
so I did....

SELECT * FROM
TypeGroup tg
WHERE tg.Description = 'Test'
OR tg.Description = 'Test1'

Now this gives me two groupID's so far its fine....

Now based on the groupID's I need to get all the types
from Test Type Table and I did something like this :

SELECT Type
FROM TypeGroup tg, TestType tt
WHERE tg.Description = 'Test'
OR tg.Description = 'Test1'
AND tg.GroupID = tt.GroupID

The above where its not happy, How can I perform to get
the desired results :

The result what I'm getting now is everything and some
repetition from TestType table...

Sample Data :

Type Group Table
1, Test
2, Test1
3, Test2
4, Test4
5, Test10

Test Type Table
1, 1, Something
2,1, Something else
3,1, Something different
4, 2, Very Different
5,3,Testing is good
6,3, Do More Testing<br. 7,4,Yield Better Results>
8,5, The better the results, better it is

From the Query I'm looking for is
Something,Somethng else, Something different, Very Different

Thanks a lot for the help.

First, you are using the 'old form' of JOINs. A JOIN should be in the new form for reliability. If I understand your question correctly, this may work for you:

Code Snippet

SET NOCOUNT ON

DECLARE @.TypeGroup table
( TypeGroupID int IDENTITY,
TypeDescrip varchar(20)
)

INSERT INTO @.TypeGroup VALUES ( 'Test' )
INSERT INTO @.TypeGroup VALUES ( 'Test1' )
INSERT INTO @.TypeGroup VALUES ( 'Test2' )
INSERT INTO @.TypeGroup VALUES ( 'Test4' )
INSERT INTO @.TypeGroup VALUES ( 'Test10' )

DECLARE @.TestType table
( TestTypeID int IDENTITY,
TypeGroupID int,
TestDescript varchar(50)
)

INSERT INTO @.TestType VALUES ( 1, 'Something' )
INSERT INTO @.TestType VALUES ( 1, 'Something else' )
INSERT INTO @.TestType VALUES ( 1, 'Something different' )
INSERT INTO @.TestType VALUES ( 2, 'Very Different' )
INSERT INTO @.TestType VALUES ( 2, 'Very Different' )
INSERT INTO @.TestType VALUES ( 3, 'Testing is good' )
INSERT INTO @.TestType VALUES ( 4, 'Testing is good' )
INSERT INTO @.TestType VALUES ( 3, 'Do More Testing' )
INSERT INTO @.TestType VALUES ( 4, 'Yield Better Results' )
INSERT INTO @.TestType VALUES ( 5, 'The better the results, better it is' )

SELECT DISTINCT tt.TestDescript
FROM @.TypeGroup tg
JOIN @.TestType tt
ON tg.TypeGroupID = tt.TypeGroupID
WHERE ( tg.TypeDescrip = 'Test'
OR tg.TypeDescrip = 'Test1'
)

Help with Query - Insert multiple rows and link between tables.

I am trying to do the following:

Insertn rows into A Table calledEAItems. For each row that is inserted intoEAItemsI need to take thatItemID(PK) and insert a row intoEAPackageItems.

I'm inserting rows from a Table calledEATemplateItems.

So far I have something like this: (I have the PackageID already at the start of the query).

 INSERT INTO EAItems(Description, Recommendation, HeadingID)
SELECT Description, Recommendation, HeadingID
FROM EATemplateItemsWHERE EATemplateItems.TemplateID = @.TemplateID
INSERT INTO EAPackageItems(ItemID, PackageID) ...

I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.Any Advice / help would rock! Thanks

I think you will want to do this as a stored procedure. As you insert an individual row you can use the @.@.IDENTITY variable for the last inserted row. You could save that to a variable and insert the record to the second table. In your first query you could adjust it to select the rows into a table variable and then loop over the rows in the table variable and use that loop to take care of your individual inserts.

The T-SQL snippet below is the basic structure for what I am describing.

DECLARE @.MyTableTABLE(IDint IDENTITY,Name varchar(20))INSERT INTO @.MyTable (Name)SELECT NameFROM OtherTableDECLARE @.CurIDintDECLARE @.MaxIDintDECLARE @.RowIDintSET @.MaxID = (SELECT MAX(ID)FROM @.MyTable )SET @.CurID = 1WHILE (@.CurID <= @.MaxID)BEGIN-- use CurID to access the row in @.MyTable-- do your insert-- get the @.@.IDENTITY-- use that value for the next insert-- be sure to increment the @.CurID to the next rowSET @.CurID = @.CurID + 1END

|||

Thanks for the reply.

I'll work with that when I get to work - it seems logically straight forward. The script you put down can work in both SQL 2000 and SQL 2005 right? I hope so :D

|||Yes, there is nothing specific in there for SQL Server 2005.

Help with query - anyone?

I have 3 tables,

first table is
CRT with crtID and crtNAME parameters, second table is
CRTP with crtpID and crtpNAME parameters and third is
FORM with crtpID and crtID parameters.

Third table joins parameters from other two whit their keys - crtpID
and crtID.

My question is how to make query to list crtNAME and crtpNAME in my
results??

For example FORM contains,

crtpID, crtID
001, 005
002, 005
003, 007
etc.

I want to list names associated to those ID-s joined in third table...

I hope that you understand me - thanks very much for any help.SELECT CRT.crtNAME, CRTP.crtpNAME
FROM CRT
JOIN FORM
ON CRT.crtID = Form.crtID
JOIN CRTP
ON Form.crtpID = CRTP.crtpID

Roy Harvey
Beacon Falls, CT

On 18 Aug 2006 00:57:21 -0700, "legenda" <dispet@.gmail.comwrote:

Quote:

Originally Posted by

>I have 3 tables,
>
>first table is
>CRT with crtID and crtNAME parameters, second table is
>CRTP with crtpID and crtpNAME parameters and third is
>FORM with crtpID and crtID parameters.
>
>Third table joins parameters from other two whit their keys - crtpID
>and crtID.
>
>My question is how to make query to list crtNAME and crtpNAME in my
>results??
>
>
>For example FORM contains,
>
>crtpID, crtID
>001, 005
>002, 005
>003, 007
>etc.
>
>I want to list names associated to those ID-s joined in third table...
>
>I hope that you understand me - thanks very much for any help.

Help with Query

I have two tables, one with all the items (iv00101) and one with history of sales per month (iv30102). I need to make a query where it displays the item number, the item description, and the sum of the sales of the months I select.

for example:

select a.itemnmbr as Number, a.itemdesc as Description, sum(b.smrysales) as Sales
from iv00101 as a left join iv30102 as b on a.itemnmbr = b.itemnmbr
where (b.month = 12 and b.year=2003)

that gives me no problem when the item has a sales history on december/2003. But if an item was created january/2004 and I make the same query, that item doesn't appear beacuse it has no sales history on december/2003. I need it to appear with the sum(b.smrysales) as Sales = 0

any ideas?change WHERE to AND so that the conditions involving b.month and b.year are part of the ON clause

Help with query

I'm not sure if this is the right forum but here goes:

I want to make a query that selects data from multiple tables and joins it all together. I have that but what i want to do is only select the data I need. at the moment it is returning columns that are not necessary. I'm trying to do something like this:

Code Snippet

SELECT
sysdba.OPPORTUNITY.OPPORTUNITYID AS OPPID
FROM sysdba.OPPORTUNITY
INNER JOIN sysdba.C_OPPTYINFO
ON sysdba.OPPORTUNITY.OPPORTUNITYID = sysdba.C_OPPTYINFO.OPPORTUNITYID

For some reason the Inner Join is not joining the two tables. Any one have any suggestions?

Thanks in advance for the help.

Nothing wrong with your query, the tables are properly joined -IF both have a column [OpportunityID].

However, are you sure that there is data in both tables with the exact same [OpportunityID]?

|||The limited information you gave makes it hard for us to answer your questions: Perhaps the OppurtunityId is not the only key needed to identify the matching rows in both tables ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with Query

I need help in generating a query that will give me all
my DB tables and associated columns.
Thanks
On Wed, 21 Apr 2004 12:35:42 -0700, Dave Lugo wrote:

>I need help in generating a query that will give me all
>my DB tables and associated columns.
>Thanks
Hi Dave,
Try the following:
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks. That worked out great.
What would I need to add to get the properties of the
column . ie. TABLE_NAME (numeric (10,0), Not Null
Thanks

>--Original Message--
>On Wed, 21 Apr 2004 12:35:42 -0700, Dave Lugo wrote:
>
>Hi Dave,
>Try the following:
>SELECT TABLE_NAME, COLUMN_NAME
>FROM INFORMATION_SCHEMA.COLUMNS
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>
|||On Thu, 22 Apr 2004 16:09:35 -0700, Dave Lugo wrote:

>Thanks. That worked out great.
>What would I need to add to get the properties of the
>column . ie. TABLE_NAME (numeric (10,0), Not Null
>Thanks
>
Hi Dave,
There are many more columns in the INFORMATION_SCHEMA.COLUMNS view. If
you use SELECT *, you'll see them all and you can check which you need
and which you don't need.
Alternatively, consult Books Online for more information on this view
(as well as other INFORMATION_SCHEMA-views).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

help with query

Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
================================================== ==
attempt at storedprocedure
================================================== ==
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
================================================== ==
table structure being used
================================================== ==
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
I think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan