Export CSV with multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
twodogdog
Forum Newbie
Posts: 7
Joined: Fri Nov 17, 2006 1:54 am

Export CSV with multiple tables

Post by twodogdog »

I want pull the data from three tables to one CSV. but I am no idea how to do that. Can someone in forum can help ?

thanks
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Search for mysql joins or unions. If the columns in each of the tables are arranged differently, you'll probably want to export to three different files.
twodogdog
Forum Newbie
Posts: 7
Joined: Fri Nov 17, 2006 1:54 am

Post by twodogdog »

Thanks,

I should do that with using JOIN.
twodogdog
Forum Newbie
Posts: 7
Joined: Fri Nov 17, 2006 1:54 am

Post by twodogdog »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I try combine parent and child tables with JOIN, but that export duplicate rows which results are same. how can I remove that duplicate rows ? (I attach my code below)

Thanks

[syntax="sql"]SELECT 
				Pt.id, 
				Pt.form_order_status, 
				Pt.form_order_int_ref, 
				Pt.form_order_ab, 
				Pt.form_order_customer, 
				Pt.form_order_action, 
				Pt.form_order_desc, 
				Pt.form_order_circuit, 
				Pt.form_order_bandwidth, 
				Pt.form_order_destination, 
				Pt.form_order_arcstar, 
				Pt.form_order_rfs, 
				Pt.form_order_pm, 
				Pt.form_order_comp_date, 
				Pt.form_mls_am, 
				Ct1.form_mls_revenue_cur, 
				Ct1.form_mls_revenue_nrc, 
				Ct1.form_mls_revenue_mrc, 
				Pt.form_mls_contract, 
				Pt.form_mls_billing, 
				Pt.form_pr_vendor, 
				Pt.form_pr_nisi, 
				Pt.form_pr_rental_purchase, 
				Pt.form_pr_quotation, 
				Pt.form_pr_date, 
				Ct2.form_pr_cost_cur, 
				Ct2.form_pr_cost_nrc, 
				Ct2.form_pr_cost_mrc, 
				Pt.form_pr_applicant, 
				Pt.form_pr_no 
			FROM TableA AS Pt
			Left Join TableB AS Ct1 
				ON Ct1.parent_id = Pt.id 
				AND Pt.deleted=0 
			Left Join TableC AS Ct2 
				ON Ct2.parent_id = Pt.id 
				AND Pt.deleted=0

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SELECT DISTINCT ...
twodogdog
Forum Newbie
Posts: 7
Joined: Fri Nov 17, 2006 1:54 am

Post by twodogdog »

my tables structure I made are like that:

TableA

+-----------+-------------+
id fieldA
+-----------+-------------+
+ 1 + value1 +
+ 2 + value2 +
+ 3 + value3 +
+ 4 + value4 +


TableB(Child Table)

+-----------+-------------+--------------+
id sub-id fieldB
+-----------+-------------+--------------+
+ 1 + 1 + value1 +
+ 1 + 2 + value2 +
+ 1 + 3 + value3 +
+ 2 + 4 + value4 +
+ 3 + 5 + value5 +

I found I will got duplicated rows with this code:

"SELECT DISTINCT
Pt.fieldA
Ct1.fieldB
FROM tableA AS Pt, tableB AS Ct1
WHERE Ct1._id = Pt.id"

I guess the query I wrote may generate some duplicated rows. How can I remove that duplicated rows ?

thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What results are you getting and what are you expecting?
twodogdog
Forum Newbie
Posts: 7
Joined: Fri Nov 17, 2006 1:54 am

Post by twodogdog »

Here is the result I got:

http://www.nodream.info/ntt/project_sta ... -02-07.csv

for example, ID9. there are three record in its chlid table. But I got total 9 rows in the exported file. And I found several rows are duplicated.

In short, the pattern of my export file is like that:

+---parent.id---+---chlid.fielda---+---chlid.fieldb---+chlid.fieldc---+
+-------9-------- +------USD--------+-------500-------+-----4000-----+
+-------9-------- +------HKD--------+-------200-------+-----2000-----+
+-------9-------- +------RMB--------+-------100-------+-----1500-----+
+-------9-------- +------USD--------+-------500-------+-----4000-----+
+-------9-------- +------HKD--------+-------200-------+-----2000-----+
+-------9-------- +------RMB--------+-------100-------+-----1500-----+
+-------9-------- +------USD--------+-------500-------+-----4000-----+
+-------9-------- +------HKD--------+-------200-------+-----2000-----+
+-------9-------- +------RMB--------+-------100-------+-----1500-----+


I just need the first three rows. But the last six rows is duplicated.


thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'm not seeing duplicates in the csv you linked.
twodogdog
Forum Newbie
Posts: 7
Joined: Fri Nov 17, 2006 1:54 am

Post by twodogdog »

Thanks feyd's reply.

I found value of certain row is same with other rows. I will try other resource to solve this problem.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Just so you know, the DISTINCT keyword works on all columns returned. So if your query is returning many columns the chances of a row being unique in the result set are often higher.

To artificially compact records that would normally be included the GROUP BY clause is often employed (or better constraints on the WHERE or HAVING clauses.)
Post Reply