Joining many results from one table with one result from another in one query

I've got some lexicon-like application which stores data in 2 tables:
lexicon( id, description ) and keywords(lexicon_id, keyword)
This is flexible solution because there may be many keywords
for one description. My problem is how to make ONE query, that
will return description with given id, along with ALL keywords
referencing to this description (keywords of course joined in
one string, separated by a space or comma or any other character)
Is it possible to achieve using MySQL 5? Maybe some subquery?
I'll be grateful for any answer.

Piotr

Re: Joining many results from one table with one result from anotherin one query

Piotr Chmielewski wrote:
> I've got some lexicon-like application which stores data in 2 tables:
> lexicon( id, description ) and keywords(lexicon_id, keyword)
> This is flexible solution because there may be many keywords
> for one description. My problem is how to make ONE query, that
> will return description with given id, along with ALL keywords
> referencing to this description (keywords of course joined in
> one string, separated by a space or comma or any other character)
> Is it possible to achieve using MySQL 5? Maybe some subquery?
> I'll be grateful for any answer.

Do a select on keywords.keyword and left join lexicon

http://dev.mysql.com/doc/refman/5.0/en/join.html


//Aho

Re: Joining many results from one table with one result from another in one query

Uzytkownik "J.O. Aho" napisal w wiadomosci =
news:46661nF9mkohU1@individual.net...
> Do a select on keywords.keyword and left join lexicon
>=20
> http://dev.mysql.com/doc/refman/5.0/en/join.html
>=20
Still I dont know how to extract ALL keywords linked to one description
in just one query... Lets say we have:
CREATE TABLE `keywords` (
`lexicon_id` int(11) NOT NULL default '0',
`keyword` varchar(32) NOT NULL default ''
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8;
INSERT INTO `keywords` VALUES (1, 'pascal');
INSERT INTO `keywords` VALUES (1, 'basic');

CREATE TABLE `lexicon` (
`id` int(11) NOT NULL auto_increment,
`description` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 AUTO_INCREMENT=3D2 ;

INSERT INTO `lexicon` VALUES (1, 'Well known programming language.');

then I want result like:

|description|keywords|
|Well known programming language.|pascal, basic|

Please gimme example or some clue if its possible at all

Piotr

Re: Joining many results from one table with one result from another in one query

Maybe something like;

SELECT *
FROM lexicon
LEFT JOIN keywords ON lexicon_id = id
WHERE id =1



"Piotr Chmielewski" wrote in message
news:dtkvhu$sli$1@inews.gazeta.pl...
Uzytkownik "J.O. Aho" napisal w wiadomosci
news:46661nF9mkohU1@individual.net...
> Do a select on keywords.keyword and left join lexicon
>
> http://dev.mysql.com/doc/refman/5.0/en/join.html
>
Still I dont know how to extract ALL keywords linked to one description
in just one query... Lets say we have:
CREATE TABLE `keywords` (
`lexicon_id` int(11) NOT NULL default '0',
`keyword` varchar(32) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `keywords` VALUES (1, 'pascal');
INSERT INTO `keywords` VALUES (1, 'basic');

CREATE TABLE `lexicon` (
`id` int(11) NOT NULL auto_increment,
`description` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `lexicon` VALUES (1, 'Well known programming language.');

then I want result like:

|description|keywords|
|Well known programming language.|pascal, basic|

Please gimme example or some clue if its possible at all

Piotr

Re: Joining many results from one table with one result from another in one query

U=BFytkownik "laptop" napisa=B3 w =
wiadomo=B6ci news:43fe202c$0$29573$da0feed9@news.zen.co.uk...
> Maybe something like;
>=20
> SELECT *
> FROM lexicon
> LEFT JOIN keywords ON lexicon_id =3D id
> WHERE id =3D1
>=20
No, that would return one keyword for one description...
Thanks though

Piotr

Re: Joining many results from one table with one result from another in one query

Would it?
Have you tested it?

"Piotr Chmielewski" wrote in message
news:dtm78i$qkd$1@inews.gazeta.pl...
U¿ytkownik "laptop" napisa³ w wiadomo¶ci
news:43fe202c$0$29573$da0feed9@news.zen.co.uk...
> Maybe something like;
>
> SELECT *
> FROM lexicon
> LEFT JOIN keywords ON lexicon_id = id
> WHERE id =1
>
No, that would return one keyword for one description...
Thanks though

Piotr

Re: Joining many results from one table with one result from another in one query

U=BFytkownik "laptop" napisa=B3 w =
wiadomo=B6ci news:43fed070$0$29565$da0feed9@news.zen.co.uk...
> Would it?
> Have you tested it?
>=20
Obviously, your query would return for given earlier example
something like this:
+----+---------------------------------+------------+------- ----+
| id | description | lexicon_id | keyword |
+----+---------------------------------+------------+------- ----+
| 1 | Well known programming language | 1 | basic |
| 1 | Well known programming language | 1 | pascal |
+----+---------------------------------+------------+------- ----+

while I need result like this:

+----+---------------------------------+------------+------- ----+
| id | description | lexicon_id | keyword |
+----+---------------------------------+------------+------- ----+
| 1 | Well known programming language | 1 | basic, pascal =
|
+----+---------------------------------+------------+------- ----+
Simply I want to join ALL keywords linked to same description
and return string in ONE query. I doubt it is possible using MySQL
builtin functions but wanted to be sure before I give up...

Piotr

Re: Joining many results from one table with one result from another in one query

I don't know enough to say that it's not possible with MySQL, but I
think the usual way to proceed would be to format the result into a
nice table using a little bit of PHP.

Would that not be appropriate here?

Re: Joining many results from one table with one result from another in one query

Uzytkownik napisal w wiadomosci =
news:1140778073.793427.138450@t39g2000cwt.googlegroups.com.. .
>I don't know enough to say that it's not possible with MySQL, but I
> think the usual way to proceed would be to format the result into a
> nice table using a little bit of PHP.
>=20
> Would that not be appropriate here?

Exactly! I've got a special class which formats and displays
rows returned by query (offers also filtering, sorting, paging)
but it only accepts one query, and it would be too much work
to modify it for this particular case. If I won't find a solution
I'll probably stay with displaying many rows for single description.

Piotr

Re: Joining many results from one table with one result from another in one query

Aha, I see. Well, I've never used it myself but you could try taking a
look at MySQL's group_concat function. It may do what you're after...

http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html

Re: Joining many results from one table with one result from another in one query

SELECT lexicon_id, GROUP_CONCAT( keyword ORDER BY keyword SEPARATOR ',
' ) AS list FROM keywords
GROUP BY lexicon_id
ORDER BY lexicon_id

Re: Joining many results from one table with one result from another in one query

Post removed (X-No-Archive: yes)

Re: Joining many results from one table with one result from another in one query

I suppose that should be...

SELECT lexicon_id, description, GROUP_CONCAT( keyword
ORDER BY keyword
SEPARATOR ', ' ) AS list
FROM keywords
LEFT JOIN lexicon ON lexicon_id = id
GROUP BY lexicon_id
ORDER BY lexicon_id

Re: Joining many results from one table with one result from another in one query

:-) or even...

SELECT lexicon_id, description, GROUP_CONCAT( keyword
ORDER BY keyword
SEPARATOR ', ' ) AS list
FROM keywords
LEFT JOIN lexicon ON lexicon_id = id
WHERE lexicon_id =2
GROUP BY lexicon_id

I can't think why you would need to include the id/lexicon_id twice in
your results so I left it out.

Re: Joining many results from one table with one result from another in one query

Uzytkownik napisal w wiadomosci =
news:1140807180.992331.141630@u72g2000cwu.googlegroups.com.. .
> :-) or even...
>=20
> SELECT lexicon_id, description, GROUP_CONCAT( keyword
> ORDER BY keyword
> SEPARATOR ', ' ) AS list
> FROM keywords
> LEFT JOIN lexicon ON lexicon_id =3D id
> WHERE lexicon_id =3D2
> GROUP BY lexicon_id
>=20
> I can't think why you would need to include the id/lexicon_id twice in
> your results so I left it out.
>
Man! That's awesome, exactly what I wanted! Only problem is
that it screws polish national characters in keywords when I'm
using ISO-8859-2 encoding for tables. Hopefully somehow I'll
fix it myself. Big thanks!!

Piotr

Re: Joining many results from one table with one result from another in one query

Uzytkownik "Piotr Chmielewski" napisal w wiadomosci =
news:dtnng1$lmf$1@inews.gazeta.pl...
>Man! That's awesome, exactly what I wanted! Only problem is
>that it screws polish national characters in keywords when I'm
>using ISO-8859-2 encoding for tables. Hopefully somehow I'll
>fix it myself. Big thanks!!
Ok, my bad, somehow table keywords had set a different collation
than lexicon that caused problem. Now everything is working well.
Thanks again Zac!

Piotr

Re: Joining many results from one table with one result from another in one query

no problem! hey, i learnt something too.