Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Linked table query

    Hi, I have a mySQL db for storing course bookings that I connect to using a VB6 app.

    I have 3 tables tblCustomer, tblCourse and tblCourseBooking with primary keys customerID, CourseID and CourseBookingID respectively. tblCourseBooking also stores CustomerID and CourseID to allow linking at runtime.

    I want to be able to retrieve all customers who have not booked on a specific course, but am clearly too stupid to work out the query. Any help appreciated. Thanks

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Show us your attempt at your query and we can help you from there.

    It smells like homework to me!

  • #3
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Haha....no, it's not homework. My problem is that I gave up programming a few years ago to be an abseiler and teach yoga and I have forgotten so much it is scary!

    I managed to make a query which returns all the people that are on the course, but I am not sure how to turn that around to give everyone who isnt on the course.

    Code:
    SELECT 
    tblcustomer.forename, 
    tblcustomer.surname, 
    tblcourse.coursename
    FROM tblcustomer
    LEFT  JOIN tblcoursebooking 
    ON tblcustomer.customerID = tblcoursebooking.customerID, tblcourse
    WHERE 
    tblcoursebooking.courseID = tblcourse.courseID 
    AND tblcourse.courseID =1
    Last edited by guelphdad; 08-16-2006 at 01:27 PM.

  • #4
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, I've been trying some more to get this work on a different pair of tables for folk going to yoga classes, but still cant get it quite right. If I use the following, I get all my customers who have never attended a class at all, but I just want those who have never attended a particular class as I am using a pair of listviews to store attendance.

    Code:
    SELECT 
    tblcustomer.CustomerID, 
    tblcustomer.Forename, 
    tblcustomer.Surname, 
    tblcustomer.PaymentMethodID, 
    tblclassattended.ClassDate, 
    tblclassattended.ClassID
    FROM tblcustomer
    LEFT  JOIN tblclassattended 
    ON tblcustomer.customerID = tblclassattended.customerID
    WHERE ISNULL( tblclassattended.customerid )
    ORDER  BY tblcustomer.Surname
    I want to add something like this in, but I cant get it to work at all, it just gives me no customers at all

    AND tblclassattended.ClassID = 2 AND tblclassattended.ClassDate = '2006-08-15'

    Any help would be greatly appreciated.
    Last edited by guelphdad; 08-16-2006 at 01:28 PM.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Can you show some sample data from your tables?

    Oh also please note, use [ code] and [/code] tags around your code. Remove the space in the first tag. I've edited both posts above.

  • #6
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, some samples from tblcustomer (note these are different tables to the original question, but I have the same problem.) I could do a dirty fix with VB, but I am sure I should be able to work it out cleaner with a query.
    Code:
    CREATE TABLE `tblcustomer` (
      `CustomerID` smallint(4) NOT NULL auto_increment,
      `Forename` varchar(36) NOT NULL default '',
      `Surname` varchar(36) NOT NULL default '',
      `Email` varchar(70) NOT NULL default '',
      `Mobile` varchar(15) NOT NULL default '',
      `Telno` varchar(15) NOT NULL default '',
      `Address` text NOT NULL,
      `PostNummer` text NOT NULL,
      `Ort` text NOT NULL,
      `PersNummer6` text NOT NULL,
      `PersNummer4` text NOT NULL,
      `ExtraInfo` text NOT NULL,
      `DateJoined` date NOT NULL default '0000-00-00',
      `PaymentMethodID` smallint(4) NOT NULL default '0',
      `PaymentDate` date NOT NULL default '0000-00-00',
      `PaymentAmount` decimal(10,0) NOT NULL default '0',
      `Nyetsbrev` smallint(1) NOT NULL default '0',
      `YogaHols` smallint(1) NOT NULL default '0',
      `WeekendCourses` smallint(1) NOT NULL default '0',
      `OtherSeminars` smallint(1) NOT NULL default '0',
      PRIMARY KEY  (`CustomerID`)
    ) TYPE=MyISAM AUTO_INCREMENT=33 ;
    
    #
    # Dumping data for table `tblcustomer`
    #
    INSERT INTO `tblcustomer` VALUES (2, 'jamie', 'ssssssss', 'jamie@sungarden.com', 'xxxx28', 'xxxx100', 'Kvarnv', '84010', 'Ljungaverk', '111111', '1111', '', '2004-08-01', 1, '2006-03-01', '1200', 1, 1, 1, 1);
    INSERT INTO `tblcustomer` VALUES (3, 'marie', 'nnnnnnnnn', 'marie@sungarden.com', '0xxxx1991', 'xxxx3100', 'Kvarnv', '84010', 'Ljungaverk', '222222', '2222', 'extra info', '2005-04-12', 2, '2006-02-04', '200', 0, 0, 0, 0);
    INSERT INTO `tblcustomer` VALUES (5, 'tom12', 'test12', 'email32', '09090909092', '56565656562', 'addressd2', 'postnummer2', 'ort2', '333333', '3333', 'sdfgadfgadfgag2', '2006-05-02', 1, '2006-05-02', '122', 0, 0, 0, 0);
    INSERT INTO `tblcustomer` VALUES (26, 'qqqqqqqqq', 'qqqqqqqq', '', '', '', '', '', '', '', '', '', '2006-05-05', 0, '2005-08-08', '0', 0, 0, 0, 0);
    INSERT INTO `tblcustomer` VALUES (27, 'j', 'j', '', '', '', '', '', '', '', '', '', '1001-01-01', 0, '1001-01-01', '0', 0, 0, 0, 0);
    and from tblclassattended

    Code:
    CREATE TABLE `tblclassattended` (
      `ClassAttendedID` smallint(6) NOT NULL auto_increment,
      `ClassID` smallint(6) NOT NULL default '0',
      `CustomerID` smallint(6) NOT NULL default '0',
      `ClassDate` date NOT NULL default '0000-00-00',
      PRIMARY KEY  (`ClassAttendedID`)
    ) TYPE=MyISAM AUTO_INCREMENT=26 ;
    
    #
    # Dumping data for table `tblclassattended`
    #
    
    INSERT INTO `tblclassattended` VALUES (3, 2, 2, '2006-08-15');
    INSERT INTO `tblclassattended` VALUES (4, 2, 3, '2006-08-15');
    INSERT INTO `tblclassattended` VALUES (5, 3, 27, '2006-08-15');
    INSERT INTO `tblclassattended` VALUES (8, 3, 26, '2006-08-15');
    Last edited by saorsa; 08-16-2006 at 02:11 PM.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You don't want a LEFT JOIN since you are looking for matched rows only.

    This line:
    WHERE ISNULL( tblclassattended.customerid )
    is also not necessary.

    Code:
    SELECT 
    tblcustomer.CustomerID, 
    tblcustomer.Forename, 
    tblcustomer.Surname, 
    tblcustomer.PaymentMethodID, 
    tblclassattended.ClassDate, 
    tblclassattended.ClassID
    FROM tblcustomer
    INNER  JOIN tblclassattended 
    ON tblcustomer.customerID = tblclassattended.customerID
    where tblclassattended.ClassID = 2
    AND tblclassattended.ClassDate = '2006-08-15'

  • #8
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That returns all the customers who are on the class that day, but I want to get all the customers in my customers table who are not already down as attending that day.

    I am using the info to populate 2 listviews, 1 on the left for the user to select from and one on the right that shows all the customers that attended a particular class. You select customers on the left, click a button and it adds them to the right.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Code:
    SELECT 
    tblcustomer.CustomerID, 
    tblcustomer.Forename, 
    tblcustomer.Surname, 
    tblcustomer.PaymentMethodID, 
    tblclassattended.ClassDate, 
    tblclassattended.ClassID
    FROM tblcustomer
    LEFT  JOIN tblclassattended 
    ON tblcustomer.customerID = tblclassattended.customerID
    where tblcustomer.CustomerID NOT IN
    (SELECT tblclassattended.CustomerID 
    from 
    tblclassattended
    where 
    tblclassattended.ClassID = 2
    AND tblclassattended.ClassDate = '2006-08-15')

  • #10
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I get the following error when I try that (tried it through phpmyadmin and through my vb app). My version is 4.0.21 i think


    MySQL said:
    #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
    'SELECT tblclassattended.CustomerID
    FROM tblclassattended
    WHERE
    Last edited by saorsa; 08-16-2006 at 06:05 PM.

  • #11
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by saorsa
    I get the following error when I try that (tried it through phpmyadmin and through my vb app). My version is 4.0.21 i think
    yes that is the problem. read the sticky thread at the top of the forum. you have an outdated version. If your version is older than 4.1 it doesn't support subqueries. In future posts please mention your version while it is out of date, that way someone won't come up with a query that won't work for you.
    Last edited by guelphdad; 08-16-2006 at 07:45 PM.

  • #12
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The good news of course is that the query can be re-written as a join.

    Sometimes it is hard finding a solution while looking at what someone else was doing. You were quite close in your original idea.
    This will give you what you want:

    Code:
    SELECT 
    tblcustomer.CustomerID, 
    tblcustomer.Forename, 
    tblcustomer.Surname, 
    tblcustomer.PaymentMethodID, 
    tblclassattended.ClassDate, 
    tblclassattended.ClassID
    FROM tblcustomer
    LEFT  JOIN tblclassattended 
    ON tblcustomer.customerID = tblclassattended.customerID
    AND tblclassattended.ClassID = 2 
    AND tblclassattended.ClassDate = '2006-08-15'
    WHERE tblclassattended.customerID is null
    order by tblcustomer.Surname

  • #13
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile

    Perfect! Thanks for all the time spent helping me. I'll see about upgrading my MySQL version at some point too....didnt realise I was out of date already.

  • #14
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    mysql puts out updates about once per month. the current production release version is something like 5.0.24 but you can get away with the latest version of 4.1. Check out the manual for major differences between 4 and 5.

    FYI: mysql 4.1 was the production release in October 2004 and 5 has been since October of 2005.

  • #15
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Damn, think I'll stick with 4.0.21. tried 4.1 and 5.0 and neither will allow me to login using phpmyadmin after the install. just tells me either I should upgrade my client or that access is denied. time to go back to being retro i think!


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •