Databases
StackOverflow2013
Postsdbo
POhow do i select data from multiple columns from two tables where there is a common column in both?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow do i select data from multiple columns from two tables where there is a common column in both?
    primarykey
    data
    text
    <p>seems like a cake walk, because of the common column thing, but i've looked and i've looked and can't find the answer anywhere. there may be very similar things people are trying to do, but everyone always needs it slightly different than how i need it. anyway here goes...</p> <p>jobs table</p> <pre><code>+----+--------+----+----+----+ | id | userid | i1 | i2 | i3 | +----+--------+----+----+----+ | 1 | 1 | a | k | t | | 2 | 1 | b | l | u | | 3 | 1 | c | m | v | | 4 | 2 | d | n | w | | 5 | 2 | f | o | x | | 6 | 2 | g | p | y | | 7 | 3 | h | q | z | | 8 | 3 | i | r | a | | 9 | 4 | j | s | b | +----+--------+----+----+----+ </code></pre> <p>user_table table</p> <pre><code>+--------+----+----+----+ | userid | fn | ln | i4 | +--------+----+----+----+ | 1 | a | b | w | | 2 | c | d | x | | 3 | e | f | y | | 4 | g | h | z | +--------+----+----+----+ </code></pre> <p>i want to select multiple columns from one table and multiple columns from another table, and the amount of columns don't have the same amount. so if i'm selecting id, i1, i2, and i3 from jobs table and selecting fn, ln, and i4 from user_table table, then that means i'm selecting 4 pieces of info from jobs table and 3 pieces of info from user_table table.</p> <p>so let's say i want to select a specific job and display the info for that job, but also i want to display the info of the user that belongs to the job then it might go something like this...</p> <p>job 1:</p> <p>id: 1, i1: a, i2: k, i3: t, fn: a, ln: b, i4: w</p> <p>for job 4 it would be:</p> <p>id: 4, i1: d, i2: n, i3: w, fn: c, ln: d, i4: x</p> <p>the jobs table and user_table table have the common column of userid. so how do i write my query to do the above using this common column?</p> <p>i tried all sorts of things with SELECT and WHERE and AND and JOIN and UNION and AS and GROUP and BY and writing the table names separate from the column names and writing the table names and column names together with a period in between. i just can't find the correct way to write this query.</p> <p>in these examples below that don't work i'm just trying to select one thing from the user_table table and everything from the jobs table that isn't the userid, but i will need the ability to select multiple things from the 2nd table if needed. so i want to select everything from first table and just certain things from second table.</p> <p>doesn't work:</p> <pre><code>SELECT id, i1, i2, i3, i4 FROM jobs, user_table WHERE jobs.id = $id GROUP BY id </code></pre> <p>doesn't work:</p> <pre><code>SELECT * from jobs where id = $id UNION SELECT i4 from user_table where userid = $userid </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USleoarce
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COPlease read SQL tutorials about JOIN.
      singulars
      1. POhow do i select data from multiple columns from two tables where there is a common column in both?
      1. USBarmar
    2. CO`SELECT jobs.*, user_table.* FROM jobs LEFT JOIN user_table ON jobs.user_id = user_table.userid` - or INNER JOIN, depending on your requirements of the results (LEFT JOIN will show ALL jobs, regardless of whether the userid matches, where as INNER JOIN will enforce a match between the two)
      singulars
      1. POhow do i select data from multiple columns from two tables where there is a common column in both?
      1. UScale_b
    3. COthis one also doesn't select the correct data from 2nd table. unless i'm not making my variables correct? after the query how do i make variables of all the columns from different tables? like this? $row = mysql_fetch_array($result); if($row) { $date = $row['date']; $imgurl = $row['img_url']; } --- the date one is from jobs table and the img one is from user_table. when i run the whole thing, the img is the same for all the jobs and not changing depending on the user associated with the job.
      singulars
      1. POhow do i select data from multiple columns from two tables where there is a common column in both?
      1. USleoarce
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload