$30
How to left join 3 tables in a view?
CREATE VIEW answers_totals_users AS SELECT
answer.id,
answer.user_id,
answer.question_id,
answer.image AS image,
answer.description,
answer.status,
answer.prize_amount_paid_for_this_answer,
answer.created_at,
answer.updated_at,
COUNT(answer.question_id) AS questionTotal,
SUM(answer.prize_amount_paid_for_this_answer) AS prizeTotal,
sf_guard_user_profile.first_name,
sf_guard_user_profile.last_name,
sf_guard_user_profile.biography,
sf_guard_user_profile.image AS userImage,
sf_guard_user_profile.clients_can_contact,
sf_guard_user.username
FROM ((answer join sf_guard_user_profile) join sf_guard_user)
WHERE ((answer.user_id = sf_guard_user_profile.user_id)
AND (sf_guard_user.id = sf_guard_user_profile.user_id)
AND (sf_guard_user.id = answer.user_id))
GROUP by answer.user_id;The above works fine, but now I need everybody from sf_guard_user_profile, so I want to create a new view where sf_guard_user_profile is left joining against answers.
--------------------------
UPDATE:
Here are the 3 tables I am trying to LEFT JOIN:
CREATE TABLE IF NOT EXISTS answer (
id int(11) NOT NULL auto_increment,
user_id int(11) default NULL,
question_id int(11) default NULL,
image varchar(255) character set utf8 NOT NULL default '',
description text character set utf8 NOT NULL,
status varchar(255) character set utf8 NOT NULL default '',
score int(11) default NULL,
prize_amount_paid_for_this_answer float default NULL,
created_at datetime default NULL,
updated_at datetime default NULL,
PRIMARY KEY (id),
KEY answer_FI_1 (user_id),
KEY answer_FI_2 (question_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=986 ;
CREATE TABLE IF NOT EXISTS sf_guard_user_profile (
id int(11) NOT NULL auto_increment,
user_id int(11) NOT NULL,
status varchar(255) character set utf8 NOT NULL default '',
first_name varchar(255) character set utf8 NOT NULL default '',
last_name varchar(255) character set utf8 NOT NULL default '',
email varchar(255) character set utf8 NOT NULL default '',
url varchar(255) character set utf8 NOT NULL default '',
biography text character set utf8 NOT NULL,
created_at datetime default NULL,
updated_at datetime default NULL,
cash_total float default NULL,
image varchar(255) character set utf8 NOT NULL default '',
secret_signup_key varchar(255) character set utf8 NOT NULL default '',
twitter_address varchar(255) character set utf8 NOT NULL default '',
landing_page varchar(255) character set utf8 NOT NULL default '',
subscription_paid datetime NOT NULL,
paypal_email_address varchar(255) character set utf8 NOT NULL default '',
subscribe_to_questions_via_email varchar(1) character set utf8 NOT NULL default '',
clients_can_contact char(1) NOT NULL,
country varchar(255) NOT NULL,
city varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY sf_guard_user_profile_FI_1 (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1185 ;
CREATE TABLE IF NOT EXISTS sf_guard_user (
id int(11) NOT NULL auto_increment,
username varchar(128) character set utf8 NOT NULL default '',
algorithm varchar(128) character set utf8 NOT NULL default '',
salt varchar(128) character set utf8 NOT NULL default '',
password varchar(128) character set utf8 NOT NULL default '',
created_at datetime default NULL,
last_login datetime default NULL,
is_active tinyint(4) NOT NULL default '1',
is_super_admin tinyint(4) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY sf_guard_user_U_1 (username)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1189 ;
I need all the records from sf_guard_user_profile. When there is a matching record in 'answer' then I want that too, but if there is no match, I still want the info from sf_guard_user_profile. Thus, a LEFT JOIN.
Lawrence Krubner | 07/11/10 at 2:19am
| Edit
(1) Possible Answers Submitted...
-
Last edited:
07/12/10
1:01amJarret Minkler says:Your query is hard to read because of the parens and disjoined connections.
Remove the parens and sorting out the joins
FROM
answer a
join sf_guard_user_profile up on (a.user_id = up.user_id)
join sf_guard_user gu on (up.user_id = gu.id) -- you had this reversed, start with what you already joined above
-- AND (sf_guard_user.id = answer.user_id)) you don't need this here since you are already starting from this criteria (user_id propagates)
GROUP by answer.user_id;
This should be everybody from all tables already, but probably better is ..
FROM
answer a
join sf_guard_user gu on (a.user_id = gu.id)
join sf_guard_user_profile up on (gu.id = up.user_id)
Simple, and easy to read.
Now I understand your question, not everybody has an answer, and you want a left outer join.
FROM
answer a
left outer join sf_guard_user gu on (a.user_id = gu.id)
join sf_guard_user_profile up on (gu.id = up.user_id)
should be what you are looking for
Previous versions of this answer: 07/11/10 at 11:19am
- 07/11/10 1:22pm
Lawrence Krubner says:Hmm, no, I think you've got the JOIN statements backwards. This has no syntax errors, but it gives the wrong results:
CREATE VIEW members_with_some_answer_data AS SELECT
a.id,
a.user_id,
a.question_id,
a.image AS image,
a.description,
a.status,
a.prize_amount_paid_for_this_answer,
a.created_at,
a.updated_at,
COUNT(a.question_id) AS questionTotal,
SUM(a.prize_amount_paid_for_this_answer) AS prizeTotal,
sgup.first_name,
sgup.last_name,
sgup.biography,
sgup.image AS userImage,
sgup.clients_can_contact,
sgu.username
FROM
answer a
left outer join sf_guard_user sgu on (a.user_id = sgu.id)
join sf_guard_user_profile sgup on (sgu.id = sgup.user_id)
WHERE ((a.user_id = sgup.user_id)
AND (sgu.id = sgup.user_id)
AND (sgu.id = a.user_id))
GROUP by sgup.user_id
However, this is still mostly getting data from answer. I need everything from sf_guard_user_profile. The information from 'answer' is merely incidental and not especially important. - 07/11/10 6:03pm
Jarret Minkler says:You don't need the where clause
- 07/11/10 6:06pm
Jarret Minkler says:If you JUST need the sf_guard_user_profile information where they HAVE anwers, just change the SELECT to
SELECT gu.* From ...
- 07/11/10 6:09pm
Jarret Minkler says:so ...
SELECT sfgp.*
FROM sf_guard_user_profile sfgp join answers a on a.user_id = sfgp.user_id
GROUP BY sfgp.user_id
- 07/11/10 9:02pm
Lawrence Krubner says:I am sorry, I am explaining myself poorly. I did try this, but it still gives the wrong result:
CREATE VIEW members_with_some_answer_data AS SELECT
a.id,
a.user_id,
a.question_id,
a.image AS image,
a.description,
a.status,
a.prize_amount_paid_for_this_answer,
a.created_at,
a.updated_at,
COUNT(a.question_id) AS questionTotal,
SUM(a.prize_amount_paid_for_this_answer) AS prizeTotal,
sgup.first_name,
sgup.last_name,
sgup.biography,
sgup.image AS userImage,
sgup.clients_can_contact,
sgu.username
FROM
answer a
left outer join sf_guard_user sgu on (a.user_id = sgu.id)
join sf_guard_user_profile sgup on (sgu.id = sgup.user_id)
GROUP by sgup.user_id
There are 111 results that I expect back from sf_guard_user_profile, but I'm still getting only the 48 results where sf_guard_user_profile overlaps with a result in answers. - 07/11/10 9:03pm
Lawrence Krubner says:I previously stated my case poorly. I need all the info in the SELECT clause. But sf_guard_user_profile is the LEFT table. I need that data even when it does not match a record in the answers table. I've had trouble getting the syntax right.
- 07/11/10 10:24pm
Jarret Minkler says:(previously)
You can put the original select in there ..
SELECT sfgp.*
FROM sf_guard_user_profile sfgp left outer join answers a on a.user_id = sfgp.user_id
GROUP BY sfgp.user_id
- 07/11/10 10:25pm
Jarret Minkler says:Whoops
SELECT sfgp.*
FROM sf_guard_user_profile sfgp left outer join answers a on sfgp.user_id = a.user_id
GROUP BY sfgp.user_id
- 07/11/10 10:33pm
Lawrence Krubner says:That looks very close, but I also need to get the username from the sf_guard_user table. This is a 3 table JOIN.
- 07/11/10 11:47pm
Lawrence Krubner says:This seems to work:
CREATE VIEW members_with_some_answer_data AS SELECT
a.id,
a.user_id,
a.question_id,
a.image AS image,
a.description,
a.status,
a.prize_amount_paid_for_this_answer,
a.created_at,
a.updated_at,
COUNT(a.question_id) AS questionTotal,
SUM(a.prize_amount_paid_for_this_answer) AS prizeTotal,
sgup.first_name,
sgup.last_name,
sgup.biography,
sgup.image AS userImage,
sgup.clients_can_contact,
sgu.username
FROM
sf_guard_user sgu
left outer join answer a on (a.user_id = sgu.id)
join sf_guard_user_profile sgup on (sgu.id = sgup.user_id)
GROUP by sgup.user_id
I need to test this. - 07/12/10 12:32am
Jarret Minkler says:Ahh missed the sgu.username in the original select, otherwise you don't need that join :)
- 07/12/10 12:33am
Lawrence Krubner says:No, still doesn't work. I'm getting a Cartesian product. Tons of extra results.
CREATE VIEW members_and_also_answer_data AS SELECT
a.id,
a.user_id,
a.question_id,
a.image AS image,
a.description,
a.status,
a.prize_amount_paid_for_this_answer,
a.created_at,
a.updated_at,
COUNT(a.question_id) AS questionTotal,
SUM(a.prize_amount_paid_for_this_answer) AS prizeTotal,
sgup.first_name,
sgup.last_name,
sgup.biography,
sgup.image AS userImage,
sgup.clients_can_contact,
sgup.id as profileId,
sgu.username
FROM
sf_guard_user sgu
left outer join answer a on (a.user_id = sgu.id)
join sf_guard_user_profile sgup on (sgu.id = sgup.user_id)
and sgu.is_active=1
GROUP by sgup.user_id - 07/12/10 12:34am
Lawrence Krubner says:Yes, without sgu.username, this is childs play.
- 07/12/10 12:35am
Lawrence Krubner says:Well, not "tons". A few redundant results.
- 07/12/10 12:37am
Lawrence Krubner says:This is an oddly done LEFT JOIN. I'm getting back rows where both "id" and "user_id" are null.
- 07/12/10 12:40am
Lawrence Krubner says:I am stupid. Forgot to change the select clause. id and user_id should not reference the answer table.
Funny how taking a working view and trying to make a new view out of it can get complicated. - 07/12/10 12:42am
Lawrence Krubner says:Now I tried this, but this basically subverts the LEFT JOIN and makes it a normal JOIN again.
SELECT
sgu.id,
sgup.user_id,
a.question_id,
a.image AS image,
a.description,
a.status,
a.prize_amount_paid_for_this_answer,
a.created_at,
a.updated_at,
COUNT(a.question_id) AS questionTotal,
SUM(a.prize_amount_paid_for_this_answer) AS prizeTotal,
sgup.first_name,
sgup.last_name,
sgup.biography,
sgup.image AS userImage,
sgup.clients_can_contact,
sgup.id as profileId,
sgu.username
FROM
sf_guard_user sgu
left outer join answer a on (a.user_id = sgu.id)
join sf_guard_user_profile sgup on (sgu.id = sgup.user_id)
and sgup.user_id=a.user_id
and sgu.is_active=1
GROUP by sgu.id - 07/12/10 12:50am
Lawrence Krubner says:I suppose the JOIN might be working. I've moved on to some completely new, bizarre database behavior, I think.
- 07/12/10 1:01am
Lawrence Krubner says:Yes, so, as follow up, thanks for your help. You helped me get closer to the solution. In the end, this is what I needed:
CREATE VIEW members_and_also_answer_data AS SELECT
sgu.id,
sgup.user_id,
sgup.id as profileId,
a.question_id,
a.image AS image,
a.description,
a.status,
a.prize_amount_paid_for_this_answer,
a.created_at,
a.updated_at,
COUNT(a.question_id) AS questionTotal,
SUM(a.prize_amount_paid_for_this_answer) AS prizeTotal,
sgup.first_name,
sgup.last_name,
sgup.biography,
sgup.image AS userImage,
sgup.clients_can_contact,
sgu.username
FROM
sf_guard_user sgu
left outer join answer a on (a.user_id = sgu.id)
join sf_guard_user_profile sgup on (sgu.id = sgup.user_id)
and sgu.is_active=1
GROUP by sgu.id
- 07/11/10 1:22pm
This question has expired.
Current status of this question: Completed
