Double SQL Query - JOIN

Question

I need a double SELECT sql query from 2 different tables with names visits & items 1.: SELECT visitid, visitdate, visitreason FROM visits WHERE personid = 10 2.: SELECT itemid, itemname, itemtime FROM items WHERE itemvisitid= visitid I think I need to do a JOIN but don’t know exactly how.

Table examples:

Table: visits

visitid | personid | visitdate  | visitreason
1       | 10       | 05/07/2014 | no reason
2       | 10       | 06/07/2014 | some reason
3       | 12       | 06/07/2014 | no reason
4       | 10       | 12/07/2014 | some other reason

Table: items

itemid | personid | itemvisitid | itemname | itemtime
1      |   10     |  2          | box      | 23
2      |   10     |  2          | clock    | 70            
3      |   10     | null        | water    | 50
4      |   10     | null        | paper    | 40
5      |   12     | 3           | box      | 26

What I have now is this:

$query = "SELECT visitid, visitdate, visitreason FROM visits WHERE personid = '10' ORDER BY visitdate DESC";

// 2nd select: "SELECT itemid, itemname, itemtime FROM items WHERE itemvisitid= visitid";

    $db->setQuery($query);
    $results = $db->query();
while($row = mysqli_fetch_array($results)){
    echo "<tr>
        <td>".$row['visitid'].", ".$row['visitdate']."</td>
        <td>".$row['visitreason']."</td>
        <td>".$row['itemid'].",".$row['itemname'].", ".$row['itemtime']."</td>
        </tr>";     
}

I need results to be something like this:

<tr>
    <td>1, 05/07/2014</td><td>no reason</td><td></td>
    <td>2, 06/07/2014</td><td>some reason</td><td>1, box, 23<br />2, clock, 70</td>
    <td>4, 12/07/2014</td><td>some other reason</td><td></td>
</tr>

SO is not a place where people do things for you, but answer questions on problems you find as you do things. — Cynical, Jul 05 '14 at 13:20

ujjal · Answer 1 · 2014-07-05T13:32:44.237

0

SELECT `visits`.`visitid`, `visits`.`visitdate`, `visits`.`visitreason`,`items`.`itemname`, `items`.`itemtime` from `visits` INNER JOIN `items` ON `visits`.`personid`=`items`.`personid` WHERE `visits`.`personid` = '10' ORDER BY `visits`.`visitdate` DESC

if there is any error please change the field name personid in 'items' table.and then check.

edited Jul 05 '14 at 13:32

answered Jul 05 '14 at 13:21

ujjal

225
4
8

score 0 · Answer 2 · answered Jul 05 '14 at 13:36

This query:

SELECT v.visitid,
       v.visitdate,
       v.visitreason,
       i.itemid,  
       i.itemname,
       i.itemtime
FROM   visits v
       INNER JOIN items i
               ON ( v.visitid = i.itemvisitid )
WHERE  v.person_id = 10
ORDER  BY v.visitdate DESC,
          i.itemid ASC

will link both tables and produce a resultset that you can traverse using a double loop. The outer loop to process changes to the visit, and the inner to add every item visited in a particular visit.

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

I guess your might to use GROUP_CONCAT like this:

DEMO: http://sqlfiddle.com/#!2/9d4e22/15

SELECT visitid, DATE_FORMAT(visitdate,'%m/%d/%Y'), visitreason,
GROUP_CONCAT(itemid,itemname, itemtime)
FROM visits  left join items on visits.visitid = items.itemvisitid
WHERE visits.personid = 10 
GROUP BY  visitid, visitdate, visitreason

You might want to read this to know GROUP_CONCAT :
How to use GROUP_CONCAT in a CONCAT in MySQL

The document of GROUP_CONCAT() is here:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat hope this helps.

edited May 23 '17 at 11:49

Community

1
1

answered Jul 05 '14 at 13:39

naota

4,695
1
18
21

Thank you this worked. Is there a way to add a "space" or a "-" between the GROUP_CONCAT results?, in my case results are something like this: 1box23,2clock70 – John Jul 05 '14 at 15:45
1

@John Thank you! Yes, you could go like this `GROUP_CONCAT(itemid,'-',itemname,'-', itemtime)` http://sqlfiddle.com/#!2/9d4e22/30 – naota Jul 05 '14 at 16:10

Double SQL Query - JOIN

3 Answers3