No duplicate record from joined table

Hi,
I have 2 tables on DB2 version 6

Tab_A
----
id name
1 shop1
2 shop2

Tab_B
--------
id_tab_a keyword
1 book
1 bookstore
1 handbook
2 restaurant

I need to write a SQL query that find who have 'book' as keyword on Tab_B, but with no duplicated record. If i write:

Select distinct id, name left join Tab_B on id=id_tab_a where
keyword like '%book%'

obtain 3 "duplicated" records.

Any ideas?
Thanks,
JhonOriginally posted by fartman
Select distinct id, name left join Tab_B on id=id_tab_a where
keyword like '%book%'


You need to group this puppy

select id,name from tab_a
left join tab_b on id=id_tab_a
group by id,name

Best of luck
-Chris
Come read my SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html|||HI, the following query will help you

select id,name from tab_a
left join tab_b on id=id_tab_a where keyword like '%book%'
group by id,name;

Originally posted by christodd
You need to group this puppy

select id,name from tab_a
left join tab_b on id=id_tab_a
group by id,name

Best of luck
-Chris
Come read my SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html|||problem solved !!!
Thank you.

Jhonny

Originally posted by neelamchalam
HI, the following query will help you

select id,name from tab_a
left join tab_b on id=id_tab_a where keyword like '%book%'
group by id,name;

No Date passed

I want to update a record that has a datetime field in it. But what if there is no parameter given for that field, i.e. what is the correct type/value to pass as parameter?

Example: A DOB field for a user profile, but the user doesn't enter his birthday

This is some example code that I use to update:

 Private Sub UpdateDOB(ByVal dob As Date)
 Dim parameters As SqlParameter() = { _
 New SqlParameter("@.Birthday", SqlDbType.DateTime, 8)}
 parameters(0).Value = dob
 'Run Stored Procedure

This gives me the exception "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" when I don't pass a valid date. I tried passingNothing but same error (in this case it convert dob to "#12:00:00 AM#")

The only thing I can think of is to use Date.MaxValue and then check in application logic, but there must be a better way!use dbnull.value

hth|||ndinakar, I tried this:

User.DOB = System.DBNull.Value

but then I get an error, DBNull.Value cannot be converted to Type Date
how should i assign DBNull.Value ?|||Instead of passing DOB as date pass it as string

Private Sub UpdateDOB(ByVal strDOB As string)
...
cmd.Parameters.Add(New SqlParameter("@.Birthday", SqlDbType.DateTime))
If (strDOB= "") Then
cmd.Parameters("@.Date").Value = sqldatenull
'cmd.Parameters("@.Date").Value = DBNull.Value
Else
 cmd.Parameters("@.Date").Value = DateTime.Parse(strDOB)
End If

HTH|||or this :

myCommand.Parameters.Add(New SqlParameter("@.cusbday",SqlDbType.datetime))
If len(trim(bday.Text)) = 0 Then
 myCommand.Parameters("@.cusbday").Value = SqlDateTime.null
Else
 myCommand.Parameters("@.cusbday").Value = server.htmlencode(DateTime.Parse(bday.Text))
End If

you need to import system.data.sqldypes namespace.

hth|||Thank you

SqlDateTime.Null worked fine as parameter.sql

next/previous record

Hi. Is it possible in SQL query to find record previous or next in comparison with record found with clause WHERE (example of query below)? I need to find record with ProblemID less than or greater than 10. Regards Pawelek.

SELECT ProblemID
FROM dbo.tblProblems
WHERE (ProblemID = 10)

See if this article helps you:

http://www.xaprb.com/blog/2006/04/28/how-to-find-next-and-previous-records-in-sql/

|||

SELECT ProblemID
FROM dbo.tblProblems
WHERE (ProblemID <> 10)

HTH

Next/Prev record

If you have an identity column (i.e. 1-N) and you have 5, what is the most
efficent query to get the next (and prev) row? Gaps could happen so there
may not be a 6, but the next in order may be 8 for example. TIA
William Stacey [MVP]I usually use these:
Prev:
SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
Next:
SELECT TOP 1 key FROM table WHERE key > current ORDER BY key
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>|||Thanks Remus.
William Stacey [MVP]
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:euthl7jTGHA.2156@.tk2msftngp13.phx.gbl...
|I usually use these:
| Prev:
| SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
| Next:
| SELECT TOP 1 key FROM table WHERE key > current ORDER BY key|||William
create table William(c1 int NOT NULL primary key)
go
insert into William values (1)
insert into William values (2)
insert into William values (3)
insert into William values (4)
insert into William values (5)
insert into William values (6)
declare @.d as int
set @.d=4
select top 1 *,(select top 1 * from William where c1 >@.d order by c1
asc)as n
from William where c1 <@.d order by c1 desc
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>

next record?

If I give a table an itemid, how do i ask for the next record? Previous record too?
Can I do that?
ThanksAre you using a dataset? If so, you just index the Rows property of tables[0] (presuming a single table in the database).|||I thought of that, but I am not sure it will be the best for this situation. Maybe it is.
I have a photo gallery control. When a photo is clicked, a popup window is opened and takes the photoid from the querystring and displays that photo.
I would like to add a previous and next button on the popup page to scroll through the images in the gallery. The only things I am concerned about is starting at the right photo in the dataset and when i get to the last record, it should loop through to the first record if the next button is clicked.
Does this sound like something I can do with a dataset?
Thanks

Newbie: Updating record with User information

Hello,

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd like to
also update this value when the record is changed. If I understand things
correctly, I'll need a trigger to do this, but I've never created a trigger
before. Is this an easy thing to accomplish?

Thanks!

RickRico (you@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd
like to also update this value when the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol

SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)

The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.

A trigger fires once per statement, so there can be many rows in these
tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That's excellent! Thank you very much for your help Erland, that was
exactly what I needed! (that will make coding in VB much easier. ;)

Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98ADF09B38EBDYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (you@.me.com) writes:

Quote:

Originally Posted by

>I have a table that has a UserUpdated field which has a default value of
>SUSER_SNAME(). This works great when the record is created, but I'd
>like to also update this value when the record is changed. If I
>understand things correctly, I'll need a trigger to do this, but I've
>never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||On Jan 3, 4:38 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Rico (y...@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdatedfieldwhich has adefaultvalueof
SUSER_SNAME(). This works great when the record is created, but I'd
like to alsoupdatethisvaluewhen the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATEASUPDATEtbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I've come across this same issue...so I basically have to create a
trigger for each table in my database to update the username/modified
fields on each update. Is there a way to create a global update
trigger that will fire when any table with those fields is updated?

Thanks.
Rayne|||Rayne (wifetalks@.gmail.com) writes:

Quote:

Originally Posted by

I've come across this same issue...so I basically have to create a
trigger for each table in my database to update the username/modified
fields on each update. Is there a way to create a global update
trigger that will fire when any table with those fields is updated?

No, but you could write a program to generate them.

You could also consider third-party tools ApexSql (www.apexsql.com)
has SQL Audit, for instance. I have not tried it myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

newbie: processing records sequentially

Hi to all,
I'm a newbie to T-SQL but not to programming. What I want to do is
process every record in a table, and depending on the values in the
record itself, either copy the record to another table or update a
third table, and the delete the original record. Basically, it's a data
preparation area table where the records are imported with no error
check, and then validated against a set of conditions.

THE QUESTION IS: which approach should I follow? Is there a sort of
FORALL loop in T-SQL that I can execute on every record in the table?
Or shall I rely on other tools (VB Scripting, or...)? SQL Server 2000

Thanx to allIn SQL your goal should generally be to avoid processing data a row at
a time. This example may help you:

INSERT INTO AnotherTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE ... ? /* unspecified */

UPDATE AnotherTable
SET ... ? /* unspecified */
WHERE ... /* uspecified */

DELETE FROM YourTable
WHERE ...

etc.

Hopefully this gives you the idea. If you need more help, please refer
to the following article which describes the best way to post a fuller
description of your problem:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||David,
thanks for prompt response. I understand the process is somehow
reversed from a row-by-row cycle. It seems to me somehow inefficient,
though, to run a query against a third table twice in order to UPDATE
the first time and DELETE the second.

Cheers

David Portas wrote:
> In SQL your goal should generally be to avoid processing data a row at
> a time. This example may help you:
> INSERT INTO AnotherTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE ... ? /* unspecified */
> UPDATE AnotherTable
> SET ... ? /* unspecified */
> WHERE ... /* uspecified */
> DELETE FROM YourTable
> WHERE ...
> etc.
> Hopefully this gives you the idea. If you need more help, please refer
> to the following article which describes the best way to post a fuller
> description of your problem:
> http://www.aspfaq.com/etiquette.asp?id=5006|||It depends. INSERT, DELETE and UPDATE are separate operations so will
always require separate statements whatever method you use. The main
question is how best to optimise the WHERE selection criteria. You
didn't specify what those criteria are so I can only guess. It is at
least likely that putting the criteria in WHERE clauses, with suitable
indexes, will be much more efficient than the overhead of looping
through each row in a cursor. Understand that SQL is a declarative
language, unlike procedural languages that you are probably more
familiar with.Cursor processing (row by row) is rarely a good idea in
SQL and when manipulating data should usually be a last resort only.

--
David Portas
SQL Server MVP
--|||>> I'm a newbie to T-SQL but not to programming.<<

You missed the point of a declarative, set-oriented language and it
will take you about a year to un-learn your old mental models. I have
a book entitled SQL PROGRAMMING STYLE that deals with this problem in a
few chapters.

>> What I want to do is process every record [sic] in a table, and
depending on the values in the record [sic] itself, either copy the
record [sic] to another table or update a third table, and the delete
the original record [sic] . <<

This is a common problem; rows are not records, tables are not files
and columns are not fields. Totally differrent concepts. I will give
you the long painful details that I post on this topic if you ask.
Have you ever worked with LISP, FP, Prolog or a language that was not
based on procedures? This is a new way of thinking.

>> Basically, it's a data preparation area table where the records
[sic] are imported with no error check, and then validated against a
set of conditions. <<

This is usually done with an ETL tool these days, but you can do this
with a few statements. If you have an implementation of the new SQL-99
MERGE statement, then you can probably do it in one statement. It will
be a very complex statement, tho :)

BEGIN
UPDATE RawData
SET <column edits>;
DELETE GoodStuff
WHERE EXISTS
(SELECT *
FROM RawData
WHERE <<conditions>>;
INSERT INTO GoodStuff
SELECT a, b, c, ..
FROM RawData
WHERE <<conditions>>;
END;

First scrub the raw data, then remove the old data that is going to be
replaced, and finally insert the new clean data.|||moco (makelovenotspamdaldegan@.email.it) writes:
> thanks for prompt response. I understand the process is somehow
> reversed from a row-by-row cycle. It seems to me somehow inefficient,
> though, to run a query against a third table twice in order to UPDATE
> the first time and DELETE the second.

And with a loop there will be many queries against the table.

But as they say, seeing is believing. Go ahead and bench-mark. You will
be amazed...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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