pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin and sort on 'best match'
6+ messages / 4 participants
[nested] [flat]
* join and sort on 'best match'
@ 2006-12-13 09:26 Dirk Griffioen <dirk@code-shop.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dirk Griffioen @ 2006-12-13 09:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: join and sort on 'best match'
@ 2006-12-13 10:29 Ragnar <gnari@hive.is>
parent: Dirk Griffioen <dirk@code-shop.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ragnar @ 2006-12-13 10:29 UTC (permalink / raw)
To: Dirk Griffioen <dirk@code-shop.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: join and sort on 'best match'
@ 2006-12-14 13:10 Ashish Ahlawat <ahlawat.ashish@gmail.com>
parent: Ragnar <gnari@hive.is>
0 siblings, 1 reply; 6+ messages in thread
From: Ashish Ahlawat @ 2006-12-14 13:10 UTC (permalink / raw)
To: Ragnar <gnari@hive.is>; +Cc: Dirk Griffioen <dirk@code-shop.com>; pgsql-sql
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ð, 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: join and sort on 'best match'
@ 2006-12-14 15:36 Markus Schaber <schabi@logix-tt.com>
parent: Ashish Ahlawat <ahlawat.ashish@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Markus Schaber @ 2006-12-14 15:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: join and sort on 'best match'
@ 2006-12-15 16:40 Ashish Ahlawat <ahlawat.ashish@gmail.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ashish Ahlawat @ 2006-12-15 16:40 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: join and sort on 'best match'
@ 2006-12-15 19:46 Ragnar <gnari@hive.is>
parent: Ashish Ahlawat <ahlawat.ashish@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Ragnar @ 2006-12-15 19:46 UTC (permalink / raw)
To: Ashish Ahlawat <ahlawat.ashish@gmail.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-12-15 19:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-13 09:26 join and sort on 'best match' Dirk Griffioen <dirk@code-shop.com>
2006-12-13 10:29 ` Ragnar <gnari@hive.is>
2006-12-14 13:10 ` Ashish Ahlawat <ahlawat.ashish@gmail.com>
2006-12-14 15:36 ` Markus Schaber <schabi@logix-tt.com>
2006-12-15 16:40 ` Ashish Ahlawat <ahlawat.ashish@gmail.com>
2006-12-15 19:46 ` Ragnar <gnari@hive.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox