Re: Trouble joining between 3 tables - Database Discussion Boards

Stephan Hoppe4-Jul-08 12:24

4-Jul-08 12:24

Hi guys,

I am trying to figure out how to solve this simple select statement, but somehow I am unable to get it all together. I simplified my problem which is like this. I have a unlimited number of persons and teams. Each person can be a member of one or more teams. I would like to figure out which person is a member of all teams. To make it easier for you to follow here is a SQL snippet to create sample tables and data

create table #person
(
	PersonId varchar(2),
	Firstname nvarchar(50)
)

create table #member
(
	PersonId varchar(2),
	TeamId varchar(2)
)

create table #team
(
	TeamId varchar(2),
	TeamName nvarchar(50)
)


insert into #person values ('P1', 'Wayne');
insert into #person values ('P2', 'Rick');
insert into #person values ('P3', 'Bob');

insert into #team values ('T1', 'Team 1');
insert into #team values ('T2', 'Team 2');
insert into #team values ('T3', 'Team 3');


insert into #member values ('P1', 'T1');
insert into #member values ('P2', 'T1');
insert into #member values ('P2', 'T2');
insert into #member values ('P2', 'T3');
insert into #member values ('P3', 'T3');

drop table #member
drop table #person
drop table #team

The only person in all teams is Rick.

Thanks for any kind of help on this matter.

Cheers!
Byteloser

P.S: By the way I am using SQL Server 2005

Re: select problem

leoinfo4-Jul-08 14:59

leoinfo

4-Jul-08 14:59

SELECT PersonId, Firstname  
FROM #person  
WHERE  
PersonId IN ( 
    SELECT PersonId 
    FROM #member 
    GROUP BY PersonId 
    HAVING COUNT(*) = (SELECT COUNT(*) FROM #team) 
  )

Re: select problem

Stephan Hoppe5-Jul-08 6:13

Stephan Hoppe

5-Jul-08 6:13

Hi,

Thanks for the reply. I came up with the same idea last night. Hope this works in my real work szenario, because I have a high data volume.

Cheers!
Stephan

How to get date only from the table

BalasubramanianK4-Jul-08 3:20

BalasubramanianK

4-Jul-08 3:20

Hi,

In my table, my date is look like '2007-10-01 08:01:14.543'. But I need '2007-10-01' only. How to use convert function to get this format? Pls help. tnx in adv.

Balasubramanian K.

Re: How to get date only from the table

leoinfo4-Jul-08 3:50

leoinfo

4-Jul-08 3:50

You have here some examples:

PRINT GETDATE()
--|OUTPUT:
--|Jul  4 2008  9:45AM
PRINT CONVERT(NVARCHAR, GETDATE(), 121)
--|OUTPUT:
--|2008-07-04 09:45:45.477
PRINT CONVERT(NVARCHAR(10), GETDATE(), 121)
--|OUTPUT:
--|2008-07-04
PRINT CONVERT(NVARCHAR, GETDATE(), 112)
--|OUTPUT:
--|20080704

You can find mode details here: http://msdn.microsoft.com/en-us/library/aa226054.aspx[^]

Trouble joining between 3 tables

dlarkin773-Jul-08 10:14

dlarkin77

3-Jul-08 10:14

Hi,

I have 3 tables : Products, Orders and OrderDetails.

The Products table has a ProductCode field
The Orders table has an AccountNumber, DeliveryDate field and an ID field
The OrderDetails has a foreign key that points to the ID field in Orders
The OrderDetails has a foreign key that points to the ProductCode in Products

I need to get a list of all ProductCodes

For each ProductCode I need to get the most recent Orders.DeliveryDate and OrderDetails.Quantity where Order.AccountNumber equals a given AccountNumber

For example assume each table contains the following records:

Products Table	

ProductCode	
A1			
B2		
C3		
D4
E5

OrderDetails Table

ID ProductCode Quantity
1  A1          1
1  B2          2
1  D4          4
2  C3          1
2  E5          2

Orders Table

ID AccountNumber DeliveryDate
1  1234          03/07/2008
2  6789          26/06/2008

Assume I want information for AccountNumber 1234. The results I need are as follows:

ProductCode DeliveryDate Quantity
A1          03/07/2008   1
B2          03/07/2008   2
C3          NULL         NULL
D4          03/07/2008   4
E5          NULL         NULL

Assume I want information for AccountNumber 6789. The results I need are as follows:

ProductCode DeliveryDate Quantity
A1          NULL         NULL
B2          NULL         NULL
C3          26/06/2008   1
D4          NULL         NULL
E5          26/06/2008   2

I know that I could get a datatable with a list of products and then write some code to loop through it and lookup Orders and OrderDetails to get the other two fields but I'd rather get all the information that I need in one call.

Any ideas how to go about writing the SQL Select statement?

Thanks,

dlarkin77

Re: Trouble joining between 3 tables

Paul Conrad3-Jul-08 11:15

Paul Conrad

3-Jul-08 11:15

dlarkin77 wrote:
go about writing the SQL Select statement?

Have you tried one? Try a query and if it doesn't work, ask for help.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: Trouble joining between 3 tables

Blue_Boy3-Jul-08 22:39

Blue_Boy

3-Jul-08 22:39

First result for accountnumber 1234

SELECT     OD.ProductCode, O.DeliveryDate, CASE WHEN O.DeliveryDate IS NULL THEN NULL ELSE OD.Quantity END AS quantity<br />
FROM         dbo.Products P INNER JOIN<br />
                      dbo.OrderDetails OD ON P.ProductCode = OD.ProductCode LEFT OUTER JOIN<br />
                      dbo.Orders O ON OD.ID = O.ID AND O.AccountNumber = 1234

Second result for accountnumber 6789

<br />
SELECT     OD.ProductCode, O.DeliveryDate, CASE WHEN O.DeliveryDate IS NULL THEN NULL ELSE OD.Quantity END AS quantity<br />
FROM         dbo.Products P INNER JOIN<br />
                      dbo.OrderDetails OD ON P.ProductCode = OD.ProductCode LEFT OUTER JOIN<br />
                      dbo.Orders O ON OD.ID = O.ID AND O.AccountNumber = 6789

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

RTF in Reporting services reports

AlterD3-Jul-08 9:14

AlterD

3-Jul-08 9:14

Hi to all,

Hope I'm posting this in the right discussion group.

I wonder if anyone can tell me if VS 2008 brought in support for rich text formatting or HTML in reports of Reporting Services?
I did see some reports on the possibility but would like to know for sure.

Hope you can help.

Kind regards
Andries

Help in get Time

Senthil S3-Jul-08 4:08

Senthil S

3-Jul-08 4:08

hi all,

we know getdate() function returns current date. I want get the current time.

Can any one help me...

Thanks in Advance

Senthil S

Senthil.S
Software Engineer

Re: Help in get Time

DerekFL3-Jul-08 4:39

DerekFL

3-Jul-08 4:39

SELECT RIGHT(CONVERT(varchar(23), GETDATE(), 8),11)

Re: Help in get Time

Blue_Boy3-Jul-08 22:43

Blue_Boy

3-Jul-08 22:43

select convert(varchar(15),getdate(),108)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

trigger instead of identity

Mr.Kode3-Jul-08 1:04

Mr.Kode

3-Jul-08 1:04

i have a column its values must started with 1 and incrementing by 1
this column repeatly looses his rows
so i can not use identity here
so i tried to raise a trigger to do the job
but i have some probelems with the following statments

create trigger dbo.insertNum
on testIcreTrigger --table name
for insert
AS
begin
declare @insVal int --value to insert
declare @MaxVal int
select @MaxVal= Max(Num) from hitTheatre
select @insVal=@MaxVal+1
insert into testIcreTrigger (Num)
values (@insVal)
End
Go

Re: trigger instead of identity

DerekFL3-Jul-08 4:43

DerekFL

3-Jul-08 4:43

Have you tried

SET @MaxVal= (select Max(Num) from hitTheatre)
SET @insVal= (@MaxVal+1)

Just guessing..

Re: trigger instead of identity

DerekFL3-Jul-08 4:55

DerekFL

3-Jul-08 4:55

Another solution is to run DBCC CHECKIDENT('[table name]', RESEED, 0) when you flush the table and just use an identity.

Re: trigger instead of identity

leoinfo4-Jul-08 4:26

leoinfo

4-Jul-08 4:26

You should replace *for insert* with *INSTEAD OF INSERT*.
When FOR is the only keyword specified, AFTER is used by default.

CREATE TRIGGER dbo.insertNum
ON testIcreTrigger  
INSTEAD OF INSERT 
AS
BEGIN
  INSERT INTO testIcreTrigger (Num)
  SELECT MAX(Num)+1 FROM hitTheatre
END

begin tran... commit and stored procedures/functions

blakey4043-Jul-08 0:21

blakey404

3-Jul-08 0:21

Quick question... i'm feeling a bit thick today...

Does...

Begin Tran
stored procedure 1
stored procedure 2
stored procedure 3
rollback

...work? ie are the contents of stored procedures also rolled back?

Re: begin tran... commit and stored procedures/functions

DerekFL3-Jul-08 4:52

DerekFL

3-Jul-08 4:52

Yes they are.

Re: begin tran... commit and stored procedures/functions

blakey4043-Jul-08 5:11

blakey404

3-Jul-08 5:11

magic. cheers.

Unable to view SQL Report with normal login

Ankur.Bakliwal3-Jul-08 0:09

Ankur.Bakliwal

3-Jul-08 0:09

Hello All,

Please help me..

using admin login report showing records.
but i m Unable to view SQL Report normal login

is there any solution...

Thanks,
Ankur Bakliwal

installation

Kissy162-Jul-08 23:50

Kissy16

2-Jul-08 23:50

Hai all,
Can i install sqlserver2000 and 2005 in same folder.
I have already installed sqlserver2000 in my XP,and i want to install sqlserver2005.
I dont if it raises any problem or not,if not,wat kind of things i have to consider.
Give me some answer.

can i install in different folders.my sqlserver2000 and vs2003 are installed in C:/
and vs2005 in D:/,now where i have to install my sqlserver2005.

kissy

Re: installation

John_Adams3-Jul-08 5:35

John_Adams

3-Jul-08 5:35

Hi,

I am not sure whether SQLServer 2000 and SQLServer 2005 can be installed in same folder. But they can be installed on same machine in different folders. However, you can not have two default instances of any version of SQL Server but you can have ten different installations of SQLServer 2000 and SQLServer 2005 as long as they are all named differently, they can co-exist with out an issue.

Hope this helps.

Regards,
John Adams
ComponentOne LLC

Stored Procedure or Trigger for Autogenerate Colunm

rrrriiizz2-Jul-08 20:45

rrrriiizz

2-Jul-08 20:45

Hello All,
Am creating a small Projects using Vb.Net and Sql server 2005, in that i want to get a input automatically for one column(Eno column), that column is a alphanumeric column, i want to store information automatically like below,

if the Employeename is 'Anton' it will automatically fill the Eno as A001, if Employeename is 'Brail' then it will fill 'Boo1' if again Employeename is 'Berg' then 'b002' and so on. can i get that by adding a trigger or stored Procedure, if so then tell simply code examle for this, this very urgent for me, so Pleeeeeeeeeeeeease help me.

Thanks
Riz

Re: Stored Procedure or Trigger for Autogenerate Colunm

Mycroft Holmes2-Jul-08 20:56

Mycroft Holmes

2-Jul-08 20:56

I am going to assume you are trying to create a key field for your employee, if so then:
DO NOT ADD INTELLIGENCE TO A PRIMARY ID/KEY FIELD, you are doing this by adding the first character of the surname. This is wrong, it should simply be the record identifier, use the surname field for search/sorting NOT the ID field.

Generating an employee number for human consumption (timecards or something) is a different matter. I would put it in the inserting stored procedure (I never use triggers)

Before the insert get the count of existing surnames with the 1st char
Select @N = (count(*) + 1) From Employee where Surname like 'A%' (this fails when the employee changes surnames - gets married and is the reason for the initial comment)
or
Select top 1 employee where ENO like 'A%'
order by ENO desc

Add the text to the ENO field

Never underestimate the power of human stupidity
RAH

Re: Stored Procedure or Trigger for Autogenerate Colunm

rrrriiizz2-Jul-08 21:34

rrrriiizz

2-Jul-08 21:34

Thank you somuch for your speedy reply. i will try the code and let you know the result, once again i thank you.

Thanks
RIZ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.