Complicated problem to solve in sql, please help!

please help me solve this problem i have with my database!
i have 2 tables :
table A
--
id
name
table B
--
id
tablea_id
title
now, what i need is this:
return ALL joined rows from table B and A but it should only return maximum
2 rows with the same tablea_id.
looks easy, but NOT :)
thanks.yaniv danan wrote:
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return maximu
m
> 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
Please post proper specs: DDL, sample data and show your required
result. Without that information the answers you get may be mostly
untested guesswork. "Maximum 2 rows" for example begs the obvious
question, which 2 rows do you want?
Here are my assumptions about your table structure:
CREATE TABLE A (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(10) NOT
NULL);
CREATE TABLE B (id INTEGER NOT NULL PRIMARY KEY, tablea_id INTEGER
REFERENCES A (id), title VARCHAR(10) NOT NULL);
Here's one possible answer:
SELECT A.id, A.name, B.id, B.title
FROM A,B
WHERE A.id = B.tablea_id
AND B.id IN
(SELECT TOP 2 id
FROM B AS C
WHERE C.tablea_id = A.id
ORDER BY id) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yaniv
create table #a
(
col1 int not null primary key,
col2 char(1)
)
insert into #a values (1,'a')
insert into #a values (2,'b')
insert into #a values (3,'c')
create table #b
(
col1 int not null ,
col2 int,
col3 char(1)
)
insert into #b values (1,100,'a')
insert into #b values (1,100,'b')
insert into #b values (1,100,'b')
insert into #b values (2,500,'a')
insert into #b values (2,22,'b')
insert into #b values (3,5,'a')
insert into #b values (3,66,'b')
insert into #b values (3,66,'b')
select * from #b
where ( select count(*) from #b t
where t.col1=#b.col1 and t.col2=#b.col2
) <=2
UNION ALL
select TOP 2* from #b E
where(
select count(*) from #b
wherecol2 = E.col2
and col2 = E.col2
) >= 3
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
> maximum 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||Which two out of several rows? Using TOP without an ORDER BY may yield
unexpected results.
It would help if you explain the reasons for this a bit more.
ML
http://milambda.blogspot.com/|||TRY THIS ........
CREATE TABLE TABLE_A (id int, name varchar(255))
CREATE TABLE TABLE_B (id int, tablea_id int, title varchar(255))
TABLE_A
id name
1 A
2 B
3 C
4 D
id tablea_id title
1 1 ABC
2 1 DEF
3 1 PQR
4 2 XYZ
SELECT B.* FROM TABLE_B B
WHERE B.tablea_id IN
(SELECT TOP 2 A.ID
FROM TABLE_B B2
INNER JOIN TABLE_A A
ON A.ID = B2.tablea_id
ORDER BY A.ID )
ORDER BY B.ID
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
> maximum 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||SELECT * FROM tableA as a INNER JOIN tableB as b ON a.id = b.tablea_id
WHERE b.id IN (SELECT TOP 2 id FROM tableB as b2 WHERE b2.tableA_id = a.id)
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
maximum
> 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||thanks for the help, but i think i need to make some things clear (sorry):
table B also have a "AddedDate" column
and the query should return only the TOP 2 newest rows from table B (using
the value in "addeddate").
ofcourse the query most be efficient enough by being very quick on getting
the results.
p.s.
When saying returning rows, i mean returning all the rows from the join
between the two tables :)
thanks, yaniv.
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
> maximum 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||yaniv danan wrote:
> thanks for the help, but i think i need to make some things clear (sorry):
> table B also have a "AddedDate" column
> and the query should return only the TOP 2 newest rows from table B (using
> the value in "addeddate").
> ofcourse the query most be efficient enough by being very quick on getting
> the results.
> p.s.
> When saying returning rows, i mean returning all the rows from the join
> between the two tables :)
> thanks, yaniv.
>

>From my previous post:
Please post proper specs: DDL, sample data and show your required
result. Without that information the answers you get may be mostly
untested guesswork.
See: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--