Dealing with NULL records
-
Hi guys,
This question is actually about Sybase sql (Apologies for putting it in this discussion, but I couldn't see a discussion on Sybase in the listings), but from what I gather this is pretty similar to Microsoft SQL, so I'm wondering if you guys can help me...
I have a table where each record stores different types of information, linking to another 'main' table...On second thoughts, an example might be easier to understand: I have some tables which store information regarding books in a library. There is a table BOOK and a table ALT_BOOK_ID. The ALT_BOOK_ID table lists information about a particular book in table BOOK (i.e. one to many relationship).
For example, in table BOOK:
ID: 1234
Name: To kill a mockingbird
in table ALT_BOOK_ID:
ID (Foreign Key): 1234
ALTERNATE_TYPE: ISIN
VALUE: 0987654
ID (Foreign key): 1234
ALTERNATE_TYPE: BARCODE
VALUE: 5678
I'm trying to write some sql which will list out all of the different code types for a particular book. I've come up with:
select
isin.ID,
isin.VALUE AS "ISIN",
barcode.VALUE AS "BARCODE"
from ALT_BOOK_ID isin, ALT_BOOK_ID barcode
WHERE
isin.ID = '1234'
AND barcode.ID = '1234'
AND isin.ALTERNATE_TYPE = 'ISIN'
and barcode.ALTERNATE_TYPE = 'BARCODE'
GROUP BY barcode.ALTERNATE_TYPE, isin.ALTERNATE_TYPE, barcode.VALUE, isin.VALUE, barcode.ID, isin.ID
This would give something like:
ID ISIN BARCODE
1234 0987654 5678
This works OK, BUT the problem I have is what if for some reason, a book doesn't have an ISIN or BARCODE attached to it - Then the whole line dissapears, but what I want is for the output to show:
ID ISIN BARCODE
1234 0987654 "not found"
I've tried a few things like ISNULL() etc. but nothing seems to work
Does anyone have any ideas? I appreciate your help!!
Many thanks,
Regards,
Daniel
-
SELECT ID, ISIN = MAX(CASE ALTERNATE_TYPE WHEN 'ISIN' THEN VALUE ELSE '' END) , BARCODE = MAX(CASE ALTERNATE_TYPE WHEN 'BARCODE' THEN VALUE ELSE '' END) FROM ALT_BOOK_ID GROUP BY ID -
Thanks...I will try this out when I get back to work, but what happens if I want to extend this sql to reference an ID in the BOOK table rather than ALT_BOOK_ID (this is actually just a part of a larger query that I am working on with several joins)? Will this still work?
-
--This should do the trick, add where clause as appropriate to limit the data retrieved from the book table
select b.id,
b.name,
BARCODE = coalesce((select [value] from alt_book_id where id = b.id and alternate_type = 'barcode'),'Not Found'),
ISIN = coalesce((select [value] from alt_book_id where id = b.id and alternate_type = 'isin'),'Not Found')
from book_ b
---------------------------------------------------
-- BELOW is an example/test script
--------------------------------------------------
if exists (select 1 from sysobjects where id = object_id('book_') and type = 'U') drop table book_
go
if exists (select 1 from sysobjects where id = object_id('alt_book_') and type = 'U') drop table alt_book_
go
create table book_ (id_ int,name_ varchar(10))
create table alt_book_ (id_ int, alt_type_ varchar(10), alt_value_ varchar(10))
go
insert into book_ values (1,'Book #1')
insert into alt_book_ values (1,'ISIN','B1-0001')
insert into alt_book_ values (1,'BARCODE','B1-01')
insert into book_ values (2,'Book #2')
insert into alt_book_ values (2,'ISIN','B2-0002')
insert into alt_book_ values (2,'BARCODE','B2-02')
insert into book_ values (3,'Book #3')
insert into alt_book_ values (3,'ISIN','B3-0003')
insert into book_ values (4,'Book #4')
insert into alt_book_ values (4,'BARCODE','B4-04')
insert into book_ values (5,'Book #5')
go
--This will do the trick, add where clause as appropriate to limit the data retrieved from the book table
select b.id_,
b.name_,
BARCODE = coalesce((select alt_value_ from alt_book_ where id_ = b.id_ and alt_type_ = 'barcode'),'Not Found'),
ISIN = coalesce((select alt_value_ from alt_book_ where id_ = b.id_ and alt_type_ = 'isin'),'Not Found')
from book_ b
/* RESULTS:
id_ name_ BARCODE ISIN
----------- ---------- ---------- ----------
1 Book #1 B1-01 B1-0001
2 Book #2 B2-02 B2-0002
3 Book #3 Not Found B3-0003
4 Book #4 B4-04 Not Found
5 Book #5 Not Found Not Found
*/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply