sql query problem a simple one
-
hello friends
i got a simple query problem i have 2 tables
one is master and 1 is child
one is "candidatesmst" candiid is the common key between them
and another is "resumelist"
its a jobsite database
iam keeping candidates details in candidatesmst
and the list of all c.v's uploaded in the "resumelist" table
now i want to have a query which will display all the users name who haven't uploaded there resumes.
select fname from candidatesmst
where candiid not in(select candiid from resumelist)
something like that, but the above query doesn't work..
iam using backend as msaccess.
i want to show all the candidates names which have not uploaded there "resumes" there resume details are maintained in resumelist table.
a single candidate can upload many resumes
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
what do you get if you do a count(*) of your candidatemst table and a select distinct(candiid) from your resumelist table ?!
what are the ddls of the 2 tables ?!
**ASCII stupid question, get a stupid ANSI !!!** -
well iam asking is very simple but it doesnt seems to work with me
both tables have "candiid" as coommon key
a candidates registration details are stored in
candidatemst table
and he can upload as many resumes he wants those details are stored in
resumelist
its a simple query can u give the solution
iam maintaining the one of my website
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Select master.candiid from master left outer join child on Master.candiid = Child.candiid where child.candiid is null
-
Remi - I just placed a (huge) bet with myself that you will carry your laptop with you next week (& the week after that)!!
Do I win ?!
**ASCII stupid question, get a stupid ANSI !!!** -
The question is Will you win??
The answer is no because I have no laptop.
-
NO laptop
you will go into major withdrawal. your fingers will tremble and type meanless words on the table. You will be caught on film sneaking off in the dark of night to the nearest cyper cafe looking for a quick fix. The only answer is to purchase one before you go on vacaton.
Mike
-
are you people making joke of my question or what is it??
dear friends my query is too simple, but iam not getting any results,
if a candidate does not upload any resume his entry doesn't go in "resumelist" table
so the solution posted by "remi george" may not work
Select master.candiid from master left outer join child on Master.candiid = Child.candiid where child.candiid is null
becoz child.candiid is not present in child table
since "Remi george" is the person over 3000 posts, i will try his solution, thanks if it works
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Sukhoi we were not making fun of your question just having a little fun. If you would please post your DDLs some sample data and expected results we would be pleased to help you.
Mike
-
The key to Remi's query is the "Left Outer Join". This causes all rows to be returned even if there is no matching row in resumes. Actually, the query you posted in the original post should have worked, too. Do you get an error message or just not the results you expected?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Sukhoi I just noticed two things about the referensed post. First You had not tried Remi's solution which based on the information you posted should have worked. But with out DDLs and sample data there is no way to tell. Second, your statemt as to why it would not work does not agree with the information in your originial post. Please check this site for some useful information on getting help. http://www.aspfaq.com/etiquette.asp?id=5006
Thanks
Mike
-
nope it doesn't work,
the query i posted doesn't work
select fname from candidatesmst
where candiid not in(select candiid from resumelist)
i copied same schema in sql-server as well, but it doesn't return any result set, and iam quite surpised by it,
see my question is
its a jobsite database.
all the candidates data like name,lastname,address,phone are stored in candidatesrgmst
and all his resumes are stored in "resumelist" table
a candidate can upload n number of resumes,
suppose a candidates registers, his registration details will be stored in candidatesregmst
and if he uploads his "resume" c.v then its details will be stored in "resumelist" table
but actual c.v is stored in the harddisk not in the database. resume name is stored in the
resumelist table
i want to have an report which shows me data of the candidates who have not uploaded there "resumes"
c.v
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Lots of assumptions (read as guesses) here as you did not post your DDLs Basede on the information you posted I guessed that your tables look something more or less like the ones below. You will notice that the SQL posted by Remi works
Mike
if object_ID('tempdb..master') > 0
DROP TABLE master
IF object_id('tempdb..child') >0
DROP TABLE child
CREATE TABLE Master
(
FirstName varchar(20),
--other information here
candid int identity(1,1)
)
CREATE TABLE child
(
Candid int,
resumenumber int
--other information
)
--two candidates
Insert into master values('able')
Insert into master values('Baker')
--able has 2 resumes and baker has none
Insert into child Values(1,1)
Insert into child values(1,2)
Select master.FirstName
from master left outer join child on Master.candid = Child.candid
where child.candid is null
/*
returns Baker
*/
-
iam sorry, that u had to take so much efforts to solve my query..
thanks a lot, but you guessed it right, i will try your query, as i am not having the database with me now, iam at other location,
so i could not post the schema.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
And you say this didn't work???
Select master.candiid from master left outer join child on Master.candiid = Child.candiid where child.candiid is null
Did you actually change the table names and columns names to match your DDL?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply