March 28, 2007 at 7:06 am
hremp_adp table
EmpID Name
11111 ABC
11112 ABE
11113 ABCE
license table
EmplID License Verify Expired EMPLSTS
11112 RN Y 7/17/07 OB
11113 DR N OC
I want to write a query when the end user input 11111.
It return record set since no record in the license table
EmpID name License
11111 ABC
if end user input 11112
EmpID name License
11112 ABE RN
Since this is Verify and not Expired compared with currrent data.
What can I do that ? if I use left join it return no return on the criteria 111
SELECT HREMP_adp.[NAME] AS Expr1, HREMP_adp.EMPID AS Expr2, License.LICENSE FROM HREMP_adp left JOIN
License ON HREMP_adp.EMPID = License.EMPID
WHERE (HREMP_adp.EMPID = N'111111')
it return no record. if I use cross join , it return so many records.
Thx.
March 28, 2007 at 7:12 am
Use LEFT JOIN
_____________
Code for TallyGenerator
March 28, 2007 at 7:23 am
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],COALESCE(l.LICENSE,'') AS [LICENSE]
FROM (SELECT N'111111' AS [EMPID]) x
LEFT JOIN @HREMP_adp a ON a.EMPID = x.EMPID
LEFT JOIN @License l ON l.EMPID = x.EMPID
Far away is close at hand in the images of elsewhere.
Anon.
March 28, 2007 at 8:19 am
Do understand this part:
(SELECT N'111111' AS [EMPID]) x
LEFT JOIN @HREMP_adp a ON a.EMPID = x.EMPID
LEFT JOIN @License l ON l.EMPID = x.EMPID
where those @HREMP_adp variable come from ?
March 28, 2007 at 8:23 am
Sorry forgot to remove @ I used table variables to test query
Should have been
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],COALESCE(l.LICENSE,'') AS [LICENSE]
FROM (SELECT N'111111' AS [EMPID]) x
LEFT JOIN HREMP_adp a ON a.EMPID = x.EMPID
LEFT JOIN License l ON l.EMPID = x.EMPID
Far away is close at hand in the images of elsewhere.
Anon.
March 28, 2007 at 8:51 am
David:
Thx. Your query is working. If I added the condition on the license which I have two field verify and not expired. how will i write this query. Also as 111111 is variable. I need to modify it as parameter. so...
..
March 28, 2007 at 9:08 am
Replace N'111111' with the parameter, eg @EMPID
Add any further checks to the ON clause of the License JOIN, eg if the test for not expired is EXPIRED = 0
then your query would look like this
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],COALESCE(l.LICENSE,'') AS [LICENSE]
FROM (SELECT @EMPID AS [EMPID]) x
LEFT JOIN HREMP_adp a
ON a.EMPID = x.EMPID
LEFT JOIN License l
ON l.EMPID = x.EMPID
AND l.EXPIRED = 0
Far away is close at hand in the images of elsewhere.
Anon.
March 28, 2007 at 11:16 am
David;
right now I added Status in the license table.
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],COALESCE(l.LICENSE,'') AS [LICENSE] , COALESCE(l. EMPLSTS,'')
FROM (SELECT @EMPID AS [EMPID]) x
LEFT JOIN HREMP_adp a
ON a.EMPID = x.EMPID
LEFT JOIN License l
ON l.EMPID = x.EMPID
AND l.vErify = 'y'
The table if they have EMPLSTS in the license table did not show EMPLSTS. It show as '' there. I do not know why as l.vErify = 'y' condition since the record will not show on the left join.
Thx.
March 29, 2007 at 2:07 am
Not sure what your question is but based on your last query
a parameter of '11111' will return
EMPID NAME LICENSE EMPLSTS
11111 ABC NULL NULL
a parameter of '11112' will return
EMPID NAME LICENSE EMPLSTS
11112 ABE RN OB
a parameter of '11113' will return
EMPID NAME LICENSE EMPLSTS
11113 ABCE NULL NULL
COALESCE selects the first non null value in the list and will give you an empty string (or '' as you describe) for the NULLs above
If the query is not returning License or EMPLSTS for a paramater of 11112 then check the database and/or column collation to see if it is case sensitive as you are matching lower case 'y'
Far away is close at hand in the images of elsewhere.
Anon.
March 30, 2007 at 7:14 am
a parameter of '11113' will return. since it has EMPLSTS.
EMPID NAME LICENSE EMPLSTS
11113 ABCE NULL OC
I do not know how to write this. Thx.
March 30, 2007 at 7:41 am
Use a CASE statement
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],
CASE WHEN l.Verify = 'Y' THEN COALESCE(l.LICENSE,'') ELSE '' END AS [LICENSE],COALESCE(l.EMPLSTS,'') AS [EMPLSTS]
FROM (SELECT @EMPID AS [EMPID]) x
LEFT JOIN @HREMP_adp a
ON a.EMPID = x.EMPID
LEFT JOIN @License l
ON l.EMPID = x.EMPID
or add another LEFT JOIN
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],COALESCE(l.LICENSE,'') AS [LICENSE],COALESCE(e.EMPLSTS,'') AS [EMPLSTS]
FROM (SELECT @EMPID AS [EMPID]) x
LEFT JOIN @HREMP_adp a
ON a.EMPID = x.EMPID
LEFT JOIN @License l
ON l.EMPID = x.EMPID
AND l.Verify = 'Y'
LEFT JOIN @License e
ON e.EMPID = x.EMPID
Far away is close at hand in the images of elsewhere.
Anon.
March 30, 2007 at 7:56 am
thx. the case query work but
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],COALESCE(l.LICENSE,'') AS [LICENSE],COALESCE(e.EMPLSTS,'') AS [EMPLSTS]
FROM (SELECT @EMPID AS [EMPID]) x
LEFT JOIN @HREMP_adp a
ON a.EMPID = x.EMPID
LEFT JOIN @License l
ON l.EMPID = x.EMPID
AND l.Verify = 'Y'
LEFT JOIN @License e
ON e.EMPID = x.EMPID
or
SELECT x.EMPID,COALESCE(a.[NAME],'') AS [NAME],COALESCE(l.LICENSE,'') AS [LICENSE],COALESCE(e.EMPLSTS,'') AS [EMPLSTS]
FROM (SELECT @EMPID AS [EMPID]) x
LEFT JOIN @HREMP_adp a
ON a.EMPID = x.EMPID
LEFT JOIN @License l
ON l.EMPID = x.EMPID
AND l.Verify = 'Y'
LEFT JOIN @License e
ON e.EMPID = x.EMPID AND e.Verify = 'N'
did not work. I tested with e.Verify = 'N'
March 30, 2007 at 8:03 am
The last line
ON e.EMPID = x.EMPID and AND l.Verify = 'N'
has too many AND's and Verify should be referencing alies e not l, ie
ON e.EMPID = x.EMPID AND e.Verify = 'N'
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply