...

View Full Version : Linked table query



saorsa
07-25-2006, 05:09 PM
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

guelphdad
07-25-2006, 05:59 PM
Show us your attempt at your query and we can help you from there.

It smells like homework to me!

saorsa
07-25-2006, 08:06 PM
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.



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

saorsa
08-16-2006, 10:01 AM
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.



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.

guelphdad
08-16-2006, 01:29 PM
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.

saorsa
08-16-2006, 02:05 PM
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.


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



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');

guelphdad
08-16-2006, 02:53 PM
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.



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'

saorsa
08-16-2006, 03:03 PM
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.

guelphdad
08-16-2006, 04:00 PM
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')

saorsa
08-16-2006, 05:58 PM
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

guelphdad
08-16-2006, 07:22 PM
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.

guelphdad
08-16-2006, 07:45 PM
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:



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

saorsa
08-16-2006, 10:18 PM
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.

guelphdad
08-16-2006, 11:32 PM
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.

saorsa
08-18-2006, 08:35 AM
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!

guelphdad
08-18-2006, 01:11 PM
MySQL changed their password algorithm with version 4.1 and newer. You can upgrade your version of phpmyadmin or you can just use old passwords with your logins and that will work as well.

See this area of the manual on old passwords (http://dev.mysql.com/doc/refman/5.0/en/old-client.html) for more details.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum