How do I select two tables in 1 query?

Question

I want to select two tables in one query but it doesn't seem to work. I have tried nested select but I just got sql_error:

Subquery returns more than 1 row

Here is my query:

SELECT `client`.`id` as client_id,
(SELECT `org`.`name` FROM `org`) as organization
FROM `client`

What is the better way to query two tables?

Here is my expected result:

client_id = [1,2,3,4,5]
organization = [x,y,z]

Use `join`. That is the way that SQL normally brings two tables together. — Gordon Linoff, Oct 14 '15 at 11:35
just use two queries if there's no foreign key between those two tables — Luthando Ntsekwa, Oct 14 '15 at 11:35
Will be useful http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables — Siddharth Kumar, Oct 14 '15 at 11:39
There is no need to use joins or unions. 'select * from client, org' would work just as fine. — Reality-Torrent, Oct 14 '15 at 11:43

score 0 · Answer 1 · answered Oct 14 '15 at 11:38

This will work but it isn't what you want i think.

SELECT `client`.`id` as client_id, `org`.`name` as organization FROM `org`, `client`

This will give you a cross product of all rows in org with all rows in client. Maybe you want something like this:

SELECT `client`.`id` as client_id, `org`.`name` as organization FROM `org` JOIN `client` ON `client`.`memberOf` = `org`.`id`

The JOIN will connect rows of both tables where column memberOf in table client is equal to column id in table org

score 0 · Answer 2 · answered Oct 14 '15 at 11:41

0

You should use join queries to join two or more tables. you can visit https://dev.mysql.com/doc/refman/5.0/en/join.html

Example of the join query:

SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

answered Oct 14 '15 at 11:41

nand Maithani

2 Answers2