SQL Query for select and multi[le fetch

kushal Bothra · New User Joined: 16 Feb 2011 Posts: 38 Location: Gurgoan

Hi All,

I need to write sql query for below requirement

say I have 3 tables Table A, B and C

I have to select employeeNum from A searchfor employeeName in B if found have to use that or if not found search employeeName in table C.

The requirement should be in one query.

Please help.

Thanks
Kushal

Pandora-Box · Posted: Sat Jun 09, 2012 6:38 pm

Looks like an Interview question

What have you tried so far to acheive the same???

and what should the SQL do when employee names are found from both the table B & C for same employee number and what should it do when same employee names are found in B & C for same employee number?? ( Should duplicates be eliminated or duplicates be retained) ( Hope it would give you some hint

)

dbzTHEdinosauer · Posted: Sat Jun 09, 2012 8:04 pm

Maybe the COALESCE function

Pandora-Box · Posted: Sat Jun 09, 2012 8:30 pm

Dick,

I was thinking about UNION/UNION ALL as well not very sure which is better when compared with coalesce

dbzTHEdinosauer · Posted: Sat Jun 09, 2012 11:52 pm

you have 1 column, 2 source (tables). tab2 and tab3

depending upon which has precedence when both are there,
controls your algorithm.
when both are there
then take first:
SELECT COALESCE(Tab2.NAME,Tab3.Name,'NO NAME')

or

when both are there
then take second:
SELECT COALESCE(Tab3.NAME,Tab2.Name,'NO NAME')

wack that on top of 2 JOINs.

probably a million different ways (and better, but i think that would work)

UNION with HAVING to filter out NULL would work,
but what to do when both or neither?
i could not think of a way to UNION.
and maybe my COALESCE won't work either, (no place to test).

Pandora-Box · Posted: Sun Jun 10, 2012 12:00 am

I understand may be will try that on monday and get back on coalesce

Thanks

kushal Bothra · New User Joined: 16 Feb 2011 Posts: 38 Location: Gurgoan

Hi Pandora-Box/dbzTHEdinosauer

Thanks for your replies

kushal Bothra · New User Joined: 16 Feb 2011 Posts: 38 Location: Gurgoan

Hi

I am trying

"SELECT Coalesce( B.employeeName, C.employeeName)

where A.employeeNum = B.employeeNum
AND A.employeeNum = C.employeeNum "

As my requirement is I take employeeNum of A search in B if found return the value if not found I search in C if found return.

So the above query will only work when we have employeeNum in both A and B and it dosent seems good.

Please suggest what can be possible resolution.

chandan.inst · Posted: Mon Jun 11, 2012 11:52 am

Hi Khushal,

Try below query

kushal Bothra · New User Joined: 16 Feb 2011 Posts: 38 Location: Gurgoan

Thanks Chandan

Its working for me ....

enrico-sorichetti · Posted: Mon Jun 11, 2012 2:13 pm

You said thanks to the wrong person...
the suggestion had already been given in a previous post by dbzTHEdinosauer

GuyC · Posted: Wed Jun 13, 2012 7:50 pm

It's pretty important to have left joins and not just inner joins.
Also this query will always try to join with table C even when it already found something in Table B.
IF it is only one column you need to find, you can solve it this way :

dbzTHEdinosauer · Posted: Wed Jun 13, 2012 7:53 pm

again, thx for the lesson Guy

chandan.inst · Posted: Thu Jun 14, 2012 9:07 am

Thanks GuyC..its pretty much informative..