How to write this query?
How to write the query? table1: order (order can have more than 1 item) table2: item (has order_id). table3: item_status (has item_status_id and item_id) table4: status (has item_status_id). Status can be 'complete', 'pending'. I need to get all orders that have ONLY completed items. Examples: if order has one item and it is completed, I need this order. If order has 2 items and both completed, I need this order. If order has 2 items, 1 is completed and 1 is not completed, I don't need this order. Thanks
Re: How to write this query?
Hi Jerry.
I'm new to the list, so don't take this as the final
answer. Wait for a guru to pounce with a better
solution!
But, here is my idea:
Before we start, note that order is a reserved word.
So we must backtick `order` to reference the table in
SQL (or the interpreter will think we're using order
by)
Now, here's how I understand your schema:
Table `order` has column 'order_id'
Table item has columns 'order_id','item_id'
Table item_status has columns 'item_id',
'item_status_id'
Table status has columns
'item_status_id','status_text'
where status_text is {complete|pending}
I've given 3 queries. The last query is 'the winner'.
The first two are there only to show the 'thinking
path' I took. It might help you in case I didn't
quite understand your schema.
1: (Just to the joins)
SELECT `order`.order_id, item.item_id
,status.status_text
FROM `order` INNER JOIN item USING (order_id)
INNER JOIN item_status USING(item_id)
INNER JOIN status USING (item_status_id);
2: (Group up by Order_id)
SELECT `order`.order_id, item.item_id,
group_concat(status.status_text) as all_stats
FROM `order` INNER JOIN item USING (order_id)
INNER JOIN item_status USING(item_id)
INNER JOIN status USING (item_status_id)
GROUP BY order_id;
3: THE ANSWER Filter out the 'pendings':
SELECT `order`.order_id, item.item_id,
group_concat(status.status_text) as all_stats
FROM `order` INNER JOIN item USING (order_id)
INNER JOIN item_status USING(item_id)
INNER JOIN status USING (item_status_id)
GROUP BY order_id
HAVING all_stats NOT LIKE %pending%;
HTH.
-Mike
P.S.: Please, List comment and clean my SQL! I'm
sure there is a better solution than using
'group_concat()' I'm here to learn too :)
--- Jerry Swanson [EMAIL PROTECTED] wrote:
How to write the query?
table1: order (order can have more than 1 item)
table2: item (has order_id).
table3: item_status (has item_status_id and item_id)
table4: status (has item_status_id). Status can be
'complete', 'pending'.
I need to get all orders that have ONLY completed
items.
Examples:
if order has one item and it is completed, I need
this order.
If order has 2 items and both completed, I need this
order.
If order has 2 items, 1 is completed and 1 is not
completed, I don't need
this order.
Thanks
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to write this query?
Try it: select distinct a.* from order a, item b, item_status c, status d where a.order_id = b.order_id and b.item_id=c.item_id and c.item_status_id = d.item_status_id where d.status = 'completed' It isn´t tunned but I think that it works for that you want -Mensaje original- De: Jerry Swanson [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 10 de Noviembre de 2005 06:23 a.m. Para: mysql@lists.mysql.com Asunto: How to write this query? How to write the query? table1: order (order can have more than 1 item) table2: item (has order_id). table3: item_status (has item_status_id and item_id) table4: status (has item_status_id). Status can be 'complete', 'pending'. I need to get all orders that have ONLY completed items. Examples: if order has one item and it is completed, I need this order. If order has 2 items and both completed, I need this order. If order has 2 items, 1 is completed and 1 is not completed, I don't need this order. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write this query?
item_status table can have more than one status. I need to get the latest status from the table. Thanks On 11/10/05, ISC Edwin Cruz [EMAIL PROTECTED] wrote: Try it: select distinct a.* from order a, item b, item_status c, status d where a.order_id = b.order_id and b.item_id=c.item_id and c.item_status_id = d.item_status_id where d.status = 'completed' It isn´t tunned but I think that it works for that you want -Mensaje original- De: Jerry Swanson [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 10 de Noviembre de 2005 06:23 a.m. Para: mysql@lists.mysql.com Asunto: How to write this query? How to write the query? table1: order (order can have more than 1 item) table2: item (has order_id). table3: item_status (has item_status_id and item_id) table4: status (has item_status_id). Status can be 'complete', 'pending'. I need to get all orders that have ONLY completed items. Examples: if order has one item and it is completed, I need this order. If order has 2 items and both completed, I need this order. If order has 2 items, 1 is completed and 1 is not completed, I don't need this order. Thanks
Re: How to write this query?
Michael McFadden [EMAIL PROTECTED] wrote on 11/10/2005 08:55:13 AM:
Hi Jerry.
I'm new to the list, so don't take this as the final
answer. Wait for a guru to pounce with a better
solution!
But, here is my idea:
Before we start, note that order is a reserved word.
So we must backtick `order` to reference the table in
SQL (or the interpreter will think we're using order
by)
Now, here's how I understand your schema:
Table `order` has column 'order_id'
Table item has columns 'order_id','item_id'
Table item_status has columns 'item_id',
'item_status_id'
Table status has columns
'item_status_id','status_text'
where status_text is {complete|pending}
I've given 3 queries. The last query is 'the winner'.
The first two are there only to show the 'thinking
path' I took. It might help you in case I didn't
quite understand your schema.
1: (Just to the joins)
SELECT `order`.order_id, item.item_id
,status.status_text
FROM `order` INNER JOIN item USING (order_id)
INNER JOIN item_status USING(item_id)
INNER JOIN status USING (item_status_id);
2: (Group up by Order_id)
SELECT `order`.order_id, item.item_id,
group_concat(status.status_text) as all_stats
FROM `order` INNER JOIN item USING (order_id)
INNER JOIN item_status USING(item_id)
INNER JOIN status USING (item_status_id)
GROUP BY order_id;
3: THE ANSWER Filter out the 'pendings':
SELECT `order`.order_id, item.item_id,
group_concat(status.status_text) as all_stats
FROM `order` INNER JOIN item USING (order_id)
INNER JOIN item_status USING(item_id)
INNER JOIN status USING (item_status_id)
GROUP BY order_id
HAVING all_stats NOT LIKE %pending%;
HTH.
-Mike
P.S.: Please, List comment and clean my SQL! I'm
sure there is a better solution than using
'group_concat()' I'm here to learn too :)
--- Jerry Swanson [EMAIL PROTECTED] wrote:
How to write the query?
table1: order (order can have more than 1 item)
table2: item (has order_id).
table3: item_status (has item_status_id and item_id)
table4: status (has item_status_id). Status can be
'complete', 'pending'.
I need to get all orders that have ONLY completed
items.
Examples:
if order has one item and it is completed, I need
this order.
If order has 2 items and both completed, I need this
order.
If order has 2 items, 1 is completed and 1 is not
completed, I don't need
this order.
Thanks
This will work. Good advice on backticking the ORDER names. You followed
nice logic in building your answer. The drawback is you are doing a
substring check to determine your final results, not very quick. A faster
method is to just count how many items each order has and compare to how
many items have a complete status. Number-to-number comparison is from 5
to 50 times faster than string comparisons.
Try this:
SELECT i.`order_id`
, COUNT(DISTINCT i.`item_id`) as items_this_order
, COUNT(s.`item_status_id`) as completed_items
FROM `item` i
INNER JOIN `item_status` istat
ON istat.`item_id` = i.`item_id`
LEFT JOIN status s
ON s.`item_status_id` = istat.`item_status_id`
AND s.`status`='complete'
GROUP BY i.`order_id`
HAVING items_this_order = completed_items;
The LEFT JOIN will only link in a row from `status` if the row in
`item_status` is 'complete' (look at the final ON clause). For any items
without a match, the query will return NULL as the value from any field
from the status table. COUNT() only counts non-NULL values. See the logic?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: how to write this query?
It's not precisely correct.
When time difference is less than 7, the time is calcualted wrong
end_time 2005-01-10 17:53:33
end_time 2005-01-04 16:44:57
Result: days 6
Result: bussiness_days 6
On Sat, 19 Feb 2005 09:50:06 -0500, Mike Rains [EMAIL PROTECTED] wrote:
On Sat, 19 Feb 2005 14:01:05 +, Jerry Swanson [EMAIL PROTECTED] wrote:
I have two dates (start_date, end_date). Datediff() function returns
difference in days.
I need the difference but not including Satuday and Sunday.
Any ideas?
C:\Program Files\MySQL\MySQL Server 4.1\binmysql -utest -ptest test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 4.1.8-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql CREATE TABLE `DateDiffs` (
- start_date DATETIME,
- end_date DATETIME
- );
Query OK, 0 rows affected (0.15 sec)
mysql INSERT INTO DateDiffs
- (start_date, end_date)
- VALUES
- ('2005-02-14 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-07 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-04 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-31 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-24 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-21 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-17 00:00:00', '2005-02-18 00:00:00');
Query OK, 9 rows affected (0.06 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql SELECT
-start_date,
-end_date,
-datediff(end_date, start_date)
-AS dd1,
-datediff(end_date, start_date) -
floor(datediff(end_date, start_date) / 7) * 2
-AS dd2
- FROM DateDiffs
- ORDER BY start_date;
+-+-+--+--+
| start_date | end_date| dd1 | dd2 |
+-+-+--+--+
| 2005-01-17 00:00:00 | 2005-02-18 00:00:00 | 32 | 24 |
| 2005-01-21 00:00:00 | 2005-02-18 00:00:00 | 28 | 20 |
| 2005-01-24 00:00:00 | 2005-02-18 00:00:00 | 25 | 19 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-31 00:00:00 | 2005-02-18 00:00:00 | 18 | 14 |
| 2005-02-04 00:00:00 | 2005-02-18 00:00:00 | 14 | 10 |
| 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 11 |9 |
| 2005-02-14 00:00:00 | 2005-02-18 00:00:00 |4 |4 |
+-+-+--+--+
9 rows in set (0.00 sec)
mysql DROP TABLE DateDiffs;
Query OK, 0 rows affected (0.19 sec)
mysql exit
The column dd1 contains the unaltered DATEDIFF() which includes the
Saturdays and Sundays, while the dd2 column contains the number of
business days omitting the weekend days.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to write this query?
SELECT start_date, end_date, DATEDIFF(end_date, start_date) - (WEEK(end_date) - WEEK(start_date)) * 2 AS business_days FROM DateDiffs ORDER BY start_date; +-+-+---+ | start_date | end_date| business_days | +-+-+---+ | 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 | | 2005-01-17 00:00:00 | 2005-02-18 00:00:00 |24 | | 2005-01-21 00:00:00 | 2005-02-18 00:00:00 |20 | | 2005-01-24 00:00:00 | 2005-02-18 00:00:00 |19 | | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 |15 | | 2005-01-31 00:00:00 | 2005-02-18 00:00:00 |14 | | 2005-02-04 00:00:00 | 2005-02-18 00:00:00 |10 | | 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 | | 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 | +-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to write this query?
This works if you don't care about holidays. If you do the only solution that I have seen that works is to create a business day table. Ours is shown below. You have to hand construct the calendar by removing weekends and holidays for the specific entity. This calendar forces a non business day DATE to the next business day. Date arithmetic then becomes simple including FIRST MONDAY OF THE MONTH in the US where many holidays fall on Monday. clnd_Day is the relative business day since 2000-01-01 clnd_Week is the relative business week since 2000-01-01 mysql show create table calendar; +--+- - | Table| Create Table +--+- - | calendar | CREATE TABLE `calendar` ( `clnd_Day` smallint(5) unsigned NOT NULL default '0', `clnd_Date` date NOT NULL default '-00-00', `clnd_Week_Day_Txt` char(9) default NULL, `clnd_Week_Day_Num` tinyint(3) unsigned default NULL, `clnd_Char_Date` char(12) default NULL, `clnd_Week` smallint(8) unsigned default NULL, `clnd_Real_Date` char(10) default NULL, PRIMARY KEY (`clnd_Date`), UNIQUE KEY `clnd_Real_Date_IDX` (`clnd_Real_Date`), KEY `clnd_Day_IDX` (`clnd_Day`), KEY `clnd_Char_Date` (`clnd_Char_Date`) ) TYPE=InnoDB | mysql select * from calendar limit 10; +--++---+---+--- -+---++ | clnd_Day | clnd_Date | clnd_Week_Day_Txt | clnd_Week_Day_Num | clnd_Char_Date | clnd_Week | clnd_Real_Date | +--++---+---+--- -+---++ |1 | 2000-01-01 | Monday| 2 | 01/03/2000 | 1 | 01/01/2000 | |1 | 2000-01-02 | Monday| 2 | 01/03/2000 | 1 | 01/02/2000 | |1 | 2000-01-03 | Monday| 2 | 01/03/2000 | 1 | 01/03/2000 | |2 | 2000-01-04 | Tuesday | 3 | 01/04/2000 | 1 | 01/04/2000 | |3 | 2000-01-05 | Wednesday | 4 | 01/05/2000 | 1 | 01/05/2000 | |4 | 2000-01-06 | Thursday | 5 | 01/06/2000 | 1 | 01/06/2000 | |5 | 2000-01-07 | Friday| 6 | 01/07/2000 | 1 | 01/07/2000 | |6 | 2000-01-08 | Monday| 2 | 01/10/2000 | 2 | 01/08/2000 | |6 | 2000-01-09 | Monday| 2 | 01/10/2000 | 2 | 01/09/2000 | |6 | 2000-01-10 | Monday| 2 | 01/10/2000 | 2 | 01/10/2000 | +--++---+---+--- -+---++ 10 rows in set (0.00 sec) -Original Message- From: Mike Rains [mailto:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 9:33 AM To: mysql@lists.mysql.com Subject: Re: how to write this query? SELECT start_date, end_date, DATEDIFF(end_date, start_date) - (WEEK(end_date) - WEEK(start_date)) * 2 AS business_days FROM DateDiffs ORDER BY start_date; +-+-+---+ | start_date | end_date| business_days | +-+-+---+ | 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 | | 2005-01-17 00:00:00 | 2005-02-18 00:00:00 |24 | | 2005-01-21 00:00:00 | 2005-02-18 00:00:00 |20 | | 2005-01-24 00:00:00 | 2005-02-18 00:00:00 |19 | | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 |15 | | 2005-01-31 00:00:00 | 2005-02-18 00:00:00 |14 | | 2005-02-04 00:00:00 | 2005-02-18 00:00:00 |10 | | 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 | | 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 | +-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to write this query?
I have two dates (start_date, end_date). Datediff() function returns difference in days. I need the difference but not including Satuday and Sunday. Any ideas? TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to write this query?
On Sat, 19 Feb 2005 14:01:05 +, Jerry Swanson [EMAIL PROTECTED] wrote:
I have two dates (start_date, end_date). Datediff() function returns
difference in days.
I need the difference but not including Satuday and Sunday.
Any ideas?
C:\Program Files\MySQL\MySQL Server 4.1\binmysql -utest -ptest test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 4.1.8-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql CREATE TABLE `DateDiffs` (
- start_date DATETIME,
- end_date DATETIME
- );
Query OK, 0 rows affected (0.15 sec)
mysql INSERT INTO DateDiffs
- (start_date, end_date)
- VALUES
- ('2005-02-14 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-07 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-04 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-31 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-24 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-21 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-17 00:00:00', '2005-02-18 00:00:00');
Query OK, 9 rows affected (0.06 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql SELECT
-start_date,
-end_date,
-datediff(end_date, start_date)
-AS dd1,
-datediff(end_date, start_date) -
floor(datediff(end_date, start_date) / 7) * 2
-AS dd2
- FROM DateDiffs
- ORDER BY start_date;
+-+-+--+--+
| start_date | end_date| dd1 | dd2 |
+-+-+--+--+
| 2005-01-17 00:00:00 | 2005-02-18 00:00:00 | 32 | 24 |
| 2005-01-21 00:00:00 | 2005-02-18 00:00:00 | 28 | 20 |
| 2005-01-24 00:00:00 | 2005-02-18 00:00:00 | 25 | 19 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-31 00:00:00 | 2005-02-18 00:00:00 | 18 | 14 |
| 2005-02-04 00:00:00 | 2005-02-18 00:00:00 | 14 | 10 |
| 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 11 |9 |
| 2005-02-14 00:00:00 | 2005-02-18 00:00:00 |4 |4 |
+-+-+--+--+
9 rows in set (0.00 sec)
mysql DROP TABLE DateDiffs;
Query OK, 0 rows affected (0.19 sec)
mysql exit
The column dd1 contains the unaltered DATEDIFF() which includes the
Saturdays and Sundays, while the dd2 column contains the number of
business days omitting the weekend days.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to write a query to return records has the closest date to end of month
Hi, guys I would like to write a query to return all records which has the closest date to the end of month. (Assume that date will be 6/30/2004) | id | product_id | price | snapshot_date | ++++---+ | 1 | 1 | 99.95 |2004-06-23 | | 2 | 1 | 99.74 |2004-06-27 | * | 3 | 2 | 101.52 |2004-06-25 | | 4 | 2 | 101.85 |2004-07-01 | * | 5 | 3 | 100.00 |2004-06-29 | | 6 | 3 | 100.50 |2004-07-01 | * ++++---+ *if there is a tie, it always pick the one after the end of month The 2, 4, 6 entries shall be return by the query. Is this possible to write this in pure sql? Thanks in advance. Lam The best thing to hit the Internet in years - NetZero HiSpeed! Surf the Web up to FIVE TIMES FASTER! Only $14.95/ month -visit www.netzero.com to sign up today! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to write such query?
I want to get distinct title from table titles. But there are two differnt
titles (new and used).So I need somehow join all three tables where
user_tiles
status='true' and titles are distinctive. Thanks
titles
+---+---+--+-+-++
| Field | Type | Null | Key | Default |
Extra |
+---+---+--+-+-++
| title_id | int(5)| | PRI | NULL|
auto_increment |
| titles| varchar(5)| | MUL | |
|
| quantity | int(5)| | | 0 |
|
| condition | enum('new', 'used') | | | |
|
+---+---+--+-+-++
new_titles
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| title_id | int(8) | | PRI | 0 | |
| cost | decimal(5,2) | | | 0.00| |
used_titles
++--+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
++--+--+-+-+---+
| title_id | int(8)
| | PRI | 0 | |
| status | enum('true','false')
| | | true| |
_
Learn how to choose, serve, and enjoy wine at Wine @ MSN.
http://wine.msn.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write this query
Sean, Slight rewriting of Kevin's query--I assume you want to do the joins on A_ID. SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID WHERE A.A_ID = 4; This should work. For your example, the first left join gives a table with A.* and nulls for B.*. Then, the second left join gives you C.* for that A_ID; it doesn't matter that the B.* part contains nulls. Bill From: sean peters [EMAIL PROTECTED] To: Kevin Fries [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: How to write this query Date: Wed, 1 Oct 2003 16:22:46 -0500 Unfortunately that wont always work either. For instance, assume that there is an A record with A_ID = 4 And that there is a C record where A_ID = 4, but NO B record where A_ID = 4 So, executing the query: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; When A left joins B, there is no real B record, so any B columns are populated with null, as per left join. Then, table B is left joined to C on A_ID, which is null, and no C record will properly match the B.A_ID = NULL, so the C record is filled with nulls. If we were to join A to C then to B, a similar problem would occur if there was a cooresponding B record, but no C record. Thanks anyway. On Wednesday 01 October 2003 14:25, Kevin Fries wrote: You're on the right track with LEFT JOIN. Just continue the thought... Try: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:07 PM To: [EMAIL PROTECTED] Subject: How to write this query I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_ID INT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_ID INT NOT NULL PRIMARY KEY, A_ID INT NOT NULL, B_data text ); CREATE TABLE C ( C_ID INT NOT NULL PRIMARY KEY, A_ID INT NOT NULL, C_data text ); So ive been running a query like: SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.B_ID AND A.A_ID = C.C_ID AND A.A_ID = 4; What i really want is to get the A_data from A, and if there are cooresponding records in B and/or C, get B_data and/or C_data, respectively. This works fine if there are cooresponding records in tables B and C for each record in A, but if not, this returns nothing. So, short of querying each table, i cant come up with a good solution to my problem. If there were only 2 tables, a LEFT JOIN would work fine, but both B and C want to be left joined to A, which i dont know how to do. thanks sean peters [EMAIL PROTECTED] --- mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to write this query
I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_IDINT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, B_data text ); CREATE TABLE C ( C_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, C_data text ); So ive been running a query like: SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.B_ID AND A.A_ID = C.C_ID AND A.A_ID = 4; What i really want is to get the A_data from A, and if there are cooresponding records in B and/or C, get B_data and/or C_data, respectively. This works fine if there are cooresponding records in tables B and C for each record in A, but if not, this returns nothing. So, short of querying each table, i cant come up with a good solution to my problem. If there were only 2 tables, a LEFT JOIN would work fine, but both B and C want to be left joined to A, which i dont know how to do. thanks sean peters [EMAIL PROTECTED] --- mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to write this query
You're on the right track with LEFT JOIN. Just continue the thought... Try: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:07 PM To: [EMAIL PROTECTED] Subject: How to write this query I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_IDINT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, B_data text ); CREATE TABLE C ( C_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, C_data text ); So ive been running a query like: SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.B_ID AND A.A_ID = C.C_ID AND A.A_ID = 4; What i really want is to get the A_data from A, and if there are cooresponding records in B and/or C, get B_data and/or C_data, respectively. This works fine if there are cooresponding records in tables B and C for each record in A, but if not, this returns nothing. So, short of querying each table, i cant come up with a good solution to my problem. If there were only 2 tables, a LEFT JOIN would work fine, but both B and C want to be left joined to A, which i dont know how to do. thanks sean peters [EMAIL PROTECTED] --- mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write this query
Unfortunately that wont always work either. For instance, assume that there is an A record with A_ID = 4 And that there is a C record where A_ID = 4, but NO B record where A_ID = 4 So, executing the query: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; When A left joins B, there is no real B record, so any B columns are populated with null, as per left join. Then, table B is left joined to C on A_ID, which is null, and no C record will properly match the B.A_ID = NULL, so the C record is filled with nulls. If we were to join A to C then to B, a similar problem would occur if there was a cooresponding B record, but no C record. Thanks anyway. On Wednesday 01 October 2003 14:25, Kevin Fries wrote: You're on the right track with LEFT JOIN. Just continue the thought... Try: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:07 PM To: [EMAIL PROTECTED] Subject: How to write this query I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_IDINT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, B_data text ); CREATE TABLE C ( C_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, C_data text ); So ive been running a query like: SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.B_ID AND A.A_ID = C.C_ID AND A.A_ID = 4; What i really want is to get the A_data from A, and if there are cooresponding records in B and/or C, get B_data and/or C_data, respectively. This works fine if there are cooresponding records in tables B and C for each record in A, but if not, this returns nothing. So, short of querying each table, i cant come up with a good solution to my problem. If there were only 2 tables, a LEFT JOIN would work fine, but both B and C want to be left joined to A, which i dont know how to do. thanks sean peters [EMAIL PROTECTED] --- mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to write this query
Now I'm lost. Do you really mean to be joining A.A_ID against B.B_ID? Seems like it should be A.A_ID = B.A_ID... That's a traditional naming condition. If so, my recommendation should have been: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID WHERE A.A_ID = 4; If not, maybe you can describe your data better, with examples. Regarding: When A left joins B, there is no real B record, so any B columns are populated with null, as per left join. Then, table B is left joined to C on A_ID, which is null, and no C record will That shouldn't be true. The join was (A left-join B), then that result set joined to C. And the comparison was A.A_ID = C.C_ID. If the resultset's A.A_ID has data, the C comparison will succeed regardless of B.B_ID being null. Kevin Fries -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 2:23 PM To: Kevin Fries; [EMAIL PROTECTED] Subject: Re: How to write this query Unfortunately that wont always work either. For instance, assume that there is an A record with A_ID = 4 And that there is a C record where A_ID = 4, but NO B record where A_ID = 4 So, executing the query: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; When A left joins B, there is no real B record, so any B columns are populated with null, as per left join. Then, table B is left joined to C on A_ID, which is null, and no C record will properly match the B.A_ID = NULL, so the C record is filled with nulls. If we were to join A to C then to B, a similar problem would occur if there was a cooresponding B record, but no C record. Thanks anyway. On Wednesday 01 October 2003 14:25, Kevin Fries wrote: You're on the right track with LEFT JOIN. Just continue the thought... Try: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:07 PM To: [EMAIL PROTECTED] Subject: How to write this query I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_IDINT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, B_data text ); CREATE TABLE C ( C_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, C_data text ); So ive been running a query like: SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.B_ID AND A.A_ID = C.C_ID AND A.A_ID = 4; What i really want is to get the A_data from A, and if there are cooresponding records in B and/or C, get B_data and/or C_data, respectively. This works fine if there are cooresponding records in tables B and C for each record in A, but if not, this returns nothing. So, short of querying each table, i cant come up with a good solution to my problem. If there were only 2 tables, a LEFT JOIN would work fine, but both B and C want to be left joined to A, which i dont know how to do. thanks sean peters [EMAIL PROTECTED] --- mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updated: How to write this query
Sorry, I had an error in my query. The fixed query with the entire post follows. Thanks for the responses to the incorrect one, im pretty sure that the suggestions will still fail for the previously indicated reasons, even with the modified query. ORIGINAL POST: (fixed) I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_IDINT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, B_data text ); CREATE TABLE C ( C_IDINT NOT NULL PRIMARY KEY, A_IDINT NOT NULL, C_data text ); So ive been running a query like: SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.A_ID AND A.A_ID = C.A_ID AND A.A_ID = 4; *** ORIGINAL INCORRECT QUERY :( *** SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.B_ID AND A.A_ID = C.C_ID AND A.A_ID = 4; What i really want is to get the A_data from A, and if there are cooresponding records in B and/or C, get B_data and/or C_data, respectively. This works fine if there are cooresponding records in tables B and C for each record in A, but if not, this returns nothing. So, short of querying each table, i cant come up with a good solution to my problem. If there were only 2 tables, a LEFT JOIN would work fine, but both B and C want to be left joined to A, which i dont know how to do. thanks sean peters [EMAIL PROTECTED] --- mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updated: How to write this query
On Wed, 1 Oct 2003 16:58:26 -0500, sean peters [EMAIL PROTECTED] wrote: [...] | So ive been running a query like: | SELECT A_data, B_data, C_data FROM A, B, C | WHERE A.A_ID = B.A_ID | AND A.A_ID = C.A_ID | AND A.A_ID = 4; | [...] | | What i really want is to get the A_data from A, and if there are cooresponding | records in B and/or C, get B_data and/or C_data, respectively. | | This works fine if there are cooresponding records in tables B and C for each | record in A, but if not, this returns nothing. | | So, short of querying each table, i cant come up with a good solution to my | problem. | | If there were only 2 tables, a LEFT JOIN would work fine, but both B and C | want to be left joined to A, which i dont know how to do. SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID WHERE A.A_ID = 4; That should do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write this query pls?
Ways around inner select statments Select ID, Sum(CASE WHEN phone.PHN = NULL THEN 1 ELSE 0 END) as PHNCount from person left outer join phone on ID where PHNCount = 0 GROUP BY phone.ID; The alias in the WHERE clause is illegal; it would have to be SELECT persons.ID, Sum( CASE WHEN phone.type = 'PHN' THEN 1 ELSE 0 END ) AS PHNCount FROM persons LEFT OUTER JOIN phone USING ( ID ) GROUP BY phone.ID HAVING phncount = 0; which on this machine is up to ten times slower than SELECT * FROM persons pe LEFT JOIN phone ph ON pe.ID = ph.ID AND ph.type = 'PHN' WHERE ph.type IS NULL; PB [mysql] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to write delete query??
What you have there is a subselect, which are not supported in mysql 3.2x, but I *believe* are supported in 4.0+ (gamma and 4.1alpha). Don't take my word for it and check the docs first. --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: Inandjo Taurel [mailto:[EMAIL PROTECTED]] Sent: Monday, February 17, 2003 6:35 AM To: [EMAIL PROTECTED] Subject: How to write delete query?? SQL SQL hi all, i have a query that works just fine on Dbase, but just crashes on mysql. How can i write it in one statement for it to work both on Dbase and mysql? the query: - delete from table1 where table1.field1 in (select distinct field2 from table2). Thanx _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to write delete query??
thanx joe, i checked. I just thought that there could be a way around this problem! From: Joe Stump [EMAIL PROTECTED] To: Inandjo Taurel [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: How to write delete query?? Date: Mon, 17 Feb 2003 09:46:53 -0800 MIME-Version: 1.0 Received: from mail4.mxpath.net ([130.94.106.86]) by mc5-f9.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 17 Feb 2003 06:41:02 -0800 Received: by mail4.mxpath.net (Postfix, from userid 89)id ACE4113BA62; Mon, 17 Feb 2003 06:35:30 + (GMT) Received: (qmail 21374 invoked from network); 17 Feb 2003 06:35:28 - Received: from web.mysql.com (213.136.49.183) by 130.94.106.86 with SMTP; 17 Feb 2003 06:35:28 - Received: (qmail 30777 invoked by uid 7797); 17 Feb 2003 14:40:03 - Received: (qmail 30759 invoked from network); 17 Feb 2003 14:40:02 - X-Message-Info: dHZMQeBBv44lPE7o4B5bAg== Delivered-To: [EMAIL PROTECTED] Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 In-Reply-To: [EMAIL PROTECTED] Importance: Normal X-MxUser: [EMAIL PROTECTED] X-MxHost: [EMAIL PROTECTED] X-MxDest:Maildir Return-Path: X-OriginalArrivalTime: 17 Feb 2003 14:41:03.0057 (UTC) FILETIME=[986BDC10:01C2D692] What you have there is a subselect, which are not supported in mysql 3.2x, but I *believe* are supported in 4.0+ (gamma and 4.1alpha). Don't take my word for it and check the docs first. --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: Inandjo Taurel [mailto:[EMAIL PROTECTED]] Sent: Monday, February 17, 2003 6:35 AM To: [EMAIL PROTECTED] Subject: How to write delete query?? SQL SQL hi all, i have a query that works just fine on Dbase, but just crashes on mysql. How can i write it in one statement for it to work both on Dbase and mysql? the query: - delete from table1 where table1.field1 in (select distinct field2 from table2). Thanx _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to write delete query??
Inandjo, - delete from table1 where table1.field1 in (select distinct field2 from table2). Subselects (or nested queries) like that are supported as of MySQL 4.1. At this moment, you will have to compile 4.1 yourself; it's expected to be out as a binary version in the next couple of weeks. The most recent binary distribution is 4.0.10, just to make sure you don't mix up version numbers ;) Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to write delete query??
Inandjo - delete from table1 where table1.field1 in (select distinct field2 from table2) From what I've read subqueries are not yet supported. We worked around this by converting the distinct list to a comma delimited list of values. E.g., : *- Create a delimited list of field1 from table1 lcDelimitedList = goQCApp.p_oFunc.m_CreateList(table1,field1) *- Build our query string sqlString = Delete from table1 where table.field1 in ( + lcDelimitedList + ) *- Execute query string If !goQCApp.m_SqlExec(sqlString) return messagebox(Rutt, row Query Failed =+sqlString,16,ABORTED)=6 endif : Note that where MySql is happy with both double and single quotes (for strings), Microsoft Sql Server chokes on double quotes; we found it best to use single quotes. We're in the midst of our conversion (from Visual Foxpro to MySql/Sql backend) and have not yet seen a limitation in using this approach. - Bill K. -Original Message- From: Inandjo Taurel [mailto:[EMAIL PROTECTED]] Sent: Monday, February 17, 2003 8:35 AM To: [EMAIL PROTECTED] Subject: How to write delete query?? SQL SQL hi all, i have a query that works just fine on Dbase, but just crashes on mysql. How can i write it in one statement for it to work both on Dbase and mysql? the query: - delete from table1 where table1.field1 in (select distinct . Thanx _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to write this query??
If you have the option to change the table structure, just replace the date and time columns with a timestamp column. If you must keep the current structure then the following wil work, but it will not use indexes in the search: select ... where concat(today,' ',heure1) between '2002-01-01 17:00:00' and '2002-01-30 08:00:59'; To allow MySQL to use an index on today, add an extra clause: select ... where today between '2002-01-01' and '2002-01-30' and concat(today,' ',heure1) between '2002-01-01 17:00:00' and '2002-01-30 08:00:59'; The following would also work, and might allow an index scan of an index on (today, heure1): select ... where today between '2002-01-01' and '2002-01-30' and (today '2002-01-01' or heure1 = '17:00:00') and (today '2002-01-30' or heure1 = '08:00:59'); From: Inandjo Taurel [EMAIL PROTECTED] Subject: How to write this query?? Date: Thu, 23 Jan 2003 18:36:40 + hi all, i have a table trans with this structure: today(date) | heure1(time) | amount(double) | client(varchar 30) Now i need to write a query that would take the start date and time, end date and time, and return all the records between the two. So for example, the query should return all records between 01/01/2002 at 17:00 and 01/30/2002 at 08:00. How can i get that query in one query?? SQL SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to write this query??
hi all, i have a table trans with this structure: today(date) | heure1(time) | amount(double) | client(varchar 30) Now i need to write a query that would take the start date and time, end date and time, and return all the records between the two. So for example, the query should return all records between 01/01/2002 at 17:00 and 01/30/2002 at 08:00. How can i get that query in one query?? SQL SQL _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to write this query??
hi all, i have a table trans with this structure: today(date) | heure1(time) | amount(double) | client(varchar 30) Now i need to write a query that would take the start date and time, end date and time, and return all the records between the two. So for example, the query should return all records between 01/01/2002 at 17:00 and 01/30/2002 at 08:00. How can i get that query in one query?? SQL SQL _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to write this query??
hi all, i have a table trans with this structure: today(date) | heure1(time) | amount(double) | client(varchar 30) Now i need to write a query that would take the start date and time, end date and time, and return all the records between the two. So for example, the query should return all records between 01/01/2002 at 17:00 and 01/30/2002 at 08:00. How can i get that query in one query?? SQL SQL _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
