|
SQL problem in selecting multi tables
okay i have his mysql query...
SELECT members.memberID, members.salutation, members.firstName, members.middleName, members.lastName, members.suffix, members.company, addresses.address1, addresses.address2, addresses.city, addresses.state, addresses.postalCode, addresses.country, addresses.memberID, email.email, email.memberID, phonenumbers.phoneNumber, phonenumbers.memberId, subscriptions.year, subscriptions.memberID FROM members, addresses, email, phonenumbers, subscriptions WHERE subscriptions.year = '%s' AND subscriptions.memberID = members.memberID AND subscriptions.memberID = addresses.memberID AND subscriptions.memberID = email.memberID AND subscriptions.memberID = phonenumbers.memberID ORDER BY members.lastName, members.firstName, members.company LIMIT 0, 10my problem is its a huge query so Im trying to limit it to so many at a time... its supposed to have over 5000 results... anyway the only limit that works is limit 0, 10 if you do anything else 5, 10 it doesnt work 0, 50 doesnt work... only 0, 10 works... and when I do 0, 10 the query returns blake firstName, middleName, lastName, and a few others... and when I do a print_r() on the $result it shows them blank as well and there is most def data in the database and there is also no typos for that... basically im in a heap of mess...
3 Answers
SQL statement:
SELECT
subscriptions.year,
subscriptions.memberID
members.memberID,
members.salutation,
members.firstName,
members.middleName,
members.lastName,
members.suffix,
members.company,
addresses.address1,
addresses.address2,
addresses.city,
addresses.state,
addresses.postalCode,
addresses.country,
addresses.memberID,
email.email,
email.memberID,
phonenumbers.phoneNumber,
phonenumbers.memberId,
FROM
subscriptions
left outer join members on subscriptions.memberID = members.memberID
left outer join addresses on subscriptions.memberID = addresses.memberID
left outer join email on subscriptions.memberID = email.memberID
left outer join phonenumbers on subscriptions.memberID = phonenumbers.memberID
WHERE
subscriptions.year = '%s'
ORDER BY
members.lastName,
members.firstName,
members.company
LIMIT 0, 10
Posted: is_set 1 of 1 people found this answer helpful. Did you? Yes No
Depending on how many e-mail addresses and phone numbers your members have on average, there might be a problem with your query. Suppose you have the records (most fields omitted for brevity):
members addresses phoneNumbers
memberId | firstName | lastName memberId | city memberId | phoneNumber
--------------------------------- ------------------- ----------------------
839 | Joe | Hallenbeck 839 | New York 839 | 111-11111
839 | L.A. 839 | 222-22222
Then what your join will do is produce not one record for Joe Hallenbeck, but four:memberId | firstName | lastName | city | phoneNumber ----------------------------------------------------------- 839 | Joe | Hallenbeck | New York | 111-1111 839 | Joe | Hallenbeck | New York | 222-2222 839 | Joe | Hallenbeck | L.A. | 111-1111 839 | Joe | Hallenbeck | L.A. | 222-2222don't know if this is what you want, but you should know that this query might produce far more result records than there are members in the database. For more advice you should post some DDL statements (create table etc.), as Konerak pointed out. Posted: MacOS 1 of 1 people found this answer helpful. Did you? Yes No
I would like to suggest the use of left outer join and indexing in the fields which you have used for linking both tables and applied where condition. it should increase performanace...
as I got the performance in records of 50,000 rows in main table and multiple child tables with ~10,000 rows... try left outer join properly and do proper indexing Posted: Go 1 of 1 people found this answer helpful. Did you? Yes No |
'adgower' Status
Questions : 1
Answers : 0 Comments : 0 Related Questions
|
FOLLOW AWCORE
- Follow Us:
© Advanced Web Core. All rights reserved