SQL question

I have a set of tables, a master table and two detail tables. The detail
tables have at most 5 records for each master record. I need to move these
records to a flat file that has one record each set of master detail
records. How do I go about doing this?

Example:
Master Table
===========
Id
Name
City

Detail Table 1
===========
Id
Code

Detail Table 2
===========
Id
Date

All tables are related by the 'Id' field. These records must go into the
following table:

Output
===========
Id
Name
City
Code_1
Code_2
Code_3
Code_4
Code_5
Date_1
Date_2
Date_3
Date_4
Date_5


Thanks for any help,

Jim

0
Jim
6/24/1997 1:52:24 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

4 Replies
546 Views

Similar Articles

[PageSpeed] 30

Select all the appropriate data, join the tables on ID.At the end of the
select put a semi-colon, output to c:\filename format text.

But I don't know how to get a vertical listing.

Jim Kist wrote:
> 
> I have a set of tables, a master table and two detail tables. The detail
> tables have at most 5 records for each master record. I need to move these
> records to a flat file that has one record each set of master detail
> records. How do I go about doing this?
> 
> Example:
> Master Table
> ===========
> Id
> Name
> City
> 
> Detail Table 1
> ===========
> Id
> Code
> 
> Detail Table 2
> ===========
> Id
> Date
> 
> All tables are related by the 'Id' field. These records must go into the
> following table:
> 
> Output
> ===========
> Id
> Name
> City
> Code_1
> Code_2
> Code_3
> Code_4
> Code_5
> Date_1
> Date_2
> Date_3
> Date_4
> Date_5
> 
> Thanks for any help,
> 
> Jim

0
barbra
6/24/1997 4:00:15 PM
select * from MyTable ;
output to data.txt
format ascii
delimited by '\n\n'

 The two characters \n represent a newline character

Alex Malyuk
Russia
AMalyuk@polikom.ru
 barbra hale wrote in article <33AFEF0F.7755@carecentric.com>...
>Select all the appropriate data, join the tables on ID.At the end of the
>select put a semi-colon, output to c:\filename format text.
>
>But I don't know how to get a vertical listing.




0
Alex
6/24/1997 5:53:10 PM
Because I have too much free time, I came up with a paradigm that would
parse all your code and date fields to produce the output you mentioned
from a single select statement.

FWIW, here's my entire SQL example (it's at least an interesting
diversion)

***********************************

CREATE TABLE Master( Id CHAR(1), Name CHAR(10), City CHAR(10) );
CREATE TABLE Detail_1( Id CHAR(1), Code CHAR(10) ) ;
CREATE TABLE Detail_2( Id CHAR(1), DDate date ) ;

INSERT INTO master VALUES( '1', 'Charlie', 'Paris' );
INSERT INTO master VALUES( '2', 'Bill', 'Nashville' );
INSERT INTO master VALUES( '3', 'Stan', 'Warsaw' );

INSERT INTO detail_1 VALUES( '1', 'hash' );
INSERT INTO detail_1 VALUES( '1', 'beans' );
INSERT INTO detail_1 VALUES( '1', 'fruit' );
INSERT INTO detail_1 VALUES( '1', 'bread' );
INSERT INTO detail_1 VALUES( '1', 'cookies' );

INSERT INTO detail_1 VALUES( '2', 'pants' );
INSERT INTO detail_1 VALUES( '2', 'shoes' );
INSERT INTO detail_1 VALUES( '2', 'socks' );
INSERT INTO detail_1 VALUES( '2', 'shirts' );
INSERT INTO detail_1 VALUES( '2', 'ascots' );

INSERT INTO detail_1 VALUES( '3', 'elves' );
INSERT INTO detail_1 VALUES( '3', 'faeries' );
INSERT INTO detail_1 VALUES( '3', 'dryads' );
INSERT INTO detail_1 VALUES( '3', 'goblins' );
INSERT INTO detail_1 VALUES( '3', 'sprites' );

INSERT INTO detail_2 VALUES( '1', '1962-1-17' );
INSERT INTO detail_2 VALUES( '1', '1962-1-18' );
INSERT INTO detail_2 VALUES( '1', '1962-1-19' );
INSERT INTO detail_2 VALUES( '1', '1962-1-20' );
INSERT INTO detail_2 VALUES( '1', '1962-1-21' );

INSERT INTO detail_2 VALUES( '2', '1962-2-17' );
INSERT INTO detail_2 VALUES( '2', '1962-2-18' );
INSERT INTO detail_2 VALUES( '2', '1962-2-19' );
INSERT INTO detail_2 VALUES( '2', '1962-2-20' );
INSERT INTO detail_2 VALUES( '2', '1962-2-21' );

INSERT INTO detail_2 VALUES( '3', '1973-3-12' );
INSERT INTO detail_2 VALUES( '3', '1973-3-13' );
INSERT INTO detail_2 VALUES( '3', '1973-3-14' );
INSERT INTO detail_2 VALUES( '3', '1973-3-15' );
INSERT INTO detail_2 VALUES( '3', '1973-3-16' );


SELECT master.Id AS MASTERID,
       master.Name,
       master.City,
       ( SELECT SUBSTR( LIST( detail_1.Code ), 0, LOCATE( LIST(
detail_1.Code ), ',', 0 ) -1 ) FROM  detail_1 WHERE detail_1.Id =
MASTERID ) AS Code_1,
       ( SELECT SUBSTR( LIST( detail_1.Code ), LENGTH( Code_1 ) +2 ,
LOCATE( LIST( detail_1.Code ), ',', LENGTH( Code_1 ) +2 ) -LOCATE( LIST(
detail_1.Code ), ',', 0 ) -1 ) FROM  detail_1 WHERE detail_1.Id =
MASTERID ) AS Code_2,
       ( SELECT SUBSTR( LIST( detail_1.Code ), LENGTH( Code_1 ) +LENGTH(
Code_2 ) +3 , LOCATE( LIST( detail_1.Code ), ',',  LENGTH( Code_1 )
+LENGTH( Code_2 ) +3 ) - LOCATE( LIST( detail_1.Code ), ',', LENGTH(
Code_1 ) +2 )  -1 ) FROM  detail_1 WHERE detail_1.Id = MASTERID ) AS
Code_3,
       ( SELECT SUBSTR( LIST( detail_1.Code ), LENGTH( Code_1 ) +LENGTH(
Code_2 ) +LENGTH( Code_3 ) +4 , LOCATE( LIST( detail_1.Code ), ',', 
LENGTH( Code_1 ) +LENGTH( Code_2 ) +LENGTH( Code_3 ) +4 ) - LOCATE(
LIST( detail_1.Code ), ',', LENGTH( Code_1 ) +LENGTH( Code_2 ) +3 )  -1
) FROM  detail_1 WHERE detail_1.Id = MASTERID ) AS Code_4,
       ( SELECT SUBSTR( LIST( detail_1.Code ), LENGTH( Code_1 ) +LENGTH(
Code_2 ) +LENGTH( Code_3 ) +LENGTH( Code_4 ) +5  ) FROM  detail_1 WHERE
detail_1.Id = MASTERID ) AS Code_5,
       ( SELECT SUBSTR( LIST( detail_2.DDate ), 0, LOCATE( LIST(
detail_2.DDate ), ',', 0 ) -1 ) FROM  detail_2 WHERE detail_2.Id =
MASTERID ) AS DDate_1,
       ( SELECT SUBSTR( LIST( detail_2.DDate ), LENGTH( DDate_1 ) +2,
LOCATE( LIST( detail_2.DDate ), ',', 0 ) -1 ) FROM  detail_2 WHERE
detail_2.Id = MASTERID ) AS DDate_2,
       ( SELECT SUBSTR( LIST( detail_2.DDate ), ( LENGTH( DDate_1 ) * 2
) +3, LOCATE( LIST( detail_2.DDate ), ',', ( LENGTH( DDate_1 ) * 2 ) +3
) -1 ) FROM  detail_2 WHERE detail_2.Id = MASTERID ) AS DDate_3,
       ( SELECT SUBSTR( LIST( detail_2.DDate ), ( LENGTH( DDate_1 ) * 3
) +4, LOCATE( LIST( detail_2.DDate ), ',', ( LENGTH( DDate_1 ) * 3 ) +4
) -1 ) FROM  detail_2 WHERE detail_2.Id = MASTERID ) AS DDate_4,
       ( SELECT SUBSTR( LIST( detail_2.DDate ), ( LENGTH( DDate_1 ) * 4
) +5 )  FROM  detail_2 WHERE detail_2.Id = MASTERID ) AS DDate_5
FROM   master

*****************************************************

Jim Kist wrote:
> 
> I have a set of tables, a master table and two detail tables. The detail
> tables have at most 5 records for each master record. I need to move these
> records to a flat file that has one record each set of master detail
> records. How do I go about doing this?
> 
> Example:
> Master Table
> ===========
> Id
> Name
> City
> 
> Detail Table 1
> ===========
> Id
> Code
> 
> Detail Table 2
> ===========
> Id
> Date
> 
> All tables are related by the 'Id' field. These records must go into the
> following table:
> 
> Output
> ===========
> Id
> Name
> City
> Code_1
> Code_2
> Code_3
> Code_4
> Code_5
> Date_1
> Date_2
> Date_3
> Date_4
> Date_5
> 
> Thanks for any help,
> 
> Jim
0
Tim
6/25/1997 4:36:47 PM
if you have PB, you can use crosstab report - it rotates your data
horizontally

HTH, Igor

Jim Kist wrote:

> I have a set of tables, a master table and two detail tables. The
> detail
> tables have at most 5 records for each master record. I need to move
> these
> records to a flat file that has one record each set of master detail
> records. How do I go about doing this?



0
Igor
6/27/1997 8:30:15 PM
Reply:

Similar Artilces:

GENERAL SQL SERVER: Question about the Script that setups the SQL Server Database.
This is the first time I have created a script for Sql Server 2000, and I have run into a few syntax ereas that I am not familar with. Bellow is a section of the script from the Portal server packages. At the end of the sample, is "ON [PRIMARY], Can anyone explain to me what this does and why the primary key isnt setup inside the code itself? I am basically trying to take parts of the script and incorrporate it into my own script and hate added syntax that I dont understand, makes debugging a real pain in the future. CREATE TABLE [dbo].[Portal_Announcements] ( [ItemID] [in...

general Sql question
Given the following tables Table1 --------- labnumber name doctor Table 2 --------- labnumber ailmentcode (hold 0 to many ailments for records in table 2) How can I get all the records in table 1 that have ailments (related in table 2 by labnumber) 1, 2, 3, 4, 5, 6, 7, 8, and 9. Thanks in advance Blake select table1.* from table1, table2 where table1.labnumber = table2.labnumber and table2.ailment in (1,2,3,4,5,6,7,8,9) Instead of the IN clause you could code and table2.ailment between 1 and 9 or even and table2.ailment >= 1 and table2.ailme...

General SQL Question
 I am trying to create a query that will return the matching image name from one table or displays a null value for that column if there is no matching row. So for example I want to go through all the event table rows and match up the row information with image_name from Image Gallery table. If nothing matches up from that row I want a null value displayed. I been working on this for a while and all I manage to come up with is only returning rows that have matching IDs from Image Gallery and Event.  --------Image Gallery----------Image Name |  EventIDBaseball  &...

general SQL question
--------------7EC95B4FEE051BD577C3D038 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a general SQL question. If there is a two-column table, e.g., id keyword -- ------ 1 k1 2 k2 1 k2 2 k3 (1) Is it possible to retrieve the values for id 1 by saying "select id from foo where keyword=k1 and keyword=k2" The above returns nothing. Since we are comparing against a single column (keyword), this doesn't seem possible to do the "and". (2) If...

General question about inline sql
I'm executing inline sql (PB 10.1) against a MS SQL server database as per the documentation and i'm getting null in the 'into' value. The select will work in the DB painter. Have there been changes in the last few versions of PB regarding this. I've never had this problem before. Mark wrote: > I'm executing inline sql (PB 10.1) against a MS SQL server > database as per the documentation and i'm getting null in > the 'into' value. The select will work in the DB painter. > Have there been changes in the last few versions of PB > r...

General Question about SQL efficiency
I have a website that is probably going to hold a sizable amount of data.  The data will be specific to groups of users based on login credentials.  Would it be more efficient to create a whole new database for each group of users, or create new tables for the groups in the existing database?  Any thoughts on the topic would be appreciated. ThanksJayMan I'm not quite sure what you consider a sizable amount of data, but one database should be fine.  If you feel that the database is beginning to act slow you can always partition tables, re-evaluate indexes set up o...

General SQLA/SQL question
I am curious if there is a way to create a temp table with the exact same structure as an existing table in one step. In other words, I don't want to either create the table by selecting the type and name of column from the systables or to explicitly create the table ahead of time and then populate it by selecting from the existing table. I am looking for something that would be similar to CREATE TEMPORARY TABLE X_TEMP AS SELECT * FROM X. I know that is not the correct syntax, but you get the idea. TIA, JM. Now this would be nice! Don't forget the 'on commit...

db-sql general question
I have some common questions reg sql and db operataions..i have googled on this and found the overwhelming information,i understand some and confused abt someother ..if someone can give me all the answers at one place briefly,i wud really appreciate that...i am usign SQL server 2005 and i have DAL(ADO.NET) BLL set up in my  C#  web application 1-whats the significance of 'RETURN' in SQL? i mean,what will happen if i don't have it .When does having RETURN  matter at the end of SQL? 2-i am using the OUTPUT parameter,to get the id of inserted row.However in case of...

Simple SQL Question
Looking for you Classicly Trained Database Artists to give me Your opinion on this scenario: - 10 Balances to track in a Master Row. - 3 different Transaction History tables, some columns of which can only be determined by client logic (another loooong argument we'll pass on), ie, requiring a parameterized insert by the client. - Each History table has Begin and End Balance columns for the 10 balances. BALANCE_1 needs to be incremented by TRANS_AMT 100, it is currently 500. SELECT BALANCE_1, which returns 500, issue the UPDATE, and of course some guy comes along and decr...

general question for ASP and SQL database
Hi everyone, I've wrote a ASPX page that reads data from my SQL database. The SQL database reside in virtual window server 2003 that has put on the company's network. Everything works fine when the virtual server is on company's network. And now I unjoined the virtual server from the network and ASPX page doesn't work anymore. It seems because it can not get data from SQL server. Is there ways to make ASPX and my SQL server work fine without putting the server on the company's network? Should I setup a physical server and put SQL ser...

PB, ODBC and SQL Server7 general question
Are there any companies successfully using ODBC to connect Powerbuilder to MS SQL Server 7? If so, what version of PB are you using (6.5.1 or 7.x)? Do you use SQL statements in PB script or stored procedures? Did you encounter any problems that needed to be resolved when switching from a native connection to odbc? Any feedback would be appreciated. Thank you. Andre We couldn't get this to work successfully with 7.0. I can't remember the details, but there was one problem which required disablebind=1, and another that required disablebind=0. Out of interest, why ...

a general question about closing a connection to sql datareader
Hi,I have been using the following function to get a data reader for a while now, didnt run into problems until today. This morning, I got the cannot create connection from connection pool exception. Searched on the internet, it says the max connection at a time is 100. I thougth i have been closing the connection, well, i guess not.<code>    private function fGetDR(sqlstr as string) as sqldatareader        dim dr as sqldatareader        Dim myConnection As New SqlConnection(ConfigurationSettings.AppSe...

Some general & licensing SQL Anywhere v8 questions
Hi, I have few questions I need help with please. I'll just list them in no particular order: (1) Is there some sort of client installation for ASA 8? I have currently a 60 days trial installed on my workstation. When I get a full version, will there be a client installation included on CD? (2) With just a regular SQL Anywhere Studio, will I be able to have pier-to-pier clients connected to the SQL Anywhere server? (3) What are the major differences between SQL Anywhere Studio and SQL Anywhere Studio for Workgroups? (4) My application will be used on a small pier-t...

General questions about setting up & using SQL Server Express
Hi, I use VS2008 Pro with Oracle at work, and I'm trying to set up my home server for ASP.NET development.  I would appreciate any advice on how to do this, including how to do it correctly (i.e., best practices).  At home I am running Windows Server 2003 Standard with SQL Server Express 2005 and the 3.5 .NET framework on my server.  My PC has Visual Studio 2008 Standard and the version of SQL Server 2005 that ships with it. At work we have DBAs that handle database setup, including the migration of tables from test to production.  Our databases reside on separate s...

general question on SQL Server security and ASP.NET login controls?
I am desinging a ASP.NET web site which is supposed to track all invoice request of my middle scale company. We are currently using a software developed by myself which is running as Access Desktop Database with only one user.. I have transfered MS Access structure of my program into SQL Server and i have almost completed all ASP.NET forms which is required to enter all information for invoices. I assumed first, I had to desing all forms as if only one user will be responsible like Access Database. Then I planned to switch multiuser. Question: Daily invoice requests are coming from our const...

Web resources about - SQL question - sybase.sqlanywhere.general

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

Hillary Raises The Electability Question Again And The Polls Respond
The Clinton Machine is back to the inevitability argument which has served her so poorly in the past. And now, when they're not using the same ...

Question #2 for 2016: How many payroll jobs will be added in 2016?
Earlier I posted some questions for next year: Ten Economic Questions for 2016 . I'm adding some thoughts, and a few predictions for each question. ...

5 questions all first-time car buyers should ask
Don't visit the showroom without this handy set of questions all rookie car buyers should ask both of themselves and the car dealer

The Week Ahead: Three Questions Investors Should Answer
The global stock markets have certainly gotten the world's attention after just five days of trading in 2016. Even the very strong job report ...

Two Stops, No Questions, and One Embroidered Marc Jacobs Sweater: Getting Out the Vote With Lena Dunham ...
DES MOINES, IOWA—At two campaign events for Hillary Clinton in Iowa on Saturday, speaker Lena Dunham did not shake any hands, nor did she take ...

Steven Universe Tells Us That the Answer to All Our Questions is Love—But It's Also Fandom
When last week’s “Steven Bomb” of Steven Universe episodes began, it offered love as the real answer to many fans’ questions about the history ...

More Detail, New Questions About 2011 Clinton Email
The State Department provided more detail Saturday about a 2011 document at the center of Hillary Clinton's latest email controversy, as an official ...

‘Days Of Our Lives’ Spoilers: Rafe Crosses Lines For Hope And Chad Has A Big Question For Abigail
Days of Our Lives spoilers tease that Monday’s episode will be a big one. As viewers saw on Friday, Hope shot Stefano after he taunted her in ...

White House questions Sean Penn role in drug kingpin case
Los Angeles Times White House questions Sean Penn role in drug kingpin case Los Angeles Times Rolling Stone magazine is reporting that Mexican ...

Resources last updated: 1/12/2016 12:55:42 AM