Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20

    Unanswered: Help in CASE function

    Thanks Rudy for the answer of my previous thread.
    Here's another scenario of my problem that i hardly get the correct query.

    I have a tables with fields and sample records in my database;

    Table: tblPo
    FIELDS RECORD1
    po_number: PO10001
    entered_by: chris
    approved_by: albert

    Table: tblUser
    FIELDS RECORD1 RECORD2
    user_id: chris albert
    first_name: Christopher Alberto
    mi: S W
    last_name: Lambert Del Puerto


    So this is how my database is structured. entered_by and approved_by fields in tblPo are both existed in the tblUser table as user_id so they would share the same table tblUser for First, Middle,Last Names since they both also must be a valid user to create and approve a PO.

    Now, I need to get a result like this:

    PO # Prepared by Approved by
    PO10001 Christopher S. Lambert Alberto W. Del Puerto


    My problem now is how to make a query to generate this report. I already joined the two tables with tblPo.user_id to tblUser.user_id and tblPo.approved_by to tblUser.user_id. Unfortunately, both Preparedby and Approvedby resulted to NULL values.

    Any help??? thanks in advance

    Bernie

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Here you go :

    Code:
    use pubs
    go
    create table tblPO (po_number varchar(100),entered_by varchar(100),approved_by varchar(100))
    go
    create table tblUser (user_id varchar(100),first_name varchar(100),mi varchar(100),last_name varchar(100))
    go
    insert into tblPO values('PO10001','chris','albert')
    insert into tbluser values ('chris','Christopher','S','Lambert')
    insert into tbluser values ('albert','Alberto','W','Del Puerto')
    go
    select 
    	a.po_number, 
    	isnull(b.first_name,'') +' '+ isnull(b.mi,'') +'. '+ isnull(b.last_name,'') Prepared_by,
    	isnull(c.first_name,'') +' '+ isnull(c.mi,'') +'. ' + isnull(c.last_name,'') Approved_by 
    from 
    	tblPO a, 
    	tbluser b, 
    	tbluser c 
    where 
    	a.entered_by = b.user_id 
    	and a.approved_by = c.user_id
    
    go
    drop table tbluser
    go
    drop table tblpo
    go
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
    create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
    insert into tblpo values ('PO10001','chris','albert')
    insert into tbluser values ('chris','Christopher','S','Lambert')
    insert into tbluser values ('albert','Alberto','W','Del Puerto')

    select
    tblpo.po_number as po#,
    rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
    rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
    from tblpo
    left outer join tbluser as EnteredBy
    on EnteredBy.user_id = tblpo.entered_by
    left outer join tbluser as ApprovedBy
    on ApprovedBy.user_id = tblpo.approved_by

    po# PreparedBy ApprovedBy
    ---------- ----------------------- ----------------------
    PO10001 Christopher S. Lambert Alberto W. Del Puerto

    (1 row(s) affected)

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by cvandemaele
    create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
    create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
    insert into tblpo values ('PO10001','chris','albert')
    insert into tbluser values ('chris','Christopher','S','Lambert')
    insert into tbluser values ('albert','Alberto','W','Del Puerto')

    select
    tblpo.po_number as po#,
    rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
    rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
    from tblpo
    left outer join tbluser as EnteredBy
    on EnteredBy.user_id = tblpo.entered_by
    left outer join tbluser as ApprovedBy
    on ApprovedBy.user_id = tblpo.approved_by

    po# PreparedBy ApprovedBy
    ---------- ----------------------- ----------------------
    PO10001 Christopher S. Lambert Alberto W. Del Puerto

    (1 row(s) affected)
    Thats better .. that would work even if userid does not exist in tblUser ... but remember to take care of null values .. coz null added to anything yields null
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20

    Thanks

    SALAMAT! Thank you guyz....

    I can proceed now with my report...


    Bernie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •