SQL Server Performance

need help again on sql query

Discussion in 'General Developer Questions' started by v1rt, Oct 21, 2009.

  1. v1rt New Member

    UPDATE: This post is incorrect. Please use my third post as my starting post. I can't delete this post, I'm not sure why.
    I would like to include this as a subquery so I can return few fields from another table
    Here is an example
    SELECT info1, info2, info3, info4 FROM table3 WHERE hhkey in (select hhkey from table1)
    SELECT field1, field2
    FROM table1 left join table2
    ON table1.hhkey=table2.hhkey
    I was thinking of writing it this way but it's not working
    SELECT field1,
    field2,
    SELECT info1, info2, info3, info4
    FROM table3 WHERE hhkey in (select hhkey from table1)
    FROM table1 left join table2
    ON table1.hhkey=table2.hhkey
    table1 has 5 million rows.
    table2 has 3+ million rows.
    table3 has 9 thousand rows.
  2. v1rt New Member

    I think I'm wrong with table 3. I just looked at the table and it has 1 to many relationship.

    hhkey info
    abc 11-22-33
    abc 22-22-33
    abc 12-31-88-99
    xyz 123-43-34663
    xyz 113-43-11150
    The hhkey field above is not distinct. However, this hhkey also exist in table1 but unique. What I want to happen is to retrieve all the info records say hhkey abc and return it as a single line.
    There is really one info field I need but I need all the rows for that hhkey.
  3. v1rt New Member

    Here is much more cleaner post. [:D] Sorry.
    create table custloans (lkey varchar(32) identity,
    loanno varchar(10)
    )
    create table citem(lkey varchar(32) identity,
    borrowfirst varchar(10),
    borrowerlast varchar(10)
    )
    create table multitaxid(lkey varchar(32),
    info varchar(20)
    )
    /*-------------- BEGIN QUERY -------------*/
    select ct.loanno,
    cm.borrowerfirst,
    cm.borrowerlast,
    ( select subquery from multitaxid so I can pull all the info field rows
    )
    FROM custloans ct
    LEFT JOIN citem cm
    ON ct.lkey=cm.lkey
    /*-------------- END QUERY -------------*/
    custloans table
    ----------------
    lkey loanno
    cx0001 00000000120
    cx0002 00000000121
    cx0003 00000000122
    cx0004 00000000123
    cx0005 00000000124
    cx0006 00000000125

    citem table
    --------------------
    lkey borrowerfirst borrowerlast
    cx0001 John Doe
    cx0002 Frank Wheeler
    cx0003 Susan Boyle
    cx0004 Ryan Anderson
    cx0005 Steve Sheppard
    cx0006 Tang Sy

    multitaxid table
    --------------------
    lkey info
    cx0001 11-11-11-11
    cx0001 22-22-22-22
    cx0001 33-33-33-33-66
    cx0001 54-2243-33442-11
    cx0002 32235-25325-2
    cx0002 3435-453-42-99
    The result I would like to achieve for cx0001
    ct.loanno | cm.borrowerfirst | cm.borrowerlast | then all the records from multitaxid table for that lkey
    00000000120 | John | Doe | 11-11-11-11 | 22-22-22-22 | 33-33-33-33-66 | 54-2243-33442-11

    Someone told me to use group concat but I'm not sure how to do it.
    Any help would be greatly appreciated. Thanks.
  4. Adriaan New Member

    OK, let's call the part between SELECT and FROM the 'column list'. Any expression on the column list can return only a single value for each row returned by the query.
    So if you put a subquery on the column list, it can only return a single value. (Also remember to add an alias for this value.)
    If you move the subquery to the FROM part of the table, with an alias, it becomes a 'derived table' that you can simply join. The criteria in the WHERE clause of the subform should be moved to the ON part of the JOIN statement.
    Everywhere in the query, you can simply call the derived table by its alias.
  5. v1rt New Member

    Got it. Let's do it this way. Let's forget custloans and citem table. Let's use multitaxid table only. How will I return all the info records for lkey cx0001 in a single row?
  6. Adriaan New Member

    Seeing that you're on SQL 7 or 2000, check if you can let the client application do the cross-tab.
  7. v1rt New Member

    I'm actually exporting it to a text file needed by the other department. Here is a different data. I found out that they only need 3 tax id and I can forget about the rest. Let's use the data below. How can I just pull top 3 for each lkey?
    ---------------
    DECLARE @mytable TABLE (
    [t_id] [int] IDENTITY(1,1) NOT NULL,
    lkey VARCHAR( 20 ),
    status VARCHAR( 20 ),
    someval varchar(10)
    );

    insert into @mytable (lkey, status,taxid)
    (
    select 'wx001','','aa-aa-aa' UNION ALL
    select 'wx001','','bb-bb-bb' UNION ALL
    select 'wx001','','cc-cc-cc' UNION ALL
    select 'wx001','','dd-dd-dd' UNION ALL
    select 'wx001','','ee-ee-ee' UNION ALL
    select 'wx001','','ff-ff-ff' UNION ALL
    select 'wx002','','11-11-11' UNION ALL
    select 'wx002','','22-22-22' UNION ALL
    select 'wx002','','33-33-33' UNION ALL
    select 'wx002','','44-44-44' UNION ALL
    select 'wx002','','55-55-55' UNION ALL
    select 'wx002','','66-66-66' UNION ALL
    select 'wx002','','77-77-77' UNION ALL
    select 'wx002','','88-88-88' UNION ALL
    select 'wx002','','99-99-99'
    );

    The result of select * from @mytable is

    t_id lkey taxid
    1 wx001 aa-aa-aa
    2 wx001 bb-bb-bb
    3 wx001 cc-cc-cc
    4 wx001 dd-dd-dd
    5 wx001 ee-ee-ee
    6 wx001 ff-ff-ff
    7 wx002 11-11-11
    8 wx002 22-22-22
    9 wx002 33-33-33
    10 wx002 44-44-44
    11 wx002 55-55-55
    12 wx002 66-66-66
    13 wx002 77-77-77
    14 wx002 88-88-88
    15 wx002 99-99-99

    The output below would be for me
    1 wx001 aa-aa-aa
    2 wx001 bb-bb-bb
    3 wx001 cc-cc-cc
    7 wx002 11-11-11
    8 wx002 22-22-22
    9 wx002 33-33-33
    What would be the select statement for this?
    Thanks.


  8. v1rt New Member

    Disregard folks, I was able to write a solution. Glad that data isn't that huge. It worked like a charm. :)
    Here is what I did:

    WHILE @@FETCH_STATUS = 0
    BEGIN
    if @prevlkey <> @lkey
    BEGIN
    set @count = 1
    END
    if @count = 1
    BEGIN
    insert into #temp_multitaxid (lkey, taxid1) values (@lkey, @taxid)
    set @prevlkey = @lkey
    END
    if @count = 2
    BEGIN
    if @prevlkey = @lkey
    BEGIN
    update #temp_multitaxid set lkey = @lkey, taxid2 = @taxid
    where lkey = @lkey
    set @prevlkey = @lkey
    END
    END
    if @count = 3
    BEGIN
    if @prevlkey = @lkey
    BEGIN
    update #temp_multitaxid set lkey = @lkey, taxid3 = @taxid
    where lkey = @lkey
    set @prevlkey = @lkey
    END
    END
    if @count = 4
    BEGIN
    if @prevlkey = @lkey
    BEGIN
    update #temp_multitaxid set lkey = @lkey, taxid4 = @taxid
    where lkey = @lkey
    set @prevlkey = @lkey
    END
    END
    if @count = 5
    BEGIN
    if @prevlkey = @lkey
    BEGIN
    update #temp_multitaxid set lkey = @lkey, taxid5 = @taxid
    where lkey = @lkey
    set @prevlkey = @lkey
    END
    END
    set @count = @count + 1
    FETCH NEXT FROM multi_taxid_cursor
    INTO @lkey, @taxid
    END
  9. v1rt New Member

    oh, this was from my old code.
    I don't have any "set lkey = @lkey" when count = 2, 3, 4 and 5.
    That's the only difference. :)

Share This Page