Database

28-May-07 19:43

I am using two different tables products and product details . Product ID is the primary key of the products table and is the foreign key reference to the Product details table. I want to fetch only five 5 records from the products table and for each productID I need two records from the product details table. It will be like

M1 C11
M1 C12
M2 C21
M2 C22

I am using oracle 9i database. Please help

Harini N K28-May-07 20:05

Harini N K

28-May-07 20:05

Hi
I am not getting you .. explain clearly how you want output from the result

Harini

K.P.Kannan28-May-07 20:13

28-May-07 20:13

We have two tables

One containing the list of products p1,p2,p3,p4,p5
other any contents associated with the products
p1,c1
p1,c2
p1,c3
p2,c4
p2,c5
p2,c6
p3,c7
p4,c8
p4,c9
p4,c10

I would like to get the below resultset

p1,c1
p1,c2
p2,c4
p2,c5
p3,c7
p4,c8
p4,c9

Basically master detail for data binding. My situation is that I need to some x records, let's say 5 products and on pagination next 5 products as a master and 2 contents(at the maximum) within each products, (if one content show only one) and view all button within that(.NET) to show the remaining contents for that product. It's a master detail grid. I want to retrive that kind of resultset from the query

Thanks,
K.P.Kannan

K.P.Kannan29-May-07 0:54

29-May-07 0:54

CREATE TABLE t1(col1 NUMBER)
CREATE TABLE t2(col1 NUMBER,col2 NUMBER)
INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2);INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);INSERT INTO t1 VALUES(5);INSERT INTO t1 VALUES(6);
INSERT INTO t2 VALUES(1,1);INSERT INTO t2 VALUES(2,1);INSERT INTO t2 VALUES(3,1);INSERT INTO t2 VALUES(4,1);INSERT INTO t2 VALUES(5,1);INSERT INTO t2 VALUES(6,1);INSERT INTO t2 VALUES(1,2);INSERT INTO t2 VALUES(3,2);INSERT INTO t2 VALUES(4,2);INSERT INTO t2 VALUES(6,2);INSERT INTO t2 VALUES(1,3);
INSERT INTO t2 VALUES(3,3);INSERT INTO t2 VALUES(6,3);

SELECT col1, col2 FROM t2 d WHERE col1 IN
(SELECT col1 FROM
(SELECT ROWNUM AS rownumber1,col1,col2 FROM t2 WHERE col1 IN
(SELECT col1 FROM (SELECT ROWNUM AS rownumber,col1 FROM t1) WHERE rownumber<=3)--WHERE c.col1=col1
)WHERE rownumber1<=2 AND d.col1=col1)

I want a query which will produce the following result.I just tried with the above one which is failing(no result or not compiling).

1 1
1 2
2 1
3 1
3 2

Harini N K29-May-07 1:08

Harini N K

29-May-07 1:08

Hi
I will try out your in SQL Server 2000 and let you know as Oracle is not installed on my machine.

Harini

Harini N K30-May-07 19:35

Harini N K

30-May-07 19:35

Hi
I tried out this query in SQL Server 2000. It is working as expected and I am not sure whether this will work in Oracle

select t2.col1, t2.col2 from t2
inner join t1 on t1.col1 = t2.col2
and t2.col1 in (select top 2 a.col1 from t2 a where a.col2 = t1.col1 order by a.col1)

Smile | :)

Harini

K.P.Kannan30-May-07 20:12

Insert Multiple Rows on Access

30-May-07 20:12

Thank You Harini. I am trying to convert this into Oracle. There are some problems. I will let you know once done

AnhTin28-May-07 18:27

AnhTin

28-May-07 18:27

hi all
How can i do insert multiple rows on Accsess
INSERT INTO demo(id, name, role) VALUES ('demo', 'demo1', 'demo2');
INSERT INTO demo(id, name, role) VALUES ('demo3', 'demo4', 'demo5');
INSERT INTO demo(id, name, role) VALUES ('demo6', 'demo6', 'demo7');

When i insert by SQL have Exception:
Characters found after and of SQL statement
Confused | :confused:

Re: Insert Multiple Rows on Access

Navneet Hegde29-May-07 1:51

Navneet Hegde

29-May-07 1:51

Can you bit elaborate your problem. Blush | :O

Thanks and Wishes
Navneet Hegde
Nashik

Develop2Program & Program2Develop

Re: Insert Multiple Rows on Access

Mike Dimmick29-May-07 11:33

Mike Dimmick

29-May-07 11:33

Access/Jet does not support multi-statement batches. You must send a single statement at a time.

Stability. What an interesting concept. -- Chris Maunder

MySQL basics...

Tzumer Edo28-May-07 4:08

Tzumer Edo

28-May-07 4:08

Houdy all,

I have to take over am application that works with MySQL.
I'm new to MySQL, but have worked quite a lot with SQL Server 2k, and 2005

Now, what i got is a bunch of .frm files and one .opt file,
I have down loaded MySQL 5 and installed it on my console.

What do i do next ?
How to create and import data to the MySQL DB?
Or maybe you guys think its best to just migrate to SQL Server...
is there a helpful free tool to use as a database admin interface ?

Regards,
Tzumer Edo.

Re: MySQL basics...

Giorgi Dalakishvili28-May-07 6:49

Giorgi Dalakishvili

28-May-07 6:49

Tzumer Edo wrote:

is there a helpful free tool to use as a database admin interface ?

here you go: http://dev.mysql.com/downloads/gui-tools/5.0.html

my articles

executing procedure via ODBC Connection

apoorva_raje28-May-07 1:28

apoorva_raje

28-May-07 1:28

trying to execute this stored procedure using a prg that connects to the server using an ODBC DSN. it doesnot retrieve the result set.

CREATE PROCEDURE PROC_1
AS
CREATE TABLE #abc (
ssn char(30) )

insert into #abc(ssn) select phone from tbl_users

select * from #abc
GO

when executed directly, ie. say, Query Analyser or Excel, there r no probs encountered. also, when i remove the create n insert stmts, n change the #abc to a table abc, am getting the results.

Thanks..

Colin Angus Mackay28-May-07 1:53

Colin Angus Mackay

28-May-07 1:53

A slightly off topic question - but why are you creating a temprorary table, populating it with a fairly simple query then retrieving the result set. Would it not be quicker just to retrieve the result set from the SELECT used on the INSERT? The temp table will just disappear at the end of the stored procedure call, so what use is it? Or is there more code you are not telling us about which may help us answer your query.

In other words why are you not just doing:

SELECT phone from tbl_users

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

apoorva_raje28-May-07 2:06

apoorva_raje

28-May-07 2:06

yeah.. there r more operations that r to be performed on this temp table.. modifications, etc.. but i got to get this running b4 i put in the other stmts... SOS!!

ash0428-May-07 19:52

ash04

28-May-07 19:52

use this

insert into #abc(ssn) select isnull(phone,'') from tbl_users

DAsh04

andyharman28-May-07 23:59

andyharman

28-May-07 23:59

Try adding

set nocount on

as the first line of your procedure. This stops SQL-Server from sending-back a running commentary of what its up to (which is probably being counted as the resultset for you current procedure).