PDA

Click to See Complete Forum and Search --> : Returning rows not found

kurent
11-03-2008, 01:43 AM
How can you make the DB return a value that has not been found and disaplay it as a null?

For example:
SELECT name, hoursWorked from WORK
WHERE name = 'Michael' OR name = 'Tom'

In the table WORK Michael has 10 hours logged, but Tom hasn't worked and he is not even in the table. But the result I want would be:

Michael 10
Tom 0

Can I do this with SQL?
traigo
11-03-2008, 09:20 AM
Not sure which server you are using, no multiple inserts if on MSSQL. You should be able to build this dynamically with the INSERT INTO part in a loop (assuming you don't always want just Michael and Tom. the ISNULL function might not be compatible with your server.

DECLARE @TBL TABLE ( NAME1 VARCHAR(255) )
INSERT INTO @TBL VALUES('MICHAEL')
INSERT INTO @TBL VALUES('TOM')
SELECT NAME1, ISNULL(hoursWorked, 0) FROM @TBL T
LEFT OUTER JOIN WORK ON T.NAME1 = WORK."NAME"
chazzy
11-03-2008, 05:25 PM
just wondering, where does Tom come from? Why Tom and not Sam or asfdlajlsag as the name? Is it from another table? if so you can do a left join.
kurent
11-04-2008, 01:23 AM
The names come from another table, but not all of them. The user selects which workers he wants to check for workHours. So short of inserting these into a new table this is not possible with only SQL?
chazzy
11-04-2008, 04:48 AM
The names come from another table, but not all of them. The user selects which workers he wants to check for workHours. So short of inserting these into a new table this is not possible with only SQL?

i dont get what you mean by 'but not all of them' either they are coming from your workers table or not. it doesnt make sense to me.
kurent
11-04-2008, 07:37 AM
The user selects only some workers from the table for which he wants to check hoursWorked.
chazzy
11-04-2008, 09:50 AM
ok so then they are always in this parent table

then like i said do a left join from the workers table to the work table
kurent
11-05-2008, 07:32 AM
Looks like I will be doing this programatically in the Java Servlet. I just found out that the list of workers comes from more then one table.
chazzy
11-05-2008, 11:19 AM
Looks like I will be doing this programatically in the Java Servlet. I just found out that the list of workers comes from more then one table.

You could do a union as well