Either do two queries or suppress the display of customer information within
your application code. I'd probably do two queries, especially if asking for a
lot of customer info or there were potentially a large number of orders for a
given customer. Doing a join in either case gives you a lot of extra data that
you don't need.
----- Original Message -----
From: "Trevor Morrison" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, July 27, 2003 8:42 PM
Subject: SQL statement
> HI,
>
> I am using the latest MySql on a Windows 2000 platform and connecting to the
> database using Perl's DBI. All works fine. My question is how best to
> write a SQL SELECT statement that will extract the data in the format that I
> want.
>
> I have two tables one that contains all the customers information and the
> second table contains the items that are ordered by the customer ( they both
> have the order_numbre is each table). My select statement--shown
> below--works fine for a customer that orders a single item, but fails when
> more then one item is ordered. It will print the customer information
> before each item ordered. What I want is to print just the customer
> information once, and then print each item ordered by the customer after
> that. Any help is appreciated.
>
> TIA
>
> Trevor
>
>
> my $sth =$dbh->prepare("SELECT
> miva_orders.order_number,DATE_FORMAT(miva_orders.date,'%c/%e/%Y'),miva_order
> s.credit_card_type,miva_orders.shiptype,miva_orders.bill_name,miva_orders.bi
> ll_company,miva_orders.bill_addr1,miva_orders.bill_addr2,miva_orders.bill_ci
> ty,miva_orders.bill_state,miva_orders.bill_zip,miva_orders.ship_name,miva_or
> ders.ship_addr1,miva_orders.ship_addr2,miva_orders.ship_city,miva_orders.shi
> p_state,miva_orders.ship_zip,miva_orders.email,miva_orders.phone,miva_orders
> .sales_tax,miva_orders.shipping_amount,miva_items_ordered.part_number,miva_i
> tems_ordered.quantity FROM (miva_orders,miva_items_ordered) WHERE
> miva_orders.order_number=miva_items_ordered.order_number");
> $sth->execute();
> my $i=0;
> while (my @array = $sth->fetchrow_array()) {
> for ( $i = 0; $i <= $#array; $i++) {
> print PARSED $array[$i] . ( ($i >=$#array) ? "\015" : "\t");
> }
> }
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
