StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFour Table Join in Oracle 8i
text
Body
copied!<p>I'd like to join four tables in Oracle 8i. The four table names are users, picks, putaways, and relocates. Each of the four tables has a user_id field. Picks, putaways, and relocates look like this:</p> <pre><code>| USER_ID | PICKS | PICK_VOLUME | +---------+-------+-------------+ | SMITH12 | 234 | 2431.12 | </code></pre> <p>The putaways and relocates tables look identical except 'PICKS' and 'PICK_VOLUME' are replaced with putaways and relocates, respectively.</p> <p>The table that I would like to do a left join on is the users table since it will contain all of the user_ids, regardless if they only picked inventory and didn't relocate, or put any of it away. In other words, I would like to see all the records even if some users only have relocates, instead of having picks, putaways, and relocates, or any combination thereof.</p> <p>The user table looks like this:</p> <pre><code>| USER_ID | SHIFT | GROUP_ID | +---------+-------+------------+ | SMITH12 | NIGHT | STOCK_KEEP | </code></pre> <p>So ideally, I would like to have those three columns at the left of my result set, with the picks, putaways, and relocates table joined to the right. The final select results should look like this:</p> <pre><code>| USER_ID | SHIFT | GROUP_ID | PICKS | PICK_VOL | RELOCATES | RELOCATE_VOL | PUTAWAYS | PUTAWAY_VOL | +---------+-------+------------+-------+----------+-----------+--------------+----------+-------------+ | SMITH12 | NIGHT | STOCK_KEEP | 234 | 2431.12 | NULL | NULL | 4 | 76.52 | </code></pre> <p>I've tried this SQL query:</p> <pre><code>select users.user_id, users.group_id, users.shift, pi."Picks", pi."Pick Volume", pu."Putaways", pu."Putaway Volume", re."Relocates", re."Relocate Volume" from users, pi, pu, re where users.user_id = pi.user_id and users.user_id = pu.user_id and users.user_id = re.user_id; </code></pre> <p>but it only returns rows where a user has at least one transaction in the picks, relocates, and putaways tables. This is expected since I'm doing a vanilla join. I'm assuming I need some kind of left join, but I'm not sure how to write it in Oracle 8i. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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