From dirk@code-shop.com Wed Dec  13 09:26:53 2006
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E71239FA433
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 13 Dec 2006 05:26:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 07749-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 13 Dec 2006 05:26:47 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 193DB9F9F46
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 05:26:47 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from mail.code-shop.com (lvps87-230-13-34.dedicated.hosteurope.de
	[87.230.13.34])
	by svr4.postgresql.org (Postfix) with ESMTP id 40B9E5AF67D
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 09:26:41 +0000 (GMT)
Received: from localhost (lvps87-230-13-34.dedicated.hosteurope.de
 [127.0.0.1])
	by mail.code-shop.com (Postfix) with ESMTP id AF0222442058
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 10:26:38 +0100 (CET)
Received: from mail.code-shop.com ([127.0.0.1])
	by localhost (lvps87-230-13-34.dedicated.hosteurope.de [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id XVv1LJPquVif for <pgsql-sql@postgresql.org>;
	Wed, 13 Dec 2006 10:26:38 +0100 (CET)
Received: from [20.20.20.13] (195-241-134-87-static.dsl.ip.tiscali.nl
	[195.241.134.87])
	by mail.code-shop.com (Postfix) with ESMTP id 3340F2442057
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 10:26:38 +0100 (CET)
Message-ID: <457FC751.6080104@code-shop.com>
Date: Wed, 13 Dec 2006 10:26:41 +0100
From: Dirk Griffioen <dirk@code-shop.com>
Organization: CodeShop
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: join and sort on 'best match'
Content-Type: multipart/alternative;
	boundary="------------030601010208030905020602"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/100
X-Sequence-Number: 27056

This is a multi-part message in MIME format.
--------------030601010208030905020602
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

  Hi Everybody,

I have been breaking my head on the following problem: how to join 2 
tables and sort the results on the best match.

explanation:

- there are 3 tables, items, tags and items_tags. The items_tags table 
links items to tags.
- I have one item which has certain tags, and I want to look up all the 
other items that have those tags as well
- results should be sorted and presented by 'best match': first all the 
items that have 3 tags in common, then 2 and last 1

example:

Item 1 : news, nature, greenpeace
Item 2 : news, nature
Item 3 : news, nature, greenpeace, whale

Item 1 and Item 3 are the best match.

So far, the SQL I came up wiht looks like:

SELECT id, COUNT(items_tags.item_id) AS quantity
FROM items JOIN items_tags ON items_tags.item_id = items.id
WHERE id in (select item_id from items_tags where tag_id in (select 
tag_id from items_tags where item_id=?))
GROUP BY items_tags.item_id,id
ORDER BY quantity DESC

note: the '?' in the query represents the dynamic part: I have 1 item 
and I want to look up matching items.

To me, this query means the following:

- get all items that have tags, the 'JOIN', and count the tags, but only 
those that match on the same tags, the 'WHERE'
- then show them

I thought I had found the solution (my test cases worked), but I now 
find cases that should be found by the query but are not.

Can anyone please help me?

Dirk



--------------030601010208030905020602
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-html" lang="x-unicode"> <font
 face="Helvetica, Arial, sans-serif">Hi Everybody,<br>
<br>
I have been breaking my head on the following problem: how to join 2
tables and sort the results on the best match.<br>
<br>
explanation:<br>
<br>
- there are 3 tables, items, tags and items_tags. The items_tags table
links items to tags.<br>
- I have one item which has certain tags, and I want to look up all the
other items that have those tags as well<br>
- results should be sorted and presented by 'best match': first all the
items that have 3 tags in common, then 2 and last 1<br>
<br>
example:<br>
<br>
<font face="Courier New, Courier, monospace">Item 1 : news, nature,
greenpeace
<br>
Item 2 : news, nature
<br>
Item 3 : news, nature, greenpeace, whale</font><br>
<br>
Item 1 and Item 3 are the best match.
<br>
<br>
So far, the SQL I came up wiht looks like:<br>
<br>
<small><font face="Courier New, Courier, monospace">SELECT id,
COUNT(items_tags.item_id) AS quantity <br>
FROM items JOIN items_tags ON items_tags.item_id = items.id <br>
WHERE id in (select item_id from items_tags where tag_id in (select
tag_id from items_tags where item_id=?)) <br>
GROUP BY items_tags.item_id,id <br>
ORDER BY quantity DESC </font></small><br>
<br>
note: the '?' in the query represents the dynamic part: I have 1 item
and I want to look up matching items.<br>
<br>
To me, this query means the following:<br>
<br>
- get all items that have tags, the 'JOIN', and count the tags, but
only those that match on the same tags, the 'WHERE'<br>
- then show them <br>
<br>
I thought I had found the solution (my test cases worked), but I now
find cases that should be found by the query but are not.<br>
<br>
Can anyone please help me?<br>
<br>
Dirk<br>
<br>
<br>
</font>
</div>
</body>
</html>

--------------030601010208030905020602--



From gnari@hive.is Wed Dec  13 10:29:26 2006
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 9139C9FA311
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 13 Dec 2006 06:29:26 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 28355-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 13 Dec 2006 06:29:18 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mx3.ipf.is (man.ipf.is [85.197.192.163])
	by postgresql.org (Postfix) with ESMTP id EB6869FA1DA
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 06:29:21 -0400 (AST)
Received: (qmail 26674 invoked by uid 1010); 13 Dec 2006 10:22:51 +0000
Received: from 88.149.106.19 by man (envelope-from <gnari@hive.is>,
	uid 1008) with qmail-scanner-1.25-st-qms
	(clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms.
	Clear:RC:1(88.149.106.19):SA:0(-2.5/4.5):.
	Processed in 2.63307 secs); 13 Dec 2006 10:22:51 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via man
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.5/4.5):.
	Processed in 2.63307 secs Process 26655)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx3.ipf.is with SMTP; 13 Dec 2006 10:22:48 +0000
Subject: Re: join and sort on 'best match'
From: Ragnar <gnari@hive.is>
To: Dirk Griffioen <dirk@code-shop.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <457FC751.6080104@code-shop.com>
References: <457FC751.6080104@code-shop.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Wed, 13 Dec 2006 10:29:47 +0000
Message-Id: <1166005787.6369.79.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.8.1 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/101
X-Sequence-Number: 27057

On mið, 2006-12-13 at 10:26 +0100, Dirk Griffioen wrote:

> I have been breaking my head on the following problem: how to join 2
> tables and sort the results on the best match.
  ^^^^^^^^^^^^^
> - there are 3 tables, items, tags and items_tags. The items_tags table
> links items to tags.
> - I have one item which has certain tags, and I want to look up all
> the other items that have those tags as well

looks to me like you want to join:
items->item_tags->tags->item_tags->items

so the basic select is:
SELECT *
  FROM      items AS i1
       JOIN items_tags AS it1 ON (it1.item_id = i1.id)
       JOIN tags AS t ON (t.tag_id = it1.tag_id)
       JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id)
       JOIN items AS i2 ON (i2.id = it2.item_id)
  WHERE i1.id=?

> - results should be sorted and presented by 'best match': first all
> the items that have 3 tags in common, then 2 and last 1

this would be: 

SELECT i1.id,i2.id,COUNT(*) as quantity
  FROM      items AS i1
       JOIN items_tags AS it1 ON (it1.item_id = i1.id)
       JOIN tags AS t ON (t.tag_id = it1.tag_id)
       JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id)
       JOIN items AS i2 ON (i2.id = it2.item_id)
  WHERE i1.id=?
  GROUP by i1.id,i2.id
  ORDER BY quantity DESC

> I thought I had found the solution (my test cases worked), but I now
> find cases that should be found by the query but are not.

if this does not work, please provide us with a counter example.

gnari





From ahlawat.ashish@gmail.com Thu Dec  14 13:11:03 2006
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 79EC79F9FE1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Dec 2006 09:11:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 11114-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Dec 2006 09:10:51 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172])
	by postgresql.org (Postfix) with ESMTP id 834F99F9F7B
	for <pgsql-sql@postgresql.org>; Thu, 14 Dec 2006 09:10:56 -0400 (AST)
Received: by ug-out-1314.google.com with SMTP id k3so486925ugf
	for <pgsql-sql@postgresql.org>; Thu, 14 Dec 2006 05:10:54 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=dTtCuJYpe5eIsiha4sLH+5csedwcJw/IchNKmQmz5uN6OA/lU00r2l9qEKmra0lLStCUm31pjFNqBgz7H8Gwjh6G8xRtMrkoGfORxR1eHjzbtJMrCGW4Mxfxj2Bomex8qU2d8WeM3Nok0JiCceODut5PEbdDnC/pgETVisW8PbU=
Received: by 10.78.164.13 with SMTP id m13mr673178hue.1166101853969;
	Thu, 14 Dec 2006 05:10:53 -0800 (PST)
Received: by 10.78.150.17 with HTTP; Thu, 14 Dec 2006 05:10:53 -0800 (PST)
Message-ID: <682126990612140510j61d2268fq80235b02a550eccd@mail.gmail.com>
Date: Thu, 14 Dec 2006 18:40:53 +0530
From: "Ashish Ahlawat" <ahlawat.ashish@gmail.com>
To: Ragnar <gnari@hive.is>
Subject: Re: join and sort on 'best match'
Cc: "Dirk Griffioen" <dirk@code-shop.com>, pgsql-sql@postgresql.org
In-Reply-To: <1166005787.6369.79.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_132671_8498241.1166101853931"
References: <457FC751.6080104@code-shop.com>
	<1166005787.6369.79.camel@localhost.localdomain>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/107
X-Sequence-Number: 27063

------=_Part_132671_8498241.1166101853931
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

hi pls tell me ----

if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need
join ??*

*Ashish*



On 12/13/06, Ragnar <gnari@hive.is> wrote:
>
> On mi=F0, 2006-12-13 at 10:26 +0100, Dirk Griffioen wrote:
>
> > I have been breaking my head on the following problem: how to join 2
> > tables and sort the results on the best match.
> ^^^^^^^^^^^^^
> > - there are 3 tables, items, tags and items_tags. The items_tags table
> > links items to tags.
> > - I have one item which has certain tags, and I want to look up all
> > the other items that have those tags as well
>
> looks to me like you want to join:
> items->item_tags->tags->item_tags->items
>
> so the basic select is:
> SELECT *
> FROM      items AS i1
>       JOIN items_tags AS it1 ON (it1.item_id =3D i1.id)
>       JOIN tags AS t ON (t.tag_id =3D it1.tag_id)
>       JOIN items_tags AS it2 ON (it2.tag_id =3D t.tag_id)
>       JOIN items AS i2 ON (i2.id =3D it2.item_id)
> WHERE i1.id=3D?
>
> > - results should be sorted and presented by 'best match': first all
> > the items that have 3 tags in common, then 2 and last 1
>
> this would be:
>
> SELECT i1.id,i2.id,COUNT(*) as quantity
> FROM      items AS i1
>       JOIN items_tags AS it1 ON (it1.item_id =3D i1.id)
>       JOIN tags AS t ON (t.tag_id =3D it1.tag_id)
>       JOIN items_tags AS it2 ON (it2.tag_id =3D t.tag_id)
>       JOIN items AS i2 ON (i2.id =3D it2.item_id)
> WHERE i1.id=3D?
> GROUP by i1.id,i2.id
> ORDER BY quantity DESC
>
> > I thought I had found the solution (my test cases worked), but I now
> > find cases that should be found by the query but are not.
>
> if this does not work, please provide us with a counter example.
>
> gnari
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

------=_Part_132671_8498241.1166101853931
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<div>hi pls tell me ----</div>
<div>&nbsp;</div>
<div>if table <font face=3D"Courier New" color=3D"#3333ff"><strong>Item 3 :=
 news, nature, greenpeace, whale <font color=3D"#000000">has all clmn y v n=
eed join ??</font></strong></font></div>
<p><strong><font face=3D"Courier New">Ashish</font></strong></p>
<div><br><br>&nbsp;</div>
<div><span class=3D"gmail_quote">On 12/13/06, <b class=3D"gmail_sendername"=
>Ragnar</b> &lt;<a href=3D"mailto:gnari@hive.is">gnari@hive.is</a>&gt; wrot=
e:</span>
<blockquote class=3D"gmail_quote" style=3D"PADDING-LEFT: 1ex; MARGIN: 0px 0=
px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">On mi=F0, 2006-12-13 at 10:26 +0=
100, Dirk Griffioen wrote:<br><br>&gt; I have been breaking my head on the =
following problem: how to join 2
<br>&gt; tables and sort the results on the best match.<br>^^^^^^^^^^^^^<br=
>&gt; - there are 3 tables, items, tags and items_tags. The items_tags tabl=
e<br>&gt; links items to tags.<br>&gt; - I have one item which has certain =
tags, and I want to look up all
<br>&gt; the other items that have those tags as well<br><br>looks to me li=
ke you want to join:<br>items-&gt;item_tags-&gt;tags-&gt;item_tags-&gt;item=
s<br><br>so the basic select is:<br>SELECT *<br>FROM&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;items AS i1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JOIN items_tags AS it1 ON (it1.item_id =
=3D <a href=3D"http://i1.id">i1.id</a>)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;JOIN tags AS t ON (t.tag_id =3D it1.tag_id)<br>&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;JOIN items_tags AS it2 ON (it2.tag_id =3D t.tag_id)<br>&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;JOIN items AS i2 ON (
<a href=3D"http://i2.id">i2.id</a> =3D it2.item_id)<br>WHERE i1.id=3D?<br><=
br>&gt; - results should be sorted and presented by 'best match': first all=
<br>&gt; the items that have 3 tags in common, then 2 and last 1<br><br>thi=
s would be:
<br><br>SELECT <a href=3D"http://i1.id">i1.id</a>,<a href=3D"http://i2.id">=
i2.id</a>,COUNT(*) as quantity<br>FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i=
tems AS i1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JOIN items_tags AS it1 ON=
 (it1.item_id =3D <a href=3D"http://i1.id">i1.id</a>)<br>&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;JOIN tags AS t ON (
t.tag_id =3D it1.tag_id)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JOIN items_=
tags AS it2 ON (it2.tag_id =3D t.tag_id)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;JOIN items AS i2 ON (<a href=3D"http://i2.id">i2.id</a> =3D it2.item_i=
d)<br>WHERE i1.id=3D?<br>GROUP by <a href=3D"http://i1.id">i1.id</a>
,<a href=3D"http://i2.id">i2.id</a><br>ORDER BY quantity DESC<br><br>&gt; I=
 thought I had found the solution (my test cases worked), but I now<br>&gt;=
 find cases that should be found by the query but are not.<br><br>if this d=
oes not work, please provide us with a counter example.
<br><br>gnari<br><br><br><br>---------------------------(end of broadcast)-=
--------------------------<br>TIP 4: Have you searched our list archives?<b=
r><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;<a href=3D"http://archives.postgresql.org">http://archives.p=
ostgresql.org
</a><br></blockquote></div><br>

------=_Part_132671_8498241.1166101853931--



From schabi@logix-tt.com Thu Dec  14 15:37:14 2006
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id BB79B9FA19B
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Dec 2006 11:37:14 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 03879-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Dec 2006 11:37:04 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mail.logix-tt.com (mail.logix-tt.com [212.211.145.186])
	by postgresql.org (Postfix) with ESMTP id 6E76C9FA178
	for <pgsql-sql@postgresql.org>; Thu, 14 Dec 2006 11:37:05 -0400 (AST)
Received: from kingfisher.intern.logix-tt.com
	(84-72-188-217.dclient.hispeed.ch [84.72.188.217])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.logix-tt.com (Postfix) with ESMTP id 929F164BFA
	for <pgsql-sql@postgresql.org>; Thu, 14 Dec 2006 16:39:59 +0100 (CET)
Received: from kingfisher.sec.intern.logix-tt.com (localhost [127.0.0.1])
	by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 28478181C1B6B
	for <pgsql-sql@postgresql.org>; Thu, 14 Dec 2006 16:37:01 +0100 (CET)
Date: Thu, 14 Dec 2006 16:36:59 +0100
From: Markus Schaber <schabi@logix-tt.com>
To: pgsql-sql@postgresql.org
Subject: Re: join and sort on 'best match'
Message-ID: <20061214163659.088b7236@kingfisher.sec.intern.logix-tt.com>
In-Reply-To: <682126990612140510j61d2268fq80235b02a550eccd@mail.gmail.com>
References: <457FC751.6080104@code-shop.com>
	<1166005787.6369.79.camel@localhost.localdomain>
	<682126990612140510j61d2268fq80235b02a550eccd@mail.gmail.com>
Organization: www.logix-tt.com
X-Mailer: Sylpheed-Claws 2.6.0 (GTK+ 2.8.20; i486-pc-linux-gnu)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/108
X-Sequence-Number: 27064

Hi, Ashish,

"Ashish Ahlawat" <ahlawat.ashish@gmail.com> wrote:

> hi pls tell me ----
> 
> if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need
> join ??*

Please try to write in English, so we can understand and answer your
questions.



Regards,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org



From ahlawat.ashish@gmail.com Fri Dec  15 16:40:35 2006
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 1A6E79FA2FD
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 12:40:35 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 04794-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 12:40:23 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.189])
	by postgresql.org (Postfix) with ESMTP id 813D19FA2A7
	for <pgsql-sql@postgresql.org>; Fri, 15 Dec 2006 12:40:23 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id x4so1292943nfb
	for <pgsql-sql@postgresql.org>; Fri, 15 Dec 2006 08:40:22 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=ARZ6t34+GO1tUAQQO53hKs9xYlzF0QpTKcqIEM5YISrXew7sUD2N+u5VfuzyqhZnrCu4SQKsPFHNPt/UpJTyfNPLeXU2h+rmuJwIOelWxqyJfi5lwRmyWygXQ0Tnh1NjrBkHONf6JdIzBygIM7BxabsJFrfTeJbHebZWzEjrnNA=
Received: by 10.48.210.20 with SMTP id i20mr2836616nfg.1166200822134;
	Fri, 15 Dec 2006 08:40:22 -0800 (PST)
Received: by 10.78.150.17 with HTTP; Fri, 15 Dec 2006 08:40:21 -0800 (PST)
Message-ID: <682126990612150840r409ada5y234ff3ef3ac85c5f@mail.gmail.com>
Date: Fri, 15 Dec 2006 22:10:22 +0530
From: "Ashish Ahlawat" <ahlawat.ashish@gmail.com>
To: "Markus Schaber" <schabi@logix-tt.com>
Subject: Re: join and sort on 'best match'
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20061214163659.088b7236@kingfisher.sec.intern.logix-tt.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_153179_6198842.1166200821999"
References: <457FC751.6080104@code-shop.com>
	<1166005787.6369.79.camel@localhost.localdomain>
	<682126990612140510j61d2268fq80235b02a550eccd@mail.gmail.com>
	<20061214163659.088b7236@kingfisher.sec.intern.logix-tt.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/119
X-Sequence-Number: 27075

------=_Part_153179_6198842.1166200821999
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi team

I was just asking that If table ITEM3 has all the columns then why we need
to have a join ?? even we put a sorting very easily.


*** Any way  I have a very intersting question to all of you. I want to
fetch more that 70,000 BLOB from different customer servers. the issue is
there are some BOLB files with common names on all servers. So I want merge
these files into a single BLOB during  fetching data. I am able to fetch
the  BLOB data from all cust servers but unfortunatelly it overwrite
previous file.

So pls provide any simple query format for the same, assuming two table tab1
& tab 2.

Ashish  ............. INDIA



On 12/14/06, Markus Schaber <schabi@logix-tt.com> wrote:
>
> Hi, Ashish,
>
> "Ashish Ahlawat" <ahlawat.ashish@gmail.com> wrote:
>
> > hi pls tell me ----
> >
> > if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need
> > join ??*
>
> Please try to write in English, so we can understand and answer your
> questions.
>
>
>
> Regards,
> Markus
>
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf.     | Software Development GIS
>
> Fight against software patents in Europe! www.ffii.org
> www.nosoftwarepatents.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

------=_Part_153179_6198842.1166200821999
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>&nbsp;</div>
<div>&nbsp;</div>
<div>Hi team</div>
<div>&nbsp;</div>
<div>I was just asking that If table ITEM3 has all the columns then why we need to have a join ?? even we put a sorting very easily.</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div>*** Any way&nbsp; I have a very intersting question to all of you. I want to fetch more that 70,000 BLOB from different customer servers. the issue is there are some BOLB files with common names on all servers. So I want merge these files into a single BLOB&nbsp;during &nbsp;fetching data. I am able to fetch the&nbsp; BLOB data from all cust servers but unfortunatelly it overwrite previous file.
</div>
<div>&nbsp;</div>
<div>So pls provide any simple query format&nbsp;for the same, assuming two table tab1 &amp; tab 2.</div>
<div>&nbsp;</div>
<div>Ashish&nbsp; ............. INDIA</div>
<div><br><br>&nbsp;</div>
<div><span class="gmail_quote">On 12/14/06, <b class="gmail_sendername">Markus Schaber</b> &lt;<a href="mailto:schabi@logix-tt.com">schabi@logix-tt.com</a>&gt; wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Hi, Ashish,<br><br>&quot;Ashish Ahlawat&quot; &lt;<a href="mailto:ahlawat.ashish@gmail.com">ahlawat.ashish@gmail.com
</a>&gt; wrote:<br><br>&gt; hi pls tell me ----<br>&gt;<br>&gt; if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need<br>&gt; join ??*<br><br>Please try to write in English, so we can understand and answer your
<br>questions.<br><br><br><br>Regards,<br>Markus<br><br>--<br>Markus Schaber | Logical Tracking&amp;Tracing International AG<br>Dipl. Inf.&nbsp;&nbsp;&nbsp;&nbsp; | Software Development GIS<br><br>Fight against software patents in Europe! <a href="http://www.ffii.org">
www.ffii.org</a><br><a href="http://www.nosoftwarepatents.org">www.nosoftwarepatents.org</a><br><br>---------------------------(end of broadcast)---------------------------<br>TIP 2: Don't 'kill -9' the postmaster<br></blockquote>
</div><br>

------=_Part_153179_6198842.1166200821999--



From gnari@hive.is Fri Dec  15 19:45:55 2006
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 3CCDA9FA304
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 15:45:55 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 84164-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 15:45:48 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mx3.ipf.is (man.ipf.is [85.197.192.163])
	by postgresql.org (Postfix) with ESMTP id C12EA9FA4CF
	for <pgsql-sql@postgresql.org>; Fri, 15 Dec 2006 15:45:49 -0400 (AST)
Received: (qmail 15355 invoked by uid 1010); 15 Dec 2006 19:39:08 +0000
Received: from 88.149.106.19 by man (envelope-from <gnari@hive.is>,
	uid 1008) with qmail-scanner-1.25-st-qms
	(clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms.
	Clear:RC:1(88.149.106.19):SA:0(-2.5/4.5):.
	Processed in 3.478408 secs); 15 Dec 2006 19:39:08 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via man
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.5/4.5):.
	Processed in 3.478408 secs Process 15335)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx3.ipf.is with SMTP; 15 Dec 2006 19:39:04 +0000
Subject: Re: join and sort on 'best match'
From: Ragnar <gnari@hive.is>
To: Ashish Ahlawat <ahlawat.ashish@gmail.com>
Cc: Markus Schaber <schabi@logix-tt.com>, pgsql-sql@postgresql.org
In-Reply-To: <682126990612150840r409ada5y234ff3ef3ac85c5f@mail.gmail.com>
References: <457FC751.6080104@code-shop.com>
	<1166005787.6369.79.camel@localhost.localdomain>
	<682126990612140510j61d2268fq80235b02a550eccd@mail.gmail.com>
	<20061214163659.088b7236@kingfisher.sec.intern.logix-tt.com>
	<682126990612150840r409ada5y234ff3ef3ac85c5f@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Fri, 15 Dec 2006 19:46:47 +0000
Message-Id: <1166212007.6369.194.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.8.1 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/120
X-Sequence-Number: 27076

On fös, 2006-12-15 at 22:10 +0530, Ashish Ahlawat wrote:
>  
>  
> Hi team
>  
> I was just asking that If table ITEM3 has all the columns then why we
> need to have a join ?? even we put a sorting very easily.

> On 12/14/06, Markus Schaber <schabi@logix-tt.com> wrote: 
>         Hi, Ashish,
>         
>         "Ashish Ahlawat" <ahlawat.ashish@gmail.com> wrote:
>         
>         > if table *Item 3 : news, nature, greenpeace, whale has all
>         clmn y v need
>         > join ??*

it seems these where not columns of a table.

the organisation was like this:

table items:

id   name
-----------
1    foo
2    bar
3    item3


table tags:

tag_id   text
---------------------
1        news
2        nature
3        tennisballs
4        greenpeace
5        cannibals
6        whale

and table items_tags:

item_id  tag_id
----------------
3        1
3        2
3        4
3        6
...


so table items needs to be joined to table tags via 
table items_tags to retrieve the connections from item3
to news, nature, greepeace and whales.

gnari