MySQL Sub Queries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

MySQL Sub Queries

Post by buckit »

I have 2 tables:

ledger:
ledgerID - pk ai
ledgerYear - date
ledgerNotes - blob

and

ledgerExperts:
id - pk ai
expertFirstName - varchar
expertLastName - varchar


I currently create a table row for each entry into the ledger table. I then do another while loop inside that to list each expert into a table based on ledgerID. works perfectly fine that way


my problem is I am now writing in a search function... so I need to be able to put in 'Bob' and it returns all ledger rows that have an expert with first or last name starting with bob.

here is an example of what I have been trying:

Code: Select all

SELECT * FROM ledger, ledgerExperts WHERE ledger.ledgerID = ledgerExperts.ledgerID AND ledgerYear = 2010 AND ( expertFirstName LIKE '%jeff%' OR expertLastName LIKE '%jeff%' )
2 ledger rows have attached experts with the name LIKE 'jeff'... but that query returns 5 rows. ledgerID 7 has 4 rows of experts LIKE jeff and ledgerID 8 has 1 row of experts LIKE jeff... regardless if it has 100 rows LIKE 'jeff' I need it to return only 1 row per ledger row...

does that make sense?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: MySQL Sub Queries

Post by John Cartwright »

You need to explicitely use a LEFT join.

Code: Select all

SELECT * FROM ledgerExperts
LEFT JOIN ledger ON ledgerExperts.ledgerID = ledger.ledgerID
WHERE ledgerYear = 2010 AND ( expertFirstName LIKE '%jeff%' OR expertLastName LIKE '%jeff%' )
Although it may be wrong, since your schema doesn't show a ledgerID in both tables, but your query is operating under taht assumption.
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: MySQL Sub Queries

Post by buckit »

That didnt return anything different than what mine did.

it returned
ledgerID "expertFirstName" "expertLastName"
7 "Jeff 'the Man'" "Smith"
7 "Jeff 'the Man'" "Smith"
7 "Jeff 'the Man'" "Smith"
7 "Jeff 'the Man'" "Smith"
8 "Jeff" "Smith"

it returned that because ledgerExperts has 5 fields that match 'jeff'

I need it to return
ledgerID "expertFirstName" "expertLastName"
7 "Jeff 'the Man'" "Smith"
8 "Jeff" "Smith"



and yes... both tables have ledgerID... i forgot to put that in the example... both tables have a lot more columns... but that doesnt really matter.

ledger is the primary table. ledgerExperts are the table of experts per ledger entry. I need to search ledger by keyword, one of the searchable fields needs to be the experts... so if you search for an expert with the name of 'jeff' then it needs to return the ledger entries that have a matching expert to that entry. does that make sense?
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: MySQL Sub Queries

Post by liljester »

if you want distinct rows, try select distinct
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: MySQL Sub Queries

Post by buckit »

liljester wrote:if you want distinct rows, try select distinct
You rock! thank you so much! and I learned something!! double bonus! :)
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: MySQL Sub Queries

Post by liljester »

glad it worked for ya :)
Post Reply