June 10, 2013 at 1:24 pm
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.
June 10, 2013 at 1:28 pm
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.
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
June 10, 2013 at 1:30 pm
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/
June 10, 2013 at 1:57 pm
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.
June 10, 2013 at 2:19 pm
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/
June 10, 2013 at 2:30 pm
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.
June 10, 2013 at 2:37 pm
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/
June 10, 2013 at 3:09 pm
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.
June 10, 2013 at 3:18 pm
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/
June 11, 2013 at 3:14 am
oops, Sean has already provided you a answer :-):-P
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 11, 2013 at 5:57 pm
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply