SQL Join Query Syntax

ozzii · #1 (**permalink**) 05-26-08, 06:55

Hi,

I have a database with the following tables:

tbl_joblistings
listing_ID - Primary key
job_title
job_desc

tbl_job_languages
listing_ID
language_ID

tbl_languages
language_ID - Primary key
language_Name - Unique

tbl_job_industries
listing_ID
industry_ID

tbl_industries
industry_ID - Primary key
industry_Name - Unique

tbl_joblistings and tbl_languages has a 1 to many relationship with tbl_job_languages and table tbl_joblistings and tbl_industries has a 1 to many relationship with tbl_job_industries. Both tbl_job_languages and tbl_job_industries is made up of a compound primary key.

what is the sql syntax to join these tables if I want to search a job by language_Name and industry_Name. E.g. select everything from tbl_joblistings where language_name is French and industry sector is IT?

Any help will be much appreciated. Cheers.

r937 · #2 (**permalink**) 05-26-08, 07:22

Code:

SELECT tbl_joblistings.listing_ID
     , tbl_joblistings.job_title 
     , tbl_joblistings.job_desc  
  FROM tbl_joblistings 
INNER
  JOIN tbl_job_languages
    ON tbl_job_languages.listing_ID = tbl_joblistings.listing_ID
INNER
  JOIN tbl_languages
    ON tbl_languages.language_ID = tbl_job_languages.language_ID
   AND tbl_languages.language_Name = 'French'
INNER
  JOIN tbl_job_industries
    ON tbl_job_industries.listing_ID = tbl_joblistings.listing_ID
INNER
  JOIN tbl_industries
    ON tbl_industries.industry_ID = tbl_job_industries.industry_ID
   AND tbl_industries.industry_Name = 'IT'

Internet Services

Web Development

Digital Marketing

Consumer Tech