Page 1 of 1

MySQL Sub Queries

Posted: Mon Jul 19, 2010 3:03 pm
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?

Re: MySQL Sub Queries

Posted: Mon Jul 19, 2010 4:30 pm
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.

Re: MySQL Sub Queries

Posted: Mon Jul 19, 2010 7:49 pm
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?

Re: MySQL Sub Queries

Posted: Mon Jul 19, 2010 8:20 pm
by liljester
if you want distinct rows, try select distinct

Re: MySQL Sub Queries

Posted: Mon Jul 19, 2010 8:40 pm
by buckit
liljester wrote:if you want distinct rows, try select distinct
You rock! thank you so much! and I learned something!! double bonus! :)

Re: MySQL Sub Queries

Posted: Mon Jul 19, 2010 8:43 pm
by liljester
glad it worked for ya :)