CODEWITHSUNDEEP
mysqlsql
user3258622
user3258622

Reputation: 5

SQL join - Looking to search one table for keyword, two other tables

I'm not well versed with SQL joins. Basically I'm trying to be able to search a company name for a keyword while filtering by a certain stateID (lets say 7). Here's my setup:

company table - companyID, company_name

states table - stateID, state

company_states - companyID, stateID

So far, I've all I've been able to come up with is:

select * from company where `exchange-name` like 
'%associates%' inner join company_states on ....

I need company_states' stateID to match 7 as well as match one of the companies with names that contain "associates", for example. Any help would be much appreciated.

Upvotes: 0

Views: 95

Answers (2)

Lee
Lee

Reputation: 10603

select c.* from company AS c LEFT JOIN company_states AS st ON c.companyID = st.companyID where `exchange-name` like '%associates%' AND st.stateID = 7

Explanation:

SELECT c.* /* Only select columns from company table, not the joined linker table */
FROM company AS c /* Define a table alias, because im lazy */
LEFT JOIN company_states AS st /* Join the linker table in and alias again */
ON c.companyID = st.companyID /* We tell MySQL what column in company table is to match the column in linker table */
WHERE `exchange-name` like '%associates%' /* filter the companies by name */
AND st.stateID = 7 /* filter out companies who dont have a linked state of ID 7 */

I tend to use LEFT JOIN as my default join type, due to the way i structure my data and i've rarely ever needed to use a different one.

But you can find a nice and easy to understand guide of the joins here: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Upvotes: 0

timo.rieber
timo.rieber

Reputation: 3867

One solution is to join all three existing tables together and write your where clause as needed. Suggestion:

select
    c.company_name

from
    company c join company_states cs on c.companyID = cs.companyID
    join states s on cs.stateID = s.stateID

where
    c.company_name like '%associates%'
and
    s.stateID = 7

Upvotes: 1

Related Questions