November 2, 2007 at 9:43 am
Hi
I have two tables consisting of columns one with a primary key, and another table consisting of columns with 2 primary keys, of which one of them is a foreign key ot the first table.
What am trying to achieve is to return all records in the 1st table that appear more than twice in the second table.
I have tried using JOINS but no success, can any one help?
November 2, 2007 at 10:22 am
This help?
create table MyParent
( myid int identity(1,1)
, myval char(1)
)
go
create table MyChild
( mychildid int identity(1,1)
, myid int
, mynewval char(1)
)
go
insert myparent select 'A'
insert myparent select 'B'
insert myparent select 'C'
insert myChild select 1, 'I'
insert myChild select 2, 'J'
insert myChild select 2, 'K'
insert myChild select 2, 'M'
insert myChild select 3, 'L'
insert myChild select 3, 'N'
go
select * from MyParent
select * from MyChild
select a.myid, count(b.myid)
from MyParent a
inner join MyChild b
on a.myid = b.myid
group by a.myid
having count(b.myid) > 2
go
drop table MyParent
drop table MyChild
November 2, 2007 at 3:47 pm
I could understand the concept of the join statement, but still couldnt get the results i wanted.
to make it easier these are the scripts am using, and what i want to achieve is to create a script that will show the details of books that have at least 2 orders.
create table books
(bk_no varchar(4)not null,title varchar(40), price money)
insert into books
(bk_no,title,price)
values ('B1','Oracle for Beginners','35.00')
insert into books
(bk_no,title,price)
values ('B2','Learn designer 2000 in 21 days','34.50')
insert into books
(bk_no,title,price)
values('B3','The Good DBA','28.20')
insert into books
(bk_no,title,price)
values('B4','The solution to Y2K problem','32.50')
insert into books
(bk_no,title,price)
values('B5','Practical Gardening','35.00')
insert into books
(bk_no,title,price)
values('B6','The Web and You','50.00')
insert into books
(bk_no,title,price)
values('B7','How to solve problems','27.70')
create table ordered_items
(ord_no varchar(3)not null,bk_no varchar(4)not null,qty_ordered int
primary key(ord_no,bk_no))
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O3','B6','10')
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O3','B2','25')
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O4','B6','60')
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O4','B1','10')
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O5','B3','70')
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O5','B4','10')
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O5','B6','20')
insert into ordered_items
(ord_no,bk_no,qty_ordered)
values ('O6','B7','15')
November 2, 2007 at 8:14 pm
This looks like homework... I'd recommend that you post what you've tried so we can help you learn...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2007 at 8:27 pm
Yeah, home work in the sense that am practicing on my own - self study.
November 2, 2007 at 10:11 pm
So post what you've tried so we can coach you...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2007 at 1:03 am
SELECT books.*
FROM books
INNER JOIN ordered_items
ON books.bk_no = ordered_items.bk_no
GROUP BY books.bk_no
HAVING COUNT(ordered_items.bk_no)>2
ERROR MSG:Msg 8120, Level 16, State 1, Line 1
Column 'books.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
November 3, 2007 at 2:12 am
So you're trying to show all books that have been order at least twice? Change books.* in the first line to books.bk_no. That will give you a list of all the books you are interested in. To get the rest of the details, you will need to join back to the books table. Since you're using SQL Server 2005, a common table expression will probably be the neatest way of doing this. Have a go at that and post again if you're struggling.
John
November 3, 2007 at 3:04 pm
Thanks, used:
SELECT books.bk_no
FROM books
INNER JOIN ordered_items
ON books.bk_no = ordered_items.bk_no
GROUP BY books.bk_no
HAVING COUNT(ordered_items.bk_no)>2
Thanks once again now able to understand the theories of JOIN
November 4, 2007 at 9:13 am
having difficulty trying to retrieve the rest of the details, am using :
SELECT books.*
FROM books
INNER JOIN ordered_items
ON books.bk_no = ordered_items.bk_no
GROUP BY books.bk_no
HAVING COUNT(ordered_items.bk_no)>2
and getting the error message:
Msg 8120, Level 16, State 1, Line 1
Column 'books.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Also when you say "To get the rest of the details, you will need to join back to the books table." what do you mean?
November 5, 2007 at 1:57 am
you cannot having columns other than one used in having clause...
never use select * from tablename when using having clause on one particular column...
November 5, 2007 at 3:31 am
SELECTB.*, O.Orders
FROMBooks B
INNER JOIN
(
SELECTbk_no, COUNT( * ) AS Orders
FROMordered_items
GROUP BY bk_no
) O ON B.bk_no = O.bk_no and O.orders > 2
--Ramesh
November 5, 2007 at 3:39 am
Hello Ramesh
That was what i was trying to do, for quite some time, can you kindly explain to me how you created that query, as I can see that you have a sub-query within a join statement?
I need to understand how you derived your query
November 5, 2007 at 4:49 am
to get the details you want about the books in your original query one option is to use the MAX(field) function. This option is less efficient, but will achieve the same result as creating a table expression
to use max just mimic the following syntax.
select books.bk_id, MAX(books.title) AS BookTitle ......
this should get you want you want, but again it isn't the best way to do it depending on the number of fields you are looking to add as detail.
remember whenever you are doing a GROUP BY function, every field in your select statement must be in the group by statement or within some type of aggregate function like SUM/AVG/MAX/MIN etc.
November 5, 2007 at 5:19 am
may be u should try this....
drop table #MyParent
drop table #MyChild
create table #MyParent
( myid int identity(1,1)
, myval char(1)
)
go
create table #MyChild
( mychildid int identity(1,1)
, myid int
, mynewval char(1)
)
go
insert #MyParent select 'A'
insert #MyParent select 'B'
insert #MyParent select 'C'
insert #MyChild select 1, 'I'
insert #MyChild select 2, 'J'
insert #MyChild select 2, 'K'
insert #MyChild select 2, 'M'
insert #MyChild select 3, 'L'
insert #MyChild select 3, 'N'
select * from #MyParent
select * from #MyChild
selectP.myid, count( C.mychildid )
from#MyParent P
inner join#MyChild C
OnP.myid = C.myid
group by P.myid
havingcount( C.mychildid ) > 2
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply