zeep25

I have three tables like this: subjects +-----------+-----------+ | SubjectID | Name | +-----------+-----------+ | 50 | English | | 51 | Math | | 52 | History | | 53 | Arts | | 54 | Music | +-----------+-----------+ grades +---------+--------------+ | GradeID | Name | +---------+--------------+ | 10 | Tenth | | 11 | Eleventh | | 12 | Twelfth | | 13 | College | | 14 | HomeSchool | +---------+--------------+ products +-----------+-----------+----------+--------------+ | ProductID | SubjectID | GradeID | Name | +-----------+-----------+----------+--------------+ | 84512 | 50,53 | 10,11,12 | Product 1 | | 84513 | 54,50,51 | 11,12 | Product 2 | | 84514 | 53,51 | 14,12,13 | Product 3 | +-----------+-----------+----------+--------------+ As you can see, each product has multiple subjects and grades associated with it. The way I am getting the required info for each product. I am doing three queries like this: SELECT * FROM products WHERE ProductID='84512' then the use the result from that to do this: SELECT * FROM subjects WHERE FIND_IN_SET(SubjectID, '50,53') and then finally get the grade levels for the product 84512 SELECT * FROM grades WHERE FIND_IN_SET(GradeID, '10,11,12') Now I wanted to know can i use JOIN or WHERE to get all the data (the two different subject names, and the three different grade levels) in one query? Info: Processor: 4 x Xeon 3 Ghz OS: RedHat Linux Memory : 2 GB Mysql: 4.1.22-standard

I have three tables like this: receipts +------------+-------+------+------------+-----------+ | customerId | recID | plan | recDate | processed | +------------+-------+------+------------+-----------+ | 9742 | 900 | 1 | 1178726955 | 0 | | 2188 | 899 | 1 | 1178670358 | 0 | | 2657 | 898 | 1 | 1178639090 | 0 | | 9699 | 897 | 1 | 1178627303 | 0 | | 1247 | 896 | 1 | 1178570875 | 0 | +------------+-------+------+------------+-----------+ customer +-------------------------------+----------+-----------+ | Email | LastName | FirstName | +-------------------------------+----------+-----------+ | user1@domain.com | lname | fname | | user2@domain.com | lname | fname | | user3@domain.com | lname | fname | | user4@domain.com | lname | fname | | user5@domain.com | lname | fname | +-------------------------------+----------+-----------+ user +----------+--------------+-------------+ | Approved | UserUpgraded | UpgradeDate | +----------+--------------+-------------+ | 1 | 1 | 1176134955 | | 1 | 1 | 1176078358 | | 1 | 1 | 1176047090 | | 1 | 1 | 1176035303 | | 1 | 1 | 1175978875 | +----------+--------------+-------------+ each table is actually much bigger than that, over 10,000 entries ... now i need to get all three table's info at once ... this query: SELECT DISTINCT receipts.customerId, receipts.recID, receipts.plan, receipts.recDate, receipts.processed, customer.Email, customer.LastName, customer.FirstName FROM `receipts`, `customer` WHERE receipts.customerId = customer.CustomerId GROUP BY receipts.customerId ORDER BY receipts.recID DESC LIMIT 5; works perfectly and produces the following result: +------------+-------+------+------------+-----------+-------------------------------+----------+-----------+ | customerId | recID | plan | recDate | processed | Email | LastName | FirstName | +------------+-------+------+------------+-----------+-------------------------------+----------+-----------+ | 9742 | 900 | 1 | 1178726955 | 0 | user1@domain.com | lname | fname | | 2188 | 899 | 1 | 1178670358 | 0 | user2@domain.com | lname | fname | | 2657 | 898 | 1 | 1178639090 | 0 | user3@domain.com | lname | fname | | 9699 | 897 | 1 | 1178627303 | 0 | user4@domain.com | lname | fname | | 1247 | 896 | 1 | 1178570875 | 0 | user5@domain.com | lname | fname | +------------+-------+------+------------+-----------+-------------------------------+----------+-----------+ 5 rows in set (0.01 sec) now this is where i get messed up ... as soon as i add my third and last table into play .. the query takes over 2 mins to process and uses 80+% CPU ... this is the query i try to run: SELECT DISTINCT receipts.customerId, receipts.recID, receipts.plan, receipts.recDate, receipts.processed, customer.Email, customer.LastName, customer.FirstName, user.Approved, user.UserUpgraded, user.UpgradeDate FROM `receipts`, `customer`, `user` WHERE receipts.customerId=customer.CustomerId AND customer.Email=user.UserEmail GROUP BY receipts.customerId ORDER BY receipts.recID DESC; LIMIT 5; the reason im doing distinct and group by customer id from the receipts table is because there are multiple receipts for each customer ... and i want to get the latest one! the user table also has an email column, thats how the user and customer tables are linked ... am i doing something wrong ? any help would be appreciated Info: Processor: 4 x Xeon 3 Ghz OS: RedHat Linux Memory : 2 GB Mysql: 4.1.22-standard