Select Query

I have a candidates table Linked to a Language_Link table.
In the Language_Link table I have 2 columns, dsLanguageId and dsCandidateID.
I want to do get all the candidates who speak English and afrikaans what would the select query look like
JohnRockAsked:
Who is Participating?
 
RyancConnect With a Mentor Commented:
select  A.dsCandidateID
 
from  dsCandidates as A,
        dsLanguage as B,
        dsLanguage_Link as C
 
where A.dsCandidateID = C.dsCandidateID
and B.dsLanguageID = C.dsLanguageID
and C.dsLanguageID in (select dsLanguageId from dsLanguage as Z where Z.dsLanguageID = 1 or Z.dsLanguageID = 4 or Z.dsLanguageID = 6 or Z.dsLanguageID = 8)
 
group by A.dsCandidateID having count(*) = 4
order by A.dsCandidateID
0
 
Jeremy_DCommented:
SELECT *
 FROM Candidates
  INNER JOIN Language_Link ON Candidates.dsCandidateID = Language_Link.dsCandidateID
 WHERE Language_Link.dsLanguageID = 'EN' Or Language_Link.dsLanguageID = 'AF'

You'll have to change the 'EN' and 'AF' in the where-clause to reflect the data in your Language_Link Table (could be a string like these or a numerical ID, depending on how you designed this table).
0
 
Jeremy_DCommented:
Sorry, If you want everyone that speaks English *and* Afrikaans then the "OR" in the WHERE-clause should be an "AND" of course.

Sorry, early monday morning and all that :)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
RyancCommented:
sorry Jeremy,

if you change the OR to the AND, what will happen is that SQL will look for A SINGLE RECORD in the Language_Link table with a value of 'En' AND 'Afr' - clearly such a record will never exist ...

check my comment for the workable solution ...

cheers
Ryan
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT *
 FROM Candidates
  INNER JOIN (
select distinct dsCandidateID from
Language_Link
 WHERE Language_Link.dsLanguageID = 'EN' Or Language_Link.dsLanguageID = 'AF'
) AS Language_Link
ON Candidates.dsCandidateID = Language_Link.dsCandidateID


0
 
JohnRockAuthor Commented:
Adjusted points from 50 to 90
0
 
JohnRockAuthor Commented:
Comments to AngelIII.
Your query brings back everyone that speaks english or afrikaans, which is incorrect. I only want the people that speak Eng and Afr.
0
 
Jeremy_DCommented:
Having trouble posting comments. Third attempt:

It's usually faster (given the right indexes) to use a second join in stead of a sub-query, like this (pending other 'monday-issues' :o) ):

SELECT *
 FROM Candidates
  LEFT JOIN Language_Link AS l1 ON Candidates.dsCandidateID = l1.dsCandidateID
  LEFT JOIN Language_Link AS l2 ON Candidates.dsCandidateID = l2.dsCandidateID
 WHERE l1.dsLanguageID = 'EN' AND l2.dsLanguageID = 'AF'

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.