Question about joining 2 tables.

[RobertP]

MySQL Server 5.5

 

so i am retrieving data from 2 tables. members and module_forums_posts.

i need to select all data from both tables, where module_forums_posts.author_id = members.member_id

that is easy.

but i have 1 issue that i have half solved.

if module_forums_posts.last_edit_id is not 0, i need it to select the display_name for that member from the members table again.

 

i receive the correct data if* the post has been edited. if not, then no rows are returned.

 

this is what i have.

 

CREATE TABLE `members` (
  `member_id` bigint(18) NOT NULL,
  `display_name` varchar(18) NOT NULL,
  `email_addr` varchar(320) DEFAULT NULL,
  `passwrd` varchar(64) DEFAULT NULL,
  `_salt` varchar( DEFAULT NULL,
  `access_level` tinyint(4) NOT NULL DEFAULT '0',
  `customizations` longtext,
  `permissions` longtext,
  `details` longtext,
  PRIMARY KEY (`member_id`)
);

CREATE TABLE `module_forums_posts` (
  `post_id` bigint(18) NOT NULL,
  `forum_id` bigint(18) DEFAULT NULL,
  `post_parent` bigint(18) DEFAULT NULL,
  `post_title` longtext,
  `post_content` longtext,
  `post_date` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `author_id` bigint(18) DEFAULT NULL,
  `is_locked` tinyint(1) NOT NULL DEFAULT '0',
  `last_edit_id` bigint(18) NOT NULL,
  `last_edit_date` decimal(15,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`post_id`)
);

 

select
members.*,
module_forums_posts.*,
member2.display_name as e_display_name
from
members,
members as member2,
module_forums_posts
where
module_forums_posts.author_id = members.member_id
and
module_forums_posts.last_edit_id = member2.member_id;

[Muddy_Funster]

why would you need to retrieve the same piece of information twice?

[RobertP]

the member that last edited the post might not be the original author

[kickstart]

Hi

 

I would use a case statement to select which column to get the data from. You will also need to change the join on the members table against the edit id to a LEFT OUTER JOIN (otherwise if last_edit_id is 0 there would seem to be no matching member record, and with an INNER JOIN no row would be returned).

 

select
members.*,
module_forums_posts.*,
CASE module_forums_post.last_edit_id WHEN 0 then member.display_name as e_display_name ELSE member2.display_name as e_display_name END
from members 
INNER JOIN module_forums_posts ON module_forums_posts.author_id = members.member_id
LEFT OUTER JOIN members as member2 ON module_forums_posts.last_edit_id = member2.member_id

 

All the best

 

Keith

[RobertP]

[sql] select
members.*,
module_forums_posts.*,
CASE module_forums_post.last_edit_id WHEN 0 then member.display_name as e_display_name ELSE member2.display_name as e_display_name END
from members 
INNER JOIN module_forums_posts ON module_forums_posts.author_id = members.member_id
LEFT OUTER JOIN members as member2 ON module_forums_posts.last_edit_id = member2.member_id

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as e_display_name ELSE member2.display_name as e_display_name END
from members ' at line 4

[fenway]

You need to alias after END, not before.

[RobertP]

You need to alias after END, not before.

 

i am not sure what you mean by 'alias' ?

 

[sql] select
members.*,
module_forums_posts.*,
case module_forums_posts.last_edit_id when 0 then member.display_name as e_display_name else member2.display_name as e_display_name end
from members
inner join module_forums_posts on module_forums_posts.author_id = members.member_id
left outer join members as member2 on module_forums_posts.last_edit_id = member2.member_id;

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as e_display_name else member2.display_name as e_display_name end
from members
' at line 4

[kickstart]

i am not sure what you mean by 'alias' ?

 

Alias, as in where you give a column or subselect a different name. Highlighted in red below

 

select

members.*,

module_forums_posts.*,

CASE module_forums_post.last_edit_id WHEN 0 then member.display_name as e_display_name ELSE member2.display_name as e_display_name END

from members

INNER JOIN module_forums_posts ON module_forums_posts.author_id = members.member_id

LEFT OUTER JOIN members as member2 ON module_forums_posts.last_edit_id = member2.member_id

 

You need to do this after the END of the CASE statement, rather than once per clause in the CASE statement.

 

All the best

 

Keith

[RobertP]

i am still completely lost with this query

[kickstart]

Hi

 

Probably my fault.

 

select
members.*,
module_forums_posts.*,
CASE module_forums_post.last_edit_id WHEN 0 then member.display_name ELSE member2.display_name END as e_display_name
from members 
INNER JOIN module_forums_posts ON module_forums_posts.author_id = members.member_id
LEFT OUTER JOIN members as member2 ON module_forums_posts.last_edit_id = member2.member_id

 

All the best

 

Keith