September 29, 2011 at 1:25 pm
I am a new and am having some trouble with joining two tables.
table1: (topicID, questionID) where topicID is the primary key. For each topicID, there are many questionID's.
Now, I have a table2 with questioIDs and I want to get all those topicID's from table1 which have atleast one entry for each questionID in table2.
I would appreciate any help.
Thanks
September 29, 2011 at 1:48 pm
seftest_09 (9/29/2011)
I am a new and am having some trouble with joining two tables.table1: (topicID, questionID) where topicID is the primary key. For each topicID, there are many questionID's.
Now, I have a table2 with questioIDs and I want to get all those topicID's from table1 which have atleast one entry for each questionID in table2.
I would appreciate any help.
Thanks
select distinct table1.topicID
from table1
join table2 on table1.questionID = tables.questionID
_______________________________________________________________
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/
September 29, 2011 at 9:18 pm
The test sql_code below is you want?
create table table1
(
topicID int,
questionid int
)
create table table2
(
questionid int
)
insert into table1
select 1,1
union all
select 1,2
union all
select 1,2
union all
select 2,4
insert into table2
select 1
union all
select 2
union all
select 5
union all
select 4
select table1.topicID,MAX(table2.questionID) as questionID
from table1
join table2 on table1.questionID = table2.questionID
group by table1.topicID
/*
topicIDquestionID
1 2
2 4
*/
October 3, 2011 at 1:51 pm
Hello,
no, actually for the tables you made, since none of the topicID in table 1 have atleast one answer for each questionID in table 2, so the result set should be null.
But, if the table2 had only two rows, i.e.: 1 and 2, then topicID 1 in table1 had atleast one answer for all the rows in table2 so the result should be topicID : 1.
Thanks.
October 3, 2011 at 1:55 pm
How about if you post some ddl, sample data (insert statements) and desired output based on your sample data? The query for this is really pretty simple but your description of the problem seems to be a little different this time than it was last time. Or if you just want to use the ddl and sample data that yubo posted, what is the desired output?
_______________________________________________________________
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/
October 3, 2011 at 2:03 pm
Here it is:
create table table1
(
topicID int,
questionid int
)
create table table2
(
questionid int
)
insert into table1
select 1,1
union all
select 1,2
union all
select 1,2
union all
select 2,2
union all
select 2,3
union all
select 2,7
union all
select 2,9
insert into table2
select 2
union all
select 3
union all
select 7
/*Result should be
topicID
2
*/
As only the topicID: 2 in table1 had atleast one entry for all each and every questionID in table2.
October 3, 2011 at 2:14 pm
You have all kinds of bad data in here. You have records in table1 with questionIDs that dont exist. You even have two records in table1 that are identical. If your real data is this sloppy, you should consider some RI to help. Even with the sloppy data you can accomplish this fairly easily.
The following works on your sample data:
select topicID, COUNT(*)
from table1
join table2 on table1.questionid = table2.questionid
group by topicID
having count(*) = (select COUNT(*) from table2)
_______________________________________________________________
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/
October 3, 2011 at 2:29 pm
Your answer works great. Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply