SQL join concat two different values

Question

I'm still in the process of learning SQL queries. I've tried searching around online, but I don't even know what terms I should be searching for, so I'm looking for someone to point me in the right direction. I have two tables -- appointments and logins. I'm trying to use the concat function to get the first and last name for both "apptFor" and "addedBy." Do I need to have separate queries? It seems like it should be able to get done in a single query.

<table border="1">
   <tbody>
     <tr>
       <td width="140" align="center"><strong>Appt Date/Time</strong></td>
       <td width="100" align="center"><strong>Appt For</strong></td>
       <td width="300" align="center"><strong>Appointment</strong></td>
       <td width="100" align="center"><strong>Added By</strong></td>
       <td width="100" align="center"><strong>Date Added</strong></td>
     </tr>
     <?php
            $query = "SELECT a.appID,a.appDate,a.appNote,a.apptFor,a.addedBy,a.added,concat(u.firstName,' ',u.lastName) as appAddedBy FROM appointments a ";
            $query .=" INNER JOIN logins u on a.addedBy=u.userid WHERE a.userID='$uid' order by appDate asc";
            $result = mysqli_query($con, $query);
        if(mysqli_num_rows($result) > 0)
        {
            while($row = mysqli_fetch_assoc($result))
            {
    ?>
     <tr>
       <td><?php echo $row['appDate']; ?></td>
       <td><?php echo $row['apptFor']; ?></td>
       <td><?php echo $row['appNote']; ?></td>
       <td><?php echo $row['appAddedBy']; ?></td>
       <td><?php echo $row['added']; ?></td>
     </tr>
     <?php  }} ?>
   </tbody>
 </table>

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Apr 5, 2018 at 19:19
You can JOIN the same table more than once by using different aliases. — PM 77-1
– PM 77-1, Commented Apr 5, 2018 at 19:19

ScaisEdge · Accepted Answer · 2018-04-05 19:21:23Z

2

If you need tow name you should join two time the logins

     $query = "SELECT a.appID,a.appDate,a.appNote,a.apptFor,a.addedBy
            ,a.added,concat(u1.firstName,' ',u1.lastName) as appAddedBy  
              ,concat(u2.firstName,' ',u2.lastName) as appApptFor 
            FROM appointments a ";
        $query .=" INNER JOIN logins u1 on a.addedBy=u1.userid";
        $query .=" INNER JOIN logins u2 on a.apptFor=u2.userid 
                  WHERE a.userID='$uid' ";
        order by appDate asc";
        $result = mysqli_query($con, $query);

answered Apr 5, 2018 at 19:21

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

2 Comments

mike437 Over a year ago

This solved it! I didn't realize I would need two separate joins (and therefore two separate aliases) for the same table. Thanks!

mike437 Over a year ago

I tried yesterday and it was too soon, then I got sidetracked. It's been marked now. Thanks again!

Collectives™ on Stack Overflow

SQL join concat two different values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related