MySQL Single Row Multiple Columns Multiple Tables

Question

I have two tables 'table A' and 'table B', I would like to select all columns from Table A (10 columns) and select 1 column from Table B so that I have 1 row with a total of 11 columns (10 from Table A and 1 from Table B).

I would like to avoid using 'As alias_name' and use original column name
My tables have no common ID columns
Both my select statements returns 1 row each (1 row from Table A and 1 row from Table B)
I just want to take my result from first select statement and join the result with second select statement to have multiple columns (1 row) and NOT a single column (so I will not be using UNION, perhaps INNER JOIN but not sure how to use it?)

The following statement is close to what I require - It returns 2 columns (alias_name, alias_imageurl) from 2 tables:

SELECT (SELECT name FROM `users`) AS alias_name,(SELECT imageurl FROM `pictures` WHERE profilepicture LIKE '1') AS alias_imageurl

The problem with the above (besides being forced to use an alias for column names) is that I can only return 1 column from Table A instead of all because the below query returns an error: Operation should contain 1 column(s)

SELECT (SELECT * FROM `users`),(SELECT imageurl FROM `pictures` WHERE profilepicture LIKE '1') AS alias_imageurl

You have a very unusual set of requirements. Can you motivate why you think these are a good idea? — Gordon Linoff
– Gordon Linoff, Commented Nov 17, 2016 at 19:30

Gordon Linoff · Accepted Answer · 2016-11-17 19:29:13Z

1

Is this what you want?

SELECT u.*, p.imageurl
FROM users u cross join
     picture p
WHERE p.profilepicture LIKE '1';

answered Nov 17, 2016 at 19:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

user3560827 Over a year ago

Unrecognized keyword. (near "cross" at position 36)

Gordon Linoff Over a year ago

@user3560827 . . . MySQL definitely supports cross joins: dev.mysql.com/doc/refman/5.7/en/join.html.

Collectives™ on Stack Overflow

MySQL Single Row Multiple Columns Multiple Tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related