Page 1 of 1
Export CSV with multiple tables
Posted: Sun Jan 21, 2007 11:51 pm
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
Posted: Mon Jan 22, 2007 1:07 am
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.
Posted: Mon Jan 29, 2007 8:33 pm
by twodogdog
Thanks,
I should do that with using JOIN.
Posted: Sun Feb 04, 2007 10:04 pm
by twodogdog
feyd | Please use 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
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]
Posted: Sun Feb 04, 2007 10:30 pm
by feyd
SELECT DISTINCT ...
Posted: Tue Feb 06, 2007 9:23 pm
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
Posted: Tue Feb 06, 2007 9:48 pm
by feyd
What results are you getting and what are you expecting?
Posted: Tue Feb 06, 2007 10:16 pm
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
Posted: Tue Feb 06, 2007 10:23 pm
by feyd
I'm not seeing duplicates in the csv you linked.
Posted: Wed Feb 07, 2007 9:54 pm
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.
Posted: Thu Feb 08, 2007 8:55 am
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.)