Nested table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested table

Author

Topic

belkin_99
Starting Member

27 Posts

Posted - 2009-03-02 : 15:19:08

I have two tables Table1, and Table2
I would like to make Table1 as a header to Table2
(For each row of Table1 there are many rows from Table2)
Table1
Customer#, CustomerName, CustomerAddress

Table2
Customer#, Visit_Date, Visit_Time, Service_Type

The output should be in nested figure (header and details)
<header Cust1> Customer#, CustomerName, CustomerAddress
<details> Visit_Date, Visit_Time, Service_Type.
. . .
. . .
<header cust2> -----etc

I know it is simple to
select t1.Customer#, t1.customerName,t1.customeraddress from table1 t1, table2 t2
where
t1.customer#=t2.customer#

Any help will be appreciated

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-02 : 17:04:06

Are you looking for your query to return XML output? If you are what version of SQL are you using?

belkin_99
Starting Member

27 Posts

Posted - 2009-03-02 : 17:08:46

No, but I would like to write them on file.

belkin_99
Starting Member

27 Posts

Posted - 2009-03-02 : 20:32:45

I WAS THINKING WITH THE BELOW

SELECT T1.CUSTOMER#, T1.CUSTOMER_NAME,.T1.CUSTOMER_ADDRESS FROM TABLE T1, TABLE 2
WHERE
T1.CUSTOMER#=T2.CUSTOMER#
AND
(IF COUNT (T1.CUSTOMER#)>1
THEN
SELECT * FROM TABLE1 T1,TABLE2 T2
WHERE T1.CUSTOMER#=T2.CUSTOMER#;

ANY HELP APPRECIATED

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-02 : 22:52:28

try this once,

SELECT count(T1.CUSTOMER#),T1.CUSTOMER#, T1.CUSTOMER_NAME,T1.CUSTOMER_ADDRESS FROM TABLE T1, TABLE 2
WHERE T1.CUSTOMER#=T2.CUSTOMER#
group by T1.CUSTOMER#,T1.CUSTOMER_NAME,.T1.CUSTOMER_ADDRESS
having(count(T1.CUSTOMER#)) > 1

belkin_99
Starting Member

27 Posts

Posted - 2009-03-02 : 23:31:27

Thanks but,
that's okay if we looking to get T1, without repeating but the question is a bout two tables, you did not use T2.

T2 should be run every time getting a row from T1

T1
T2
T2
T1
T2
T1
T1
.
.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:05:42

quote:
Originally posted by belkin_99

I have two tables Table1, and Table2
I would like to make Table1 as a header to Table2
(For each row of Table1 there are many rows from Table2)
Table1
Customer#, CustomerName, CustomerAddress

Table2
Customer#, Visit_Date, Visit_Time, Service_Type

The output should be in nested figure (header and details)
<header Cust1> Customer#, CustomerName, CustomerAddress
<details> Visit_Date, Visit_Time, Service_Type.
. . .
. . .
<header cust2> -----etc

I know it is simple to
select t1.Customer#, t1.customerName,t1.customeraddress from table1 t1, table2 t2
where
t1.customer#=t2.customer#

Any help will be appreciated

is this for a reporting requirement? if yes, the reqd output format can be easily obtained using reporting tools like sql report. you can bring data in normal format as in table and do formatting in report tool

belkin_99
Starting Member

27 Posts

Posted - 2009-03-03 : 09:22:33

Thank You visash16 for your reply.

+ It is not for a report, I build program, and I would like to arrange the out put in the explained way.
Is this mean, it is not possible to do it with SQL language??!

+ Please, Can you provide me more details about reqd output format, I am interested to learn about it?
Is it free?

Thank you so much for your reply...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:26:36

Formatting is not purpose of sql language. it needs to done at your presentation layer. which front end are you using?

belkin_99
Starting Member

27 Posts

Posted - 2009-03-03 : 10:36:31

+ I think, It is not a formatting purpose only here.
I need to intersect the two tables. For each customer, there are many visiting information (1-m).
a third temp table might be solve this.
I don't know any suggestion will be great.

+ Please, what is your recommendation for SQL formatting?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 10:57:32

the intersection part can be doneby sql query. however, the reqd format needs to be build from front end.

belkin_99
Starting Member

27 Posts

Posted - 2009-03-03 : 12:08:21

So, We can not do the formatting by the SQL language ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:27:33

we can.but the ways are really ugly