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 Changes?

Hello All!
Im reading a book on SQL Server, and it says it is possible to set a column
with 'No Changes' property.
I have searched google and i cant seen to find where to set it, can u guys
help me out here?
Thanks,
Bruno NThere's no such property in SQL Server. Perhaps you misinterpreted the text,
or perhaps the authors
is mistaken. You can accomplish the same effect using a trigger, however.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bruno N" <nylren@.hotmail.com> wrote in message news:%23MIwiTkKFHA.3500@.TK2MSFTNGP14.phx.gb
l...
> Hello All!
> Im reading a book on SQL Server, and it says it is possible to set a colum
n with 'No Changes'
> property.
> I have searched google and i cant seen to find where to set it, can u guys
help me out here?
> Thanks,
> Bruno N
>

Newbie: please help with NS 2000 runtime error

We have a new big project coming, which will use Notification Services.

So, I bought a book: "Microsoft SQL Server 2000 Notification Services" by Shyam Pather and did all steps including installing NS, setting up the environment, and creating the first sample NS application (Chapter03). Every single step was successful, no errors. I am pretty sure I didn't miss anything.

All went well until I copied a sampla data file into the EventsWatchDirectory.

At this point, the file name almost immediately changes to EventData.xml.20060908-160948.370.err (digits are different at every attempt, but please note the extension).

I open this file using Notepad, and it is the exact copy of the original .xml file:

<eventData>
<StockPriceChange>
<StockSymbol>XYZ</StockSymbol>
<StockPrice>55.55</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>PQS</StockSymbol>
<StockPrice>95.3</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>JKL</StockSymbol>
<StockPrice>15.0</StockPrice>
</StockPriceChange>
</eventData>

The EventViewer has the following message:

<NotificationServicesEvent>

<Description>

The SQL XML Bulk Loader could not be initialized. Ensure that it is installed or repair its installation.

</Description>

<Context>

</Context>

<Description>

Retrieving the COM class factory for component with CLSID {1DB51355-B2CA-43CB-B045-1FAA42A724B2} failed due to the following error: 80040154.

</Description>

<Instance>Chapter03</Instance>

<ApplicationName>Stock</ApplicationName>

<Component>Event collection</Component>

<Thread>16</Thread>

</NotificationServicesEvent>

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

The link did not have info on this error.

My environment is as follows:

XP Pro SP2; SQL Server 2000 Dev SP4; SQL Server NS 2.0 SP1; SQLXML3.0 SP3.

Could someone kindly point me to the right direction as to what's wrong, please?

To my great regret, I found (by trial and error all over the past weekend) what was conflicting with the NS:

it's the .NET Framework 2.0.

As soon as I uninstalled it, the sample application started working as expected (that is, the file's extension in the Notifications directory changes to ".done").

...which means I won't be able to use VS 2005 and SQL Server 2005 on the same dev machine with NS 2000.

|||

Although I don't doubt what you observed, you should be able to run a SQLNS instance on a machine that has v2.0 of the .NET framework installed.

Try this post and see if it helps...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_frm/thread/aa48d73a8ed227a1/19cdd27c687ca70d?lnk=gst&q=framework+&rnum=2#19cdd27c687ca70d

HTH...

Joe

|||

Joe, thank you very much!

I followed your advice, and got it to work.

Newbie: please help with NS 2000 runtime error

We have a new big project coming, which will use Notification Services.

So, I bought a book: "Microsoft SQL Server 2000 Notification Services" by Shyam Pather and did all steps including installing NS, setting up the environment, and creating the first sample NS application (Chapter03). Every single step was successful, no errors. I am pretty sure I didn't miss anything.

All went well until I copied a sampla data file into the EventsWatchDirectory.

At this point, the file name almost immediately changes to EventData.xml.20060908-160948.370.err (digits are different at every attempt, but please note the extension).

I open this file using Notepad, and it is the exact copy of the original .xml file:

<eventData>
<StockPriceChange>
<StockSymbol>XYZ</StockSymbol>
<StockPrice>55.55</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>PQS</StockSymbol>
<StockPrice>95.3</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>JKL</StockSymbol>
<StockPrice>15.0</StockPrice>
</StockPriceChange>
</eventData>

The EventViewer has the following message:

<NotificationServicesEvent>

<Description>

The SQL XML Bulk Loader could not be initialized. Ensure that it is installed or repair its installation.

</Description>

<Context>

</Context>

<Description>

Retrieving the COM class factory for component with CLSID {1DB51355-B2CA-43CB-B045-1FAA42A724B2} failed due to the following error: 80040154.

</Description>

<Instance>Chapter03</Instance>

<ApplicationName>Stock</ApplicationName>

<Component>Event collection</Component>

<Thread>16</Thread>

</NotificationServicesEvent>

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

The link did not have info on this error.

My environment is as follows:

XP Pro SP2; SQL Server 2000 Dev SP4; SQL Server NS 2.0 SP1; SQLXML3.0 SP3.

Could someone kindly point me to the right direction as to what's wrong, please?

To my great regret, I found (by trial and error all over the past weekend) what was conflicting with the NS:

it's the .NET Framework 2.0.

As soon as I uninstalled it, the sample application started working as expected (that is, the file's extension in the Notifications directory changes to ".done").

...which means I won't be able to use VS 2005 and SQL Server 2005 on the same dev machine with NS 2000.

|||

Although I don't doubt what you observed, you should be able to run a SQLNS instance on a machine that has v2.0 of the .NET framework installed.

Try this post and see if it helps...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_frm/thread/aa48d73a8ed227a1/19cdd27c687ca70d?lnk=gst&q=framework+&rnum=2#19cdd27c687ca70d

HTH...

Joe

|||

Joe, thank you very much!

I followed your advice, and got it to work.