I've got 2 tables. submissions and users. They are related by submissions.user_id = users.id.
users:
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| email | varchar(128) | NO | MUL | NULL | |
| hash | varchar(64) | NO | | NULL | |
| salt | varchar(32) | NO | | NULL | |
| username | varchar(23) | NO | | NULL | |
| type | enum('normal','admin') | NO | | normal | |
| about | varchar(255) | NO | | NULL | |
| created | datetime | NO | | NULL | |
| last_login | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created_ip | int(10) unsigned | NO | | NULL | |
| last_login_ip | int(10) unsigned | NO | | NULL | |
| remember_me | tinyint(3) unsigned | NO | | 0 | |
| avatar | varchar(32) | NO | | NULL | |
| confirmed | tinyint(1) unsigned | NO | | 0 | |
| confirm_code | varchar(64) | NO | | NULL | |
| public_profile | tinyint(1) | NO | | 1 | |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
submissions
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | NULL | |
| slug | varchar(255) | NO | | NULL | |
| description | mediumtext | NO | | NULL | |
| user_id | int(11) | NO | MUL | NULL | |
| created | datetime | NO | | NULL | |
| category | enum('animals-pets','autos-vehicles','careers-work','clothing','computers','cooking-food','deals','education','electronics','entertainment','general-advice','finance','health-fitness','hygiene','home-garden','how-to','misc','self-improvement','sports','traveling') | NO | | NULL | |
| type | enum('tip','request') | NO | | NULL | |
| thumbnail | varchar(64) | NO | | NULL | |
| removed | tinyint(1) unsigned | NO | | 0 | |
| down_votes | int(10) unsigned | NO | | 0 | |
| up_votes | int(10) unsigned | NO | | 0 | |
| score | int(11) | NO | MUL | 0 | |
| keywords | varchar(255) | NO | | NULL | |
| ip | int(10) unsigned | NO | | NULL | |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
I'm trying to join the two tables for viewing a submission but the result I get the following query gives me this:
SELECT s.*, u.id, u.username FROM submissions s JOIN users u ON s.user_id = u.id WHERE s.id = 12'
Will always give me:
{ id: 15,
title: 'what the hell!',
slug: 'what-the-hell',
description: 'seriously what the hell node mysql?!',
user_id: 15,
created: Sat Jan 11 2014 11:58:06 GMT-0800 (PST),
category: 'misc',
thumbnail: '',
removed: 0,
down_votes: 0,
up_votes: 0,
score: 0,
keywords: '!',
ip: 127001,
username: 'blahbster'}
It'll always make the submission.id when fetching which is the user.id. Why is it overwriting?
It is "overwriting" because two columns have the same name, id, and there is no way to distinguish between them.
Just use:
SELECT s.*, u.username
FROM submissions s JOIN
users u
ON s.user_id = u.id
WHERE s.id = 12;
And use user_id for the user id and id for the subscription id.