In this blog post, I use a very simple example to answer the question how to select data from two different tables in a MySQL database.
Start with creating a database. Now create 2 tables: CREATE TABLE t1 (col1 VARCHAR(1),col2 INT(1));CREATE TABLE t2 (col3 VARCHAR(1),col4 INT(1));
INSERT INTO t1 VALUES (‘a’,1),(‘b’,2),(‘c’,3);
INSERT INTO t2 VALUES (‘x’,1),(‘y’,2),(‘z’,3); Now do a joined select and notice that you can one very big table: SELECT * FROM t1,t2 output:
a 1 x 1
b 2 x 1
c 3 x 1
a 1 y 2
b 2 y 2
c 3 y 2
a 1 z 3
b 2 z 3
c 3 z 3 However, if col2 of t1 and col4 of t2 are identifiers for the same subjects, we can use these keys to display only rows that make sense: SELECT * FROM t1,t2 WHERE col2=col4 output:
a 1 x 1
b 2 y 2
c 3 z 3 We can adjust this statement to get only the information we want, e.g. all information from both tables about subject x: SELECT * FROM t1,t2 WHERE col2=col4 AND col3=’x’ Suppose that the two columns col2 and col4 have the same name, e.g. ‘id’ (which would make sense) then we would write: SELECT * FROM t1,t2 WHERE t1.id=t2.id AND col3=’x’ and if we only need to know col1 (because the id column is really just an identifier and doesn’t contain information we need and we use col2 to identify subject x, which is information we already have) then we can write: SELECT col1 FROM t1,t2 WHERE t1.id=t2.id AND col3=’x’ output:
a
