leoarce leoarce - 6 days ago 4
MySQL Question

How to select data of 2nd table from column values of 1st table in same select statement?

Let's say I have this query:

SELECT first, last, operation_code, serial_no FROM list_data WHERE cid='".$cid."'


And let's say that the results of the query is this:

john, smith, 100, 000000001
john, doe, 200, 000000002
jane, doe, 300, 000000003


I would like to add some data to the results from a different table. Like this:

john, smith, 100 - description for 100, 000000001
john, doe, 200 - description for 200, 000000002
jane, doe, 300 - description for 300, 000000003


The other table has an operation_code column and a description column called operation_text. So as the original query is going through the first table to get records, can it use the operation_code number to go get description of that operation code from the operation_codes table?

Not sure if this matters, but it needs to fit in this csv file creation method I am using.

$output = fopen('php://output', 'w');
fputcsv($output, array('first', 'last', 'operation_code', 'serial_no'));
$rows = mysql_query("SELECT first, last, operation_code, serial_no FROM list_data WHERE cid='".$cid."' ORDER BY last ASC, first ASC");
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);


So when csv file is created, I want the code number and description to be in the same column. No extra comma in between code and description, unless there is no other way then it's fine if description is in it's own column like so:

Header line of csv:
first, last, operation_code, operation_text, serial_no
body of csv:
john, smith, 100, description for 100, 000000001
john, doe, 200, description for 200, 000000002
jane, doe, 300, description for 300, 000000003

Answer

This is just an INNER JOIN to that second table:

SELECT first, last, list_data.operation_code, second_table.operation_text, serial_no 
FROM list_data INNER JOIN second_table ON list_data.operation_code = second_table.operation_code
WHERE cid='".$cid."'

Here we join list_data to second_table on the operation_code then up in the SELECT portion of the statement we bring in that new field. Note that for operation_code because it exists in two tables that are in the query, we have to specify that the operation_code we want is from the list_data table (either will work since it's an INNER JOIN, but it has to be specified).