MySQL Sub Queries
Posted: Mon Jul 19, 2010 3:03 pm
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:
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?
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%' )does that make sense?