CODEWITHSUNDEEP
mysqlselectinner-join
Hamid
Hamid

Reputation: 141

mysql Using INNER JOIN to select multiple tables?

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.

Upvotes: 0

Views: 1271

Answers (2)

Johan
Johan

Reputation: 76670

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.

Upvotes: 0

Marco
Marco

Reputation: 57593

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.

Upvotes: 1

Related Questions