subquery returning more than one value

  • I have two tables, a and b. I want to select all from table a (except where proc_num is null) and one field from table b when it matches a row in table a.

    I keep getting the data in the table b field for all the rows in table a.

    select *.a, b.result from tablea as a left outer join tableb as b on a.id = b.parent_id

    The result I get are:

    proc_id dtime proc_num result

    70360 20130404 3706 positive

    63 20080313 0960 positive

    2956 20080313 null positive

    59913 20120327 3705 positive

    I want to get 3 rows for this. I don't want the row for 2956 to show, I only want the result to be positive for the row with proc_id of 63, the rest should be null.

    I probably need a subquery but I can't figure out how to make this work.

    Thanks very much for any ideas.

  • I may be braindead today but I only kind of follow the problem.

    You're getting the null join because you're using a left join.

    More than one value is usually a problem with correllated subqueries returning multiple rows, so I'm not even sure how your title applies here.

    If the big deal is making sure that things with nulls in the link column in table a is the issue, use a WHERE clause and strip out those items with NULLs in that field in table a, like so:

    WHERE a.proc_code IS NOT NULL

    Otherwise, can you setup a bit of sample schema/data like you'll find in the first link in my signature? It might help me make more sense of what you're trying to do.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hello Denise,

    I don't think this is quite difficult. Unfortunately it's more difficult to really understand your example.

    Please read the following link (I did too at the beginning 😉 ) and post your question and example data again, inlcuding the create-table-statements 🙂

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • OK, sorry about that, I'll try again.

    Tablea has

    proc_id dtime proc_num

    70360 20130404 3706

    63 20080313 0960

    2956 20080313 null

    59913 20120327 3705

    Tableb has

    id result

    2956 positive

    1234 negative

    I want all the rows from Tablea where proc_num is not null, and add the result field from Tableb when a.proc_id = b.id

    The results for the other rows that don't match any thing in Tableb should be null.

    I tried using a subquery but got error "subquery retuning more than one value" hence the title of my question.

    like this:

    select a.*, (select b.result from tablea as a left outer join tableb as b on a.proc_id = b.id) as result

    from tablea as a left outer join tableb as b on a.proc_id = b.id

    Hope this is a bit more clear and thanks again for any help.

  • Obviously you did not read the article about best practices. The idea here is provide ddl and data in a consumable format. That way we are not left guessing datatypes and having to put together all sorts of inserts and stuff to start working on your problem. I converted your post into an example of how this should be posted.

    create table #Tablea

    (

    proc_id int,

    dtime datetime,

    proc_num int

    )

    insert #Tablea

    select *

    from (values(70360, '20130404', 3706)

    ,(63, '20080313', 0960)

    ,(2956, '20080313', null)

    ,(59913, '20120327', 3705)) as x(p,d,pr)

    create table #Tableb

    (

    ID int,

    result varchar(10)

    )

    insert #Tableb

    select *

    from (values(2956, 'positive'), (1234, 'negative')) as x(i,r)

    select * from #Tablea a

    select * from #Tableb

    drop table #Tablea

    drop table #Tableb

    Now the question is, what do you expect as output based on the sample data provided?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The results I'm trying to get are:

    proc_id dtime proc_num result

    70360 20130404 3706 null

    63 20080313 0960 positive

    59913 20120327 3705 null

    And thanks for creating the sample tables for me. I'm just so caught up in my problem that my example seemed obvious to me!

    Thanks again for any ideas.

  • Denise McMillan (6/10/2013)


    The results I'm trying to get are:

    proc_id dtime proc_num result

    70360 20130404 3706 null

    63 20080313 0960 positive

    59913 20120327 3705 null

    And thanks for creating the sample tables for me. I'm just so caught up in my problem that my example seemed obvious to me!

    Thanks again for any ideas.

    From the sample data you posted that is not going to happen. You join the two tables on a.proc_id and b.ID. There are no values in b that have a corresponding value in a where prod_num is also not null. The only row in tablea that has a corresponding row in tableb is 2956 but the proc_num for that one is NULL which you don't to have returned.

    select *

    from #Tablea a

    left join #Tableb b on a.proc_id = b.ID

    where a.proc_num is not null

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • That's about all I have to work with. I didn't see how to do it but was hoping someone else could.

    Thanks though.

  • Denise McMillan (6/10/2013)


    That's about all I have to work with. I didn't see how to do it but was hoping someone else could.

    Thanks though.

    What is the logic here? Using the data and the join logic you posted the three rows returned are all the same in regards to tableb. There just isn't anything to indicate that 63 should be positive and the others null.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • oops, Sean has already provided you a answer :-):-P

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Denise McMillan (6/10/2013)


    The results I'm trying to get are:

    proc_id dtime proc_num result

    70360 20130404 3706 null

    63 20080313 0960 positive

    59913 20120327 3705 null

    And thanks for creating the sample tables for me. I'm just so caught up in my problem that my example seemed obvious to me!

    Thanks again for any ideas.

    It doesn't make any sense, but here is your result: 😀

    SELECT a.*, T.result

    FROM #Tablea a

    OUTER APPLY (SELECT TOP 1 result

    FROM #Tableb b

    inner join #Tablea a2 ON a2.proc_id = b.ID

    WHERE a2.dtime = a.dtime

    AND a2.proc_num IS NULL) T

    WHERE proc_num IS NOT NULL


    Alex Suprun

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply