Re: [SQL] join and sort on 'best match'
Hi, Ashish, "Ashish Ahlawat" <[EMAIL PROTECTED]> 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
Re: [SQL] join and sort on 'best match'
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 <[EMAIL PROTECTED]> 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
Re: [SQL] join a lot of columns of two tables
On fim, 2006-12-14 at 12:01 +0100, ivan marchesini wrote: > Dear Postgres Users, > I have 2 tables... > each one has a column called ID (primary keys of each table) > the values into each ID column are exactly the same. > > each table has a lot of other columns (around 50 for each table) > > I would like to create a table containing the ID (only one column > obviously) and all the other columns of both tables... > > a simple equi join for each column is simple but how can I join > completely the two table on the bases of the column ID??? > > probably it is a simple question but I don't know how to solve this very > simple problem quikly... :-( > thanks select * from t1 join t2 USING (id); gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] join a lot of columns of two tables
On 14/12/06, ivan marchesini <[EMAIL PROTECTED]> wrote: Dear Postgres Users, I have 2 tables... each one has a column called ID (primary keys of each table) the values into each ID column are exactly the same. each table has a lot of other columns (around 50 for each table) I would like to create a table containing the ID (only one column obviously) and all the other columns of both tables... a simple equi join for each column is simple but how can I join completely the two table on the bases of the column ID??? probably it is a simple question but I don't know how to solve this very simple problem quikly... :-( thanks Ivan select * from t1 full join t2 on (t1.id=t2.id); you'll end up with the id column from both tables if you don't want that you are going to need to list all the column names. Peter. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] join a lot of columns of two tables
Dear Postgres Users, I have 2 tables... each one has a column called ID (primary keys of each table) the values into each ID column are exactly the same. each table has a lot of other columns (around 50 for each table) I would like to create a table containing the ID (only one column obviously) and all the other columns of both tables... a simple equi join for each column is simple but how can I join completely the two table on the bases of the column ID??? probably it is a simple question but I don't know how to solve this very simple problem quikly... :-( thanks Ivan -- Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg. Preferisco formati liberi. Please try to avoid to send me .doc, .xls, .ppt, .dwg files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Open_format Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
