Search the Community
Showing results for tags 'join'.
Found 3 results
-
I've got two tables user table - id - username - fname - lname - group_id event Table - id - user_id - event_time - event_date - event ('Arrival','Departure','Break',etc..) My problem is getting all user by group and joining other table even date, but it should be base on 'event_date' I use `LEFT JOIN` and `LEFT OUTER JOIN` but because I have to specify the `event_date` not all user can be shown because some are absent. I made this SELECt * FROM user as u LEFT OUTER JOIN event as e ON u.id = e.user_id WHERE u.group_id = 6 AND ( e.event_date = '2016-07-05' AND e.event = 'Arrival' ) GROUP BY u.id but it only show the user who has an event on the date specified. I tried to make an VIEW table but it still has the condition base on event_date. Although I made this on PHP by condition but it takes time to load as I select event on every user. is there a way to get it using SQL? it should be like this:
-
I want to fetch the titles from the Courses table, but I only want the Course Title to return once when I join Events. Courses CID | CourseTitle 1 | Course A 2 | Course B 3 | Course C Events EID | CID | EventDate 1 | 1 | 2016-02-22 2 | 1 | 2016-02-23 3 | 2 | 2016-02-24 4 | 3 | 2016-02-25 5 | 3 | 2016-02-26 If I use a JOIN, SELECT Courses.CourseTitle FROM Courses LEFT JOIN Events on (Events.CID = Courses.CID) then I get Course A Course A Course B Course C Course C But what I want is Course A Course B Course C Because ultimately, I'm going to select an Event date range, and I want to see just the courses with the event date range. Thanks!
-
Hello all. I dont know how to go about this. I have a table (Transactions) that contains transactions of users. Another table (Confirmed) contains details of every confirmed user. I want to do a select statement that will display all the confirmed user with only the last of their transaction. But so far all it does is replicate the user and their date of transaction and that is not what i want. My intention is to get something like: Firstname Surname Date Registered Last Transaction andrews john 12-12-2014 10-10-2015 doe andy 12-12-2010 12-12-2014 But i'm getting something like: Firstname Surname Date Registered Last Transaction andrews john 12-12-2014 10-10-2015 andrews john 12-12-2014 10-11-2015 doe andy 12-12-2010 12-12-2014 doe andy 12-12-2010 01-12-2014 doe andy 12-12-2010 12-12-2013 Thanks $stm=$pdo->query("select * from confirmed left join transaction on confirmed.user_id = transaction.user_id where confirmed.status='confirmed' order by date"); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['firstname']; echo $row['surname']; echo $row['regDate']; echo $row['lastTrans']; }
