mysql Using INNER JOIN to select multiple tables?

Question

I want to select all fields of several table and fetch result separate but mysql return all rows together:

SELECT prod_product.*
  ,pub_comment.* 
FROM prod_product 
INNER JOIN pub_comment ON (prod_product.id = pub_comment.itemId) 
WHERE prod_product.id=7744

Is there any way that i could fetch each table rows separately?

I try @prod:=prod_product.*, @comment:=pub_comment.* but mysql didn't allow me to store more than 1 row.

@user703617: remember to accept correct answer if you find one. — Marco
– Marco, Commented Apr 12, 2011 at 9:10

Marco · Accepted Answer · 2011-04-12 08:36:24Z

1

Execute two queries:

select * from prod_product WHERE prod_product.id=7744

and

select * from pub_comment WHERE pub_comment.itemId=7744

A single query always return single rows containing two table fields.
Anyway: what is the problem of having columns together in a single row? If you have problems with their names you can use aliases.

edited Apr 12, 2011 at 8:36

answered Apr 12, 2011 at 8:23

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hamid Over a year ago

Hi marco , Thanks for your answer but it's 2 queries and i need to decrease my db fetches, maybe i have to do it

Marco Over a year ago

@user703617: As I told you, why can't you use a single query? Which is the problem of having columns together?

Marco Over a year ago

@user703617: if you give us some detail, we can help you better :)

Johan · Accepted Answer · 2011-04-12 15:43:59Z

0

SELECT @prod:= CONCAT_WS(',',prod_product.field1,prod_product.field2,...)
  ,@comment:= CONCAT_WS(' ',pub_comment.field1,pub_comment.field2,....) 
FROM prod_product 
INNER JOIN pub_comment ON (prod_product.id = pub_comment.itemId) 
WHERE prod_product.id = 7744

Note that CONCAT_WS will separate your fields and CONCAT will just smash then together.

answered Apr 12, 2011 at 15:43

Johan

77.4k28 gold badges204 silver badges346 bronze badges

1 Comment

Johan Over a year ago

Oh, I must warn you about concat_ws, it gave me a nasty collation error where the table was in LATIN1 and the system is set to UTF8 then the result will be in UTF8, which will not be INSERTed into a LATIN1 table. If you get this workaround is to use CONCAT(field1,' ',field2,' '...).

Collectives™ on Stack Overflow

mysql Using INNER JOIN to select multiple tables?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related