Combining AND & OR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining AND & OR

Author

Topic

codrrodd
Starting Member

3 Posts

Posted - 2008-12-07 : 12:43:07

I have this working but it only inserts just the 1 row into another table if the person has more than one of the below items (shown in my code below), i want to be able to be able for this code to be inserted into the table IF they have 1 OR more of the items. I have another table which has all the information and i want the below code to insert the people with the items into another table called item_list, the table with all the info is called item_CHECK. So how would i be able to do that with my code below?

My code line is something like this

[CODE]
ALTER PROCEDURE [dbo].[insertinto55]
@id char(21)

AS

DELETE FROM item_list WHERE strUserID = @Id
DECLARE @ITEMZ CHAR(50)

SET @ITEMZ = (select max(name) from item_check WHERE struserid = @id and (name = 'box'
or name = 'card'
or name = 'keyboard'
or name = 'monitor'
or name = 'desk'
or name = 'mouse' ))

IF @ITEMZ = @ITEMZ

BEGIN

INSERT INTO item_list (strUserID, name) VALUES (@Id, @itemz)
END

[/CODE]

I know it's a pretty simple code, but i just don't know how to make this code insert into the item_list table if they have 1 or more items of this, because right now it just inserts just the 1 row even if they have more than 1, so help me out please - i'm new at sql if you can't tell alrdy :P

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-07 : 16:21:51

[code]ALTER PROCEDURE dbo.insertinto55
(
@id char(21)
)
AS

SET NOCOUNT ON

DELETE
FROM item_list
WHERE strUserID = @Id

INSERT item_list
(
strUserID,
name
)
select struserid,
max(name)
from item_check
WHERE struserid = @id
and name in ('box', 'card', 'keyboard', 'monitor', 'desk', 'mouse')
group by struserid[/code]

E 12°55'05.63"
N 56°04'39.26"

codrrodd
Starting Member

3 Posts

Posted - 2008-12-07 : 18:13:03

Ty for taking the time to do this, but that code you posted is doing the exact same thing as the code i orginally posted, i have a couple of people with more than 1 of those items in the item_check table but it only inserts just the first find entering only 1 row and not entering all the items into the table that the person has. I need this code to insert all the found items into the item_list as right now it's only inserting one.

Still needing help on this one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 20:44:28

[code]ALTER PROCEDURE [dbo].[insertinto55]
@id char(21)

AS

DELETE FROM item_list WHERE strUserID = @Id

INSERT INTO item_list (strUserID, name)
select @id,name from item_check WHERE struserid = @id and (name = 'box'
or name = 'card'
or name = 'keyboard'
or name = 'monitor'
or name = 'desk'
or name = 'mouse' ))

GO[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 02:31:58

No, they are not the same.
You are ALWAYS inserting a reoord into target table, I only insert when there is a match.

E 12°55'05.63"
N 56°04'39.26"

codrrodd
Starting Member

3 Posts

Posted - 2008-12-08 : 13:31:25

Ty visakh16, that does the trick, but now i've came across another problem, how do i stop inserting duplicate rows?



ALTER PROCEDURE [dbo].[itembatch]

AS
declare @ID varchar(21)

DELETE FROM office_user WHERE strUserID = @Id

INSERT INTO office_user (strUserID)

SELECT STRUSERID FROM item_list

That's my code, and i want it to be able to just put the struserid into the table office_user without putting in duplicate rows of the same struserid over and over, as i have more than 1 of the same user in the item_list table. Will appreciate any help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 14:06:35

INSERT INTO office_user (strUserID)
SELECT top 1 STRUSERID FROM item_list

INSERT INTO office_user (strUserID)
SELECT distinct STRUSERID FROM item_list

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources