conditional query mysql

Question

i have this mysql schema:

comments:
    -----------------------------
    |id|pageid|userid|

members:
    -----------------------------
    |username|userid|

sometimes the userid from comments is blank, cause anon comment. how i can get the username from members if userid from comments isn't blank.

i need to get something like this:

---------------------------
|id|pageid|userid|username|
---------------------------

Edit. i want to try to be more specific.

comments:
    ----------------------------------
    |id|pageid|userid|comment        |   
    ---------------------------------- 
    |1 |1     |1     | hi            |
    ---------------------------------- 
    |2 |1     |      | annon comment |

members:
    -----------------------------
    |username|userid            |
    -----------------------------
    |john    |1                 |

thats is a example data, now i need to get something like this:

    -------------------------------------------
    |id|pageid|userid|comment        |username|  
    ------------------------------------------- 
    |1 |1     |1     | hi            |john    |
    ------------------------------------------- 
    |2 |1     |      | annon comment |        |

ps sorry for my bad english.

Glen Solsberry · Accepted Answer · 2010-10-19 15:24:47Z

1

SELECT c.PageId,c.UserId, m.UserName FROM comments as c
LEFT OUTER JOIN members m ON c.userid = m.userid

LEFT JOIN gets you fields that do not have a match in the right table.

answered Oct 19, 2010 at 15:24

Glen Solsberry

12.4k16 gold badges74 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abe Miessler · Accepted Answer · 2010-10-19 15:15:02Z

1

This should work:

SELECT c.PageId,c.UserId, m.UserName FROM Members as m
INNER JOIN Comments as c
ON m.UserId = c.UserId

answered Oct 19, 2010 at 15:15

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

2 Comments

greenbandit Over a year ago

if the userid from comments table is blank all these anonymous comments are ignored.

Abe Miessler Over a year ago

Yes, based on your statement how i can get the username from members if userid from comments isn't blank. this would be the correct behavior...

Collectives™ on Stack Overflow

conditional query mysql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related