Export CSV with multiple tables
Moderator: General Moderators
Export CSV with multiple tables
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
thanks
feyd | Please use
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]
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=0feyd | 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]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
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
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
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
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.)
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.)