Re: how to select the record with one sql statement?

[shawn l.green]

Hello sea,

On 8/13/2018 7:01 PM, sea wrote:

helle,
I have a table, like this:
  pigId  dayweigt
   pig1  2018-1-121
   pig2  2018-1-131
   pig3  2018-1-141
   pig1  2018-1-222
   pig2  2018-1-231
   pig3  2018-1-240
   pig1  2018-1-323  pig2  2018-1-330
   pig3  2018-1-341
   .

only the pig1'weight increase continuously for 3 days.   Giving the input: 
num_of_day(weight increasing continuously for num_of_day);   expecting the 
output: certain_day, pigId;from certain_day,  pigId'weight increasing 
continuously for num_of_day.  How to select the records in one sql 
statement?
 thanks



I've thought about this a bit (since your question appeared on the list) 
and I break down the tasks you need to perform in my head like this. 
(Others on the list may have different ways to approach the same problem)


task 1 - For each bucket, a pigId value, assemble an ordered list (not a 
set) of each weight sorted by time.  (not hard)


task 2 - Within each ordered list, compare the values of every 
consecutive pair.  (several ways to do this)


task 3 - Iterate over those "consecutive value differences" generated in 
task 2 looking for the longest sequence of positive non-zero values for 
each pigId.  (this is not really a set-oriented process so normal SELECT 
or GROUP BY command patterns will not handle it with any efficency)


I'm afraid that attempting all of that sequencing and iteration using 
just a single set-based SQL command is not going to be practical. Using 
one or more cursors within a stored procedure is your best bet for this 
type of sequential trend analysis.


I could easily imagine the first step as a INSERT...SELECT...ORDER BY... 
command going to a new table with an autoincrement column on it (to 
provide a global sequence number across all of your individual pigId 
values) . The second step could do a self join to that table where the 
ON clause could look like

  a.pigId = b.pigID AND a.seq-1 = b.seq

But at that point, counting the length of sequences (and remembering 
when each trend became positive) needs a loop. That's where even 
complicated set-wise SQL fails you and you need to shift into using the 
SQL of stored programs.



Regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

how to select the record with one sql statement?

[sea]

helle,
   I have a table, like this:
 pigId  dayweigt
  pig1  2018-1-121
  pig2  2018-1-131
  pig3  2018-1-141
  pig1  2018-1-222
  pig2  2018-1-231
  pig3  2018-1-240
  pig1  2018-1-323  pig2  2018-1-330
  pig3  2018-1-341
  .

   only the pig1'weight increase continuously for 3 days.   Giving the input: 
num_of_day(weight increasing continuously for num_of_day);   expecting the 
output: certain_day, pigId;from certain_day,  pigId'weight increasing 
continuously for num_of_day.  How to select the records in one sql 
statement?
thanks

behavior and documents conflict for SELECT LAST_INSERT_ID()

[Chenxi Li]

Dear friends,

1. https://bugs.mysql.com/bug.php?id=78934

2.
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

The document (2) says that LAST_INSERT_ID() will not be changed if no rows
are inserted  successfully. But (1) says that it's undefined when no rows
are inserted successfully, which is in real case.

What is the problem? the document or the code?

Best Regards,
Chenxi Li

RE: Select Earliest Related Row

[Pavel Zimahorau]

Select class_name, 
GROUP_CONCAT(DISTINCT cl_date
  ORDER BY cl_date DESC SEPARATOR ',  ')
 (select min(cl_date) from CLASS_DATES where item_id = c.item_id and 
cl_date > Now())
From CLASSES c
Join CLASS_DATES cd on  (c.item_id = cd.item_id) 
Group by class_name, c. item_id

I did not check it in DB and it can have some parse errors. 

But It should work. 

Best Regards,
Pavel Zimahorau

-Original Message-
From: Don Wieland [mailto:d...@pointmade.net] 
Sent: Tuesday, February 09, 2016 6:57 PM
To: MySql <mysql@lists.mysql.com>
Subject: Select Earliest Related Row

I have a two tables where I am adding CLASSES and CLASS_DATES for people to 
register for.

Table Name = tl_items (Parent)
item_id
class_name


table_name = tl_items_classes (Children)
class_date_id
item_id
cl_date

“tl_items" will have 0 to Many “tl_items_classes" using the “item_id” as the 
KEY field.

I am compiling a SELECT query to search the dates of the classes 
(tl_items_classes rows), but I need to these two things:

1) Only show the EARLIEST “cl_date" in my returned list (i.e... if a class has 
dates for Jan 2,3,4,5,6 2016, only show Jan 2’s row)

2) In that displayed EARLIEST Class Date row, have a column that displays the 
complete list of related class dates in ASC order delineated by a COMMA 
(Group_Concat()???).

Here is a query I have started off with which show all the dates fine. Just 
want to fine tune it.

SELECT ic.*, i.*, DATE_FORMAT(ic.cl_date, "%M %Y") AS mo_label FROM 
tl_items_classes ic 
LEFT JOIN tl_items i ON ic.item_id = i.item_id 
WHERE i.active = 1 AND ic.cl_cancelled IS NULL ORDER BY ic.cl_date ASC;

Any help would be appreciated.

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Select Earliest Related Row

[Don Wieland]

I have a two tables where I am adding CLASSES and CLASS_DATES for people to 
register for.

Table Name = tl_items (Parent)
item_id
class_name


table_name = tl_items_classes (Children)
class_date_id
item_id
cl_date

“tl_items" will have 0 to Many “tl_items_classes" using the “item_id” as the 
KEY field.

I am compiling a SELECT query to search the dates of the classes 
(tl_items_classes rows), but I need to these two things:

1) Only show the EARLIEST “cl_date" in my returned list (i.e... if a class has 
dates for Jan 2,3,4,5,6 2016, only show Jan 2’s row)

2) In that displayed EARLIEST Class Date row, have a column that displays the 
complete list of related class dates in ASC order delineated by a COMMA 
(Group_Concat()???).

Here is a query I have started off with which show all the dates fine. Just 
want to fine tune it.

SELECT ic.*, i.*, DATE_FORMAT(ic.cl_date, "%M %Y") AS mo_label FROM 
tl_items_classes ic 
LEFT JOIN tl_items i ON ic.item_id = i.item_id 
WHERE i.active = 1 AND ic.cl_cancelled IS NULL ORDER BY ic.cl_date ASC;

Any help would be appreciated.

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band

select contiguous addresses that start on a bit boundary

[John Stile]

I need help creating a select that returns 4 records that have
contiguous addresses that start on a bit boundary.

If 4 do not exist, I need a return of zero records.

I would like to do this in one statement and I do not have ownership of
this mysql server, so fancy views, temporary tables, indexing, etc are
outside my permission level.

I am also not the only consumer of this database, so altering it for my
needs could hurt the other consumers.

Below I specify the issue and where I am.

Thank you for your attention.

#
# Create problem set
# - This has non-contiguous addresses
# - This has one status not 0
# - This has contiguous addresses that start before the bit boundary
#
CREATE TABLE addresses ( address BIGINT(20), status INT );
INSERT INTO addresses
VALUES (1001,0),
   (1003,0),
   (1004,0),
   (1005,1),
   (1006,0),
   (1007,0),
   (1008,0),
   (1009,0),
   (1010,0),
   (1011,0),
   (1013,0),
   (1014,0),
   (1015,0),
   (1016,0),
   (1017,0);
#
# This shows the bit boundary, where the start is  (address & 3) = 0
#
select address, (address & 3) as boundary from addresses where address
>0 and status=0 order by address limit 10  ;
+--+--+
| address  | boundary |
+--+--+
| 1001 |1 |
| 1003 |3 |
| 1004 |0 |
| 1006 |2 |
| 1007 |3 |
| 1008 |0 |
| 1009 |1 |
| 1010 |2 |
| 1011 |3 |
| 1013 |1 |
+--+--+
10 rows in set (0.00 sec)
#
# This shows contiguous add, but they do not stat on the bit  boundary
#
select c1.address, (address & 3) as boundary  from addresses c1 where 4
= ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
BETWEEN c1.address AND (c1.address + 3)  ) limit 10;

+--+--+
| address  | boundary |
+--+--+
| 1006 |2 |
| 1007 |3 |
| 1008 |0 |
| 1013 |1 |
| 1014 |2 |
+--+--+
5 rows in set (0.00 sec)



I can't seem to add my ((address & 3) = 0) condition to the correct location to 
get the desired
result. I don't understand how I can use c1.address in the BETWEEN, and
yet I can't seem to make ((address & 3) = 0) work anywhere.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: select contiguous addresses that start on a bit boundary

[John Stile]

I should have said consecutive addresses, rather than contiguous.
I care about a set of consecutive addresses, and there is no guarantee
of record order.

On 12/17/2015 07:35 AM, John Stile wrote:
> I need help creating a select that returns 4 records that have
> contiguous addresses that start on a bit boundary.
>
> If 4 do not exist, I need a return of zero records.
>
> I would like to do this in one statement and I do not have ownership of
> this mysql server, so fancy views, temporary tables, indexing, etc are
> outside my permission level.
>
> I am also not the only consumer of this database, so altering it for my
> needs could hurt the other consumers.
>
> Below I specify the issue and where I am.
>
> Thank you for your attention.
>
> #
> # Create problem set
> # - This has non-contiguous addresses
> # - This has one status not 0
> # - This has contiguous addresses that start before the bit boundary
> #
> CREATE TABLE addresses ( address BIGINT(20), status INT );
> INSERT INTO addresses
> VALUES (1001,0),
>(1003,0),
>(1004,0),
>(1005,1),
>(1006,0),
>(1007,0),
>(1008,0),
>(1009,0),
>(1010,0),
>(1011,0),
>(1013,0),
>(1014,0),
>(1015,0),
>(1016,0),
>(1017,0);
> #
> # This shows the bit boundary, where the start is  (address & 3) = 0
> #
> select address, (address & 3) as boundary from addresses where address
>> 0 and status=0 order by address limit 10  ;
> +--+--+
> | address  | boundary |
> +--+--+
> | 1001 |1 |
> | 1003 |3 |
> | 1004 |0 |
> | 1006 |2 |
> | 1007 |3 |
> | 1008 |0 |
> | 1009 |1 |
> | 1010 |2 |
> | 1011 |3 |
> | 1013 |1 |
> +--+--+
> 10 rows in set (0.00 sec)
> #
> # This shows contiguous add, but they do not stat on the bit  boundary
> #
> select c1.address, (address & 3) as boundary  from addresses c1 where 4
> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
> BETWEEN c1.address AND (c1.address + 3)  ) limit 10;
>
> +--+--+
> | address  | boundary |
> +--+--+
> | 1006 |2 |
> | 1007 |3 |
> | 1008 |0 |
> | 1013 |1 |
> | 1014 |2 |
> +--+--+
> 5 rows in set (0.00 sec)
>
>
>
> I can't seem to add my ((address & 3) = 0) condition to the correct location 
> to get the desired
> result. I don't understand how I can use c1.address in the BETWEEN, and
> yet I can't seem to make ((address & 3) = 0) work anywhere.
>
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: select contiguous addresses that start on a bit boundary

[John Stile]

I have a solution.

SELECT start_bit_boundary FROM (
  SELECT
min(address) as start_bit_boundary,
status, count(*) as CT
  FROM MAC_addresses
  WHERE status = 0
  GROUP BY address >> 2
) AS _INNER
WHERE
  _INNER.CT = 4
ORDER BY start_bit_boundary
LIMIT 0,1;

It returns the first of 4 consecutive addresses.
This works with the following data set.

CREATE TABLE addresses ( address BIGINT(20), status INT );
INSERT INTO addresses
VALUES (1001,0),
   (1003,0),
   (1004,0),
   (1005,1),
   (1006,0),
   (1007,0),
   (1009,0),
   (1010,0),
   (1011,0),
   (1013,0),
   (1008,0),
   (1014,0),
   (1015,0),
   (1016,0),
   (1017,0);

If I want to print all the addresses I could do this:

select * from addresses
where status = 0
AND  address BETWEEN (
  SELECT @b := start_bit_boundary FROM (
SELECT min(address) as start_bit_boundary,status,count(*) as CT
FROM MAC_addresses
WHERE status = 0
GROUP BY address >> 2
  ) AS _INNER
  WHERE _INNER.CT = 4
  ORDER BY start_bit_boundary
  LIMIT 0,1
) AND (@b+3) limit 0,4;

On 12/17/2015 08:14 AM, John Stile wrote:
> I should have said consecutive addresses, rather than contiguous.
> I care about a set of consecutive addresses, and there is no guarantee
> of record order.
>
> On 12/17/2015 07:35 AM, John Stile wrote:
>> I need help creating a select that returns 4 records that have
>> contiguous addresses that start on a bit boundary.
>>
>> If 4 do not exist, I need a return of zero records.
>>
>> I would like to do this in one statement and I do not have ownership of
>> this mysql server, so fancy views, temporary tables, indexing, etc are
>> outside my permission level.
>>
>> I am also not the only consumer of this database, so altering it for my
>> needs could hurt the other consumers.
>>
>> Below I specify the issue and where I am.
>>
>> Thank you for your attention.
>>
>> #
>> # Create problem set
>> # - This has non-contiguous addresses
>> # - This has one status not 0
>> # - This has contiguous addresses that start before the bit boundary
>> #
>> CREATE TABLE addresses ( address BIGINT(20), status INT );
>> INSERT INTO addresses
>> VALUES (1001,0),
>>(1003,0),
>>(1004,0),
>>(1005,1),
>>(1006,0),
>>(1007,0),
>>(1008,0),
>>(1009,0),
>>(1010,0),
>>(1011,0),
>>(1013,0),
>>(1014,0),
>>(1015,0),
>>(1016,0),
>>(1017,0);
>> #
>> # This shows the bit boundary, where the start is  (address & 3) = 0
>> #
>> select address, (address & 3) as boundary from addresses where address
>>> 0 and status=0 order by address limit 10  ;
>> +--+--+
>> | address  | boundary |
>> +--+--+
>> | 1001 |1 |
>> | 1003 |3 |
>> | 1004 |0 |
>> | 1006 |    2 |
>> | 1007 |3 |
>> | 1008 |    0 |
>> | 1009 |1 |
>> | 1010 |2 |
>> | 1011 |3 |
>> | 1013 |1 |
>> +--+--+
>> 10 rows in set (0.00 sec)
>> #
>> # This shows contiguous add, but they do not stat on the bit  boundary
>> #
>> select c1.address, (address & 3) as boundary  from addresses c1 where 4
>> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
>> BETWEEN c1.address AND (c1.address + 3)  ) limit 10;
>>
>> +--+--+
>> | address  | boundary |
>> +--+--+
>> | 1006 |2 |
>> | 1007 |3 |
>> | 1008 |0 |
>> | 1013 |1 |
>> | 1014 |2 |
>> +--+--+
>> 5 rows in set (0.00 sec)
>>
>>
>>
>> I can't seem to add my ((address & 3) = 0) condition to the correct location 
>> to get the desired
>> result. I don't understand how I can use c1.address in the BETWEEN, and
>> yet I can't seem to make ((address & 3) = 0) work anywhere.
>>
>>
>>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Lucio Chiappetti]

On Wed, 29 Apr 2015, Olivier Nicole wrote:


I have a table where each record is made of one item_number and one
data_value.


You do not have any other column ? In particular you do not have any 
unique key record identifier ? All my tables have a column with a record 
sequence number seq int NOT NULL AUTO_INCREMENT which is also a key
KEY auxiliary(seq). This is useful a posteriori to locate particular 
records.



What is the command to select all the records where an item_number has
the data 1 but not the data 2?


1) by select you mean display at the terminal using the mysql line mode
   client, or locate all affected records for further work ?

2) am I getting it correctly that you want to locate all the cases where
   a given item_number (any) has JUST ONE occurrence in the table ?

In the line mode client this can be easily done with an additional table, 
which can be a temporary table.


Consider e.g. the following table (it has two columns, no seq column, and
nothing else ... actually it is a table of seq pointers in two other 
tables)


 select * from north33w1t7_ 

| north33 | w1t7 |
+-+--+
|  21 |1 |
|  21 |2 |
|  24 |   20 |

create temporary table temp1
select north33,count(*) as c from north33w1t7_
group by north33 order by north33;

temp1 will contain something like this

| north33 | c |
+-+---+
|  21 | 2 |
|  24 | 1 |

so it will tell you that item 21 has 2 counteparts, while item 24 
has 1 counterpart.


If you want to select (display) all cases in the main table with 1 
counterpart do


select north33w1t7_.*
from temp1 join north33w1t7_  on 
temp1.north33=north33w1t7_.north33

where c=1 :

| north33 | w1t7 |
+-+--+
|  24 |   20 |
|  200013 |   93 |


A different story would be if you want always to extract ONE record from 
the main table, the single one if c=1, and the FIRST one if c1.


What you define first it is up to you (the smallest data_value, the 
highest data_value, a condition on other columns).


Here in general I use a trick which involves one or two temporary tables 
and a variable.  I initialize the variable to zero (or a value which is 
not represented in the table, which shall be ordered on the columns as you 
need. Then I test whether the item_number is the same as the variable, if 
not I declare it to be first, then reset the variable in the same select 
statement.


 set @x:=0;
 select north33w1t7_.*,
 if(@xnorth33w1t7_.north33,'FIRST','no') as flag,
 @x:=north33w1t7_.north33
 from temp1 join north33w1t7_  on temp1.north33=north33w1t7_.north33
 where c1 order by north33,w1t

| north33 | w1t7 | flag  | @x:=north33w1t7_.north33 |
+-+--+---+--+
|  21 |1 | FIRST |   21 |
|  21 |2 | no|   21 |
|  22 |8 | FIRST |   22 |
|  22 |9 | no|   22 |

I can then save this select to a temporary table, and take my pick where 
flag='FIRST'.



of course you can also do without the join with temp1 if you want
either the single or the first (i.e. c=1 and c1)

select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from 
north33w1t7_  order by north33,w1t7


| north33 | w1t7 | flag  | @x:=north33 |
+-+--+---+-+
|  21 |1 | FIRST |  21 |
|  21 |2 | no|  21 |
|  22 |8 | FIRST |  22 |
|  22 |9 | no|  22 |
|  24 |   20 | FIRST |  24 |


--

Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html

Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Mogens Melander]

Right,

Take a look at this one then:

insert into test(item_number,data_value)
values(1,1),(1,2),(1,3)
,(2,1),(2,3)
,(3,1),(3,2),(3,3)
,(4,1),(4,3);

SELECT * FROM test
WHERE item_number in (SELECT item_number FROM test where data_value=1)
AND item_number not in (SELECT item_number FROM test where data_value = 2);

Result:
2, 1
2, 3
4, 1
4, 3

On Wed, April 29, 2015 07:20, Olivier Nicole wrote:
 SELECT * FROM table WHERE item_number=1;

 Sorry if my question was not clear: what I am looking for is:

 SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
 the same item_number and data_value=2

 Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier


-- 
Mogens Melander
+66 8701 33224


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

AW: Select one valuebut not the other

[Axel Diehl | GIP]

Simply translated:

select *  from table t1
where t1.data_value=1 
AND not exists(select * from table t2 
   where t2.data_value=2
   and   t2.item_number = t1.item_number)



Axel Diehl
__
GIP Exyr GmbH
Hechtsheimer Str. 35-37 | 55131 Mainz 

Tel: +49 (0) 6131 / 80124 - 46 | Fax: +49 (0) 6131 / 80124 - 24
E-Mail: axel.di...@gip.com | Web: www.gip.com http://www.gip.com/  

Geschäftsführer: Dr. Bernd Reifenhäuser, Dr. Alexander Ebbes
Handelsregister: HRB 6870 - Amtsgericht Mainz 

 


-Ursprüngliche Nachricht-
Von: Olivier Nicole [mailto:olivier.nic...@cs.ait.ac.th] 
Gesendet: Mittwoch, 29. April 2015 07:21
An: mog...@fumlersoft.dk
Cc: mysql@lists.mysql.com
Betreff: Re: Select one valuebut not the other

 SELECT * FROM table WHERE item_number=1;

Sorry if my question was not clear: what I am looking for is:

SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2

Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a 
 newbie question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one 
 data_value.

 If one item has several values, there are several records with the 
 same item_number and a different data_value.

 What is the command to select all the records where an item_number 
 has the data 1 but not the data 2?

 Thanks in advance,

 Olivier

 --
 Mogens Melander
 +66 8701 33224


 --
 This message has been scanned for viruses and dangerous content by 
 MailScanner, and is believed to be clean.

-- 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: AW: Select one valuebut not the other

[Olivier Nicole]

Axel,

 Simply translated:

 select *  from table t1
 where t1.data_value=1 
 AND not exists(select * from table t2 
where t2.data_value=2
and   t2.item_number = t1.item_number)

Yes, but with t1 and t2 the same table.

best regards,

Olivier




 Axel Diehl
 __
 GIP Exyr GmbH
 Hechtsheimer Str. 35-37 | 55131 Mainz 

 Tel: +49 (0) 6131 / 80124 - 46 | Fax: +49 (0) 6131 / 80124 - 24
 E-Mail: axel.di...@gip.com | Web: www.gip.com http://www.gip.com/  

 Geschäftsführer: Dr. Bernd Reifenhäuser, Dr. Alexander Ebbes
 Handelsregister: HRB 6870 - Amtsgericht Mainz 

  


 -Ursprüngliche Nachricht-
 Von: Olivier Nicole [mailto:olivier.nic...@cs.ait.ac.th] 
 Gesendet: Mittwoch, 29. April 2015 07:21
 An: mog...@fumlersoft.dk
 Cc: mysql@lists.mysql.com
 Betreff: Re: Select one valuebut not the other

 SELECT * FROM table WHERE item_number=1;

 Sorry if my question was not clear: what I am looking for is:

 SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
 the same item_number and data_value=2

 Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a 
 newbie question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one 
 data_value.

 If one item has several values, there are several records with the 
 same item_number and a different data_value.

 What is the command to select all the records where an item_number 
 has the data 1 but not the data 2?

 Thanks in advance,

 Olivier

 --
 Mogens Melander
 +66 8701 33224


 --
 This message has been scanned for viruses and dangerous content by 
 MailScanner, and is believed to be clean.

-- 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

Thank you, 

 SELECT * FROM test
 WHERE item_number in (SELECT item_number FROM test where data_value=1)
 AND item_number not in (SELECT item_number FROM test where data_value = 2);

That did it.

Olivier


 On Wed, April 29, 2015 07:20, Olivier Nicole wrote:
 SELECT * FROM table WHERE item_number=1;

 Sorry if my question was not clear: what I am looking for is:

 SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
 the same item_number and data_value=2

 Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier


 -- 
 Mogens Melander
 +66 8701 33224


 -- 
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

Lucio, 

 I have a table where each record is made of one item_number and one
 data_value.
 You do not have any other column ? In particular you do not have any 
 unique key record identifier ? All my tables have a column with a record 
 sequence number seq int NOT NULL AUTO_INCREMENT which is also a key
 KEY auxiliary(seq). This is useful a posteriori to locate particular 
 records.

I do, but that was irrelevant to my question, as it is only counting the
records, it carries no information.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?
 1) by select you mean display at the terminal using the mysql line mode
 client, or locate all affected records for further work ?

I meant SELECT command, so a display I guess (but that would be the same
select in Perl).

 2) am I getting it correctly that you want to locate all the cases where
 a given item_number (any) has JUST ONE occurrence in the table ?

 In the line mode client this can be easily done with an additional table, 
 which can be a temporary table.

My idea was to do it in one single command, without using additional
table.

I ended up with something along the line of:

select handle, text_value from metadatavalue, handle where 
item_id in (select item_id from metadatavalue where metadata_field_id=64)
and item_id not in (select item_id from metadatavalue 
where metadata_field_id=27) 
and metadata_field_id=64 
and handle.resource_id=item_id 
and resource_type_id=2
order by item_id;

Maybe not the fastest nor the nicest, but as I need to run it only once,
it is enought.

Thank you,

Olivier

 Consider e.g. the following table (it has two columns, no seq column, and
 nothing else ... actually it is a table of seq pointers in two other 
 tables)

   select * from north33w1t7_ 

 | north33 | w1t7 |
 +-+--+
 |  21 |1 |
 |  21 |2 |
 |  24 |   20 |

 create temporary table temp1
 select north33,count(*) as c from north33w1t7_
 group by north33 order by north33;

 temp1 will contain something like this

 | north33 | c |
 +-+---+
 |  21 | 2 |
 |  24 | 1 |

 so it will tell you that item 21 has 2 counteparts, while item 24 
 has 1 counterpart.

 If you want to select (display) all cases in the main table with 1 
 counterpart do

 select north33w1t7_.*
 from temp1 join north33w1t7_  on 
 temp1.north33=north33w1t7_.north33
 where c=1 :

 | north33 | w1t7 |
 +-+--+
 |  24 |   20 |
 |  200013 |   93 |


 A different story would be if you want always to extract ONE record from 
 the main table, the single one if c=1, and the FIRST one if c1.

 What you define first it is up to you (the smallest data_value, the 
 highest data_value, a condition on other columns).

 Here in general I use a trick which involves one or two temporary tables 
 and a variable.  I initialize the variable to zero (or a value which is 
 not represented in the table, which shall be ordered on the columns as you 
 need. Then I test whether the item_number is the same as the variable, if 
 not I declare it to be first, then reset the variable in the same select 
 statement.

   set @x:=0;
   select north33w1t7_.*,
   if(@xnorth33w1t7_.north33,'FIRST','no') as flag,
   @x:=north33w1t7_.north33
   from temp1 join north33w1t7_  on temp1.north33=north33w1t7_.north33
   where c1 order by north33,w1t

 | north33 | w1t7 | flag  | @x:=north33w1t7_.north33 |
 +-+--+---+--+
 |  21 |1 | FIRST |   21 |
 |  21 |2 | no|   21 |
 |  22 |8 | FIRST |   22 |
 |  22 |9 | no|   22 |

 I can then save this select to a temporary table, and take my pick where 
 flag='FIRST'.


 of course you can also do without the join with temp1 if you want
 either the single or the first (i.e. c=1 and c1)

 select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from 
 north33w1t7_  order by north33,w1t7

 | north33 | w1t7 | flag  | @x:=north33 |
 +-+--+---+-+
 |  21 |1 | FIRST |  21 |
 |  21 |2 | no|  21 |
 |  22 |8 | FIRST |  22 |
 |  22 |9 | no|  22 |
 |  24 |   20 | FIRST |  24 |


 -- 
 
 Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
 For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
 
 Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Select one value, not the other

[Peter Brawley]

On 2015-04-29 12:20 AM, Olivier Nicole wrote:

SELECT * FROM table WHERE item_number=1;

Sorry if my question was not clear: what I am looking for is:

SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2


Assuming a table named t ...

One way, an exclusion join:

select a.*
from t a
left join t b on a.item_number=b.item_number and b.data_value=2
where a.data_value=1 and b.item_number is null;

Another way, with a semi-join:

select a.*
from t a
where a.data_value=1
  and not exists (select data_value from t b where 
b.item_number=a.item_number and data_value=2);


PB




Olivier


On Wed, April 29, 2015 06:30, Olivier Nicole wrote:

Hi,

I am sure that it is feasible with MySQl, and I am sure that's a newbie
question, but my SQL skills are limited...

I have a table where each record is made of one item_number and one
data_value.

If one item has several values, there are several records with the same
item_number and a different data_value.

What is the command to select all the records where an item_number has
the data 1 but not the data 2?

Thanks in advance,

Olivier

--
Mogens Melander
+66 8701 33224


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Select one valuebut not the other

[Olivier Nicole]

Hi,

I am sure that it is feasible with MySQl, and I am sure that's a newbie
question, but my SQL skills are limited...

I have a table where each record is made of one item_number and one
data_value.

If one item has several values, there are several records with the same
item_number and a different data_value.

What is the command to select all the records where an item_number has
the data 1 but not the data 2?

Thanks in advance,

Olivier
-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Mogens Melander]

SELECT * FROM table WHERE item_number=1;

On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier

-- 
Mogens Melander
+66 8701 33224


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

 SELECT * FROM table WHERE item_number=1;

Sorry if my question was not clear: what I am looking for is:

SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2

Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier

 -- 
 Mogens Melander
 +66 8701 33224


 -- 
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Narrowing a SELECT statement by multiple hits

[Jennifer]

On Feb 12, 2014, at 6:30 PM, Larry Martell wrote:
 Try adding a having clause, e.g.:
 
 SELECT `ip`,`page`,`url`,`time_stamp`
 FROM `ip_addresses`
 WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND 
 CURDATE() - INTERVAL 1 SECOND)
 AND TRIM(`referrer`) LIKE ''
 HAVING COUNT(ip) 1
 ORDER BY INET_ATON(`ip`), `time_stamp`

Thank you Larry for the response.  Unfortunately, I can't get it to 
work.  The code above only returns one row.  It should return 35 rows.  If I 
remove the HAVING COUNT line, 52 rows are returned.

If I add COUNT(`ip`) AS UniqueIPs to the SELECT line, that shows that 
there are 52 records, but still only returns one row.  So I added GROUP BY `ip` 
and that gave me 7 rows with counts that added up to 35.  Closer, but each row 
was a group of IP addresses where there was more than one hit.  

I want each hit to be returned, not a summary of hits per IP, so I 
don't think GROUP BY is what I need(?).  I've run across a couple of sites that 
seem to say that an INNER JOIN would give me what I want.  If that's true, then 
that's above my head.

BTW, this on MySQL 5.5.34-cll-lve

Thank you,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Wire LAN Shelving:
http://www.superiorshelving.com/mfg/nexel/pages/wire-shelving-chrome.php


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Narrowing a SELECT statement by multiple hits

[Roy Lyseng]

Hi Jennifer,

please try filtering with a subquery that locates ip addresses with more than 1 
attempt:


SELECT ip, page, url, time_stamp
FROM ip_adresses
WHERE existing where clause AND
  ip IN (SELECT ip
 FROM ip_addresses
 WHERE existing where clause
 GROUP BY ip
 HAVING COUNT(*)  1
)

I think this will be more performant on version 5.6, though. If this is too 
slow, you may try materializing the subquery in a temporary table and use that 
table instead of the subquery.


Thanks,
Roy

On 17.02.14 17:42, Jennifer wrote:

On Feb 12, 2014, at 6:30 PM, Larry Martell wrote:

Try adding a having clause, e.g.:

SELECT `ip`,`page`,`url`,`time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND 
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
HAVING COUNT(ip) 1
ORDER BY INET_ATON(`ip`), `time_stamp`


Thank you Larry for the response.  Unfortunately, I can't get it to 
work.  The code above only returns one row.  It should return 35 rows.  If I 
remove the HAVING COUNT line, 52 rows are returned.

If I add COUNT(`ip`) AS UniqueIPs to the SELECT line, that shows that 
there are 52 records, but still only returns one row.  So I added GROUP BY `ip` 
and that gave me 7 rows with counts that added up to 35.  Closer, but each row 
was a group of IP addresses where there was more than one hit.

I want each hit to be returned, not a summary of hits per IP, so I 
don't think GROUP BY is what I need(?).  I've run across a couple of sites that seem to 
say that an INNER JOIN would give me what I want.  If that's true, then that's above my 
head.

BTW, this on MySQL 5.5.34-cll-lve

Thank you,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Wire LAN Shelving:
http://www.superiorshelving.com/mfg/nexel/pages/wire-shelving-chrome.php





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Narrowing a SELECT statement by multiple hits

[Jennifer]

On Feb 17, 2014, at 10:17 AM, Roy Lyseng wrote:
 please try filtering with a subquery that locates ip addresses with more than 
 1 attempt:

Hi Roy,

That did it!  Thank you so much!!!  Now I just need to study that IN 
clause to see what's going on there.

 If this is too slow, you may try materializing the subquery in a temporary 
 table and use that table instead of the subquery.

Yes, it is slow.  It takes about 15 seconds to finish, but since it's 
run once a day via cron to email me a report, I don't think it's too much of a 
problem.  However, I did try to see if I could do it, but there's an error 
somewhere in the SQL.  What am I doing wrong?

CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
GROUP BY `ip`
HAVING COUNT(*)  1
   );
SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE '' AND
`ip` IN (temp_ip)
ORDER BY INET_ATON(`ip`), `time_stamp`;

Thank you again,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Workstation Chairs/Stools:
http://www.superiorshelving.com/mfg/nexel/pages/stools.php


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Narrowing a SELECT statement by multiple hits

[Roy Lyseng]

Hi Jennifer,

great that it worked.

Try replacing the line

  `ip` IN (temp_ip)

with

  `ip` IN (SELECT ip FROM temp_ip)

Each subquery needs to be a complete SELECT query.

Thanks,
Roy

On 17.02.14 21:11, Jennifer wrote:

On Feb 17, 2014, at 10:17 AM, Roy Lyseng wrote:

please try filtering with a subquery that locates ip addresses with more than 1 
attempt:


Hi Roy,

That did it!  Thank you so much!!!  Now I just need to study that IN 
clause to see what's going on there.


If this is too slow, you may try materializing the subquery in a temporary 
table and use that table instead of the subquery.


Yes, it is slow.  It takes about 15 seconds to finish, but since it's 
run once a day via cron to email me a report, I don't think it's too much of a 
problem.  However, I did try to see if I could do it, but there's an error 
somewhere in the SQL.  What am I doing wrong?

CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
 FROM `ip_addresses`
 WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
 GROUP BY `ip`
 HAVING COUNT(*)  1
);
SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE '' AND
`ip` IN (temp_ip)
ORDER BY INET_ATON(`ip`), `time_stamp`;

Thank you again,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Workstation Chairs/Stools:
http://www.superiorshelving.com/mfg/nexel/pages/stools.php




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Narrowing a SELECT statement by multiple hits

[shawn l.green]

Hi Jennifer,

On 2/17/2014 3:11 PM, Jennifer wrote:

CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
 FROM `ip_addresses`
 WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
 GROUP BY `ip`
 HAVING COUNT(*)  1
);
SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE '' AND
`ip` IN (temp_ip)
ORDER BY INET_ATON(`ip`), `time_stamp`;


You are *so* close!

CREATE TEMPORARY TABLE temp_ip AS
SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) 
AND CURDATE() - INTERVAL 1 SECOND)

AND TRIM(`referrer`) LIKE ''
GROUP BY `ip`
HAVING COUNT(*)  1
;

ALTER TABLE temp_ip ADD KEY(ip);

SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
INNER JOIN tmp_ip
  ON ip_addresses.ip = tmp_ip.ip
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
ORDER BY INET_ATON(`ip`), `time_stamp`;

Give that a whirl and let us know your results.
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Narrowing a SELECT statement by multiple hits

[Jennifer]

On Feb 17, 2014, at 12:36 PM, Roy Lyseng wrote:
 Try... `ip` IN (SELECT ip FROM temp_ip)

Wow!  Only 1 second to return the results now!!  That's 15x faster!!!

 Each subquery needs to be a complete SELECT query.

That's good to know.  I figured that since temp_ip referenced a 
complete SELECT statement from before, that was good enough, but no.  There's 
always something new to learn!

Thanks again,
Jenni

Superior Shelving Systems
The (Storage|Office|Display|Warehouse) Shelving Specialists
Since 1984

Metro Shelving:
http://www.superiorshelving.com/mfg/metro/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Narrowing a SELECT statement by multiple hits

[Jennifer]

Hello,

I have the following SQL statement that I'd like to add to.  It's used 
to create a report that's emailed to me showing hits to our site that didn't 
provide a referrer.  However, I only want to report on multiple hits from the 
same IP address - not just a single hit by someone.  

How can I add a condition to only show hits by someone who's hit the 
site 2 or more times with the same IP?  I tried GROUP BY but that didn't return 
all the hits - one one per IP.

SELECT `ip`,`page`,`url`,`time_stamp`
FROM `ip_addresses` 
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND 
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE '' 
ORDER BY INET_ATON(`ip`), `time_stamp`

I hope I'm explaining this correctly.

Thank you,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Computer Workstations:
http://www.superiorshelving.com/mfg/nexel/pages/lan-workstations-nexel.php


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Narrowing a SELECT statement by multiple hits

[Larry Martell]

On Wed, Feb 12, 2014 at 7:35 PM, Jennifer jenni...@superiorshelving.com wrote:
 Hello,

 I have the following SQL statement that I'd like to add to.  It's 
 used to create a report that's emailed to me showing hits to our site that 
 didn't provide a referrer.  However, I only want to report on multiple hits 
 from the same IP address - not just a single hit by someone.

 How can I add a condition to only show hits by someone who's hit the 
 site 2 or more times with the same IP?  I tried GROUP BY but that didn't 
 return all the hits - one one per IP.

 SELECT `ip`,`page`,`url`,`time_stamp`
 FROM `ip_addresses`
 WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND 
 CURDATE() - INTERVAL 1 SECOND)
 AND TRIM(`referrer`) LIKE ''
 ORDER BY INET_ATON(`ip`), `time_stamp`

 I hope I'm explaining this correctly.

Try adding a having clause, e.g.:

SELECT `ip`,`page`,`url`,`time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
HAVING COUNT(ip) 2
ORDER BY INET_ATON(`ip`), `time_stamp`

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[hsv]

 2013/06/13 23:08 +, Rick James 
FIND_IN_SET might work the cleanest...
WHERE FIND_IN_SET('action', genres) OR/AND [NOT] ...

And have genres look like 'action,drama,foobar', that is comma-separators, and 
no need for leading/trailing comma.
That would also work for genres = '1,3,10,19,38' and FIND_IN_SET('19', genres)

And you seem no fan of named BITs (SET), either. *sigh*


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How do I select all rows of table that have some rows in another table (AND, not OR)

[hsv]

 2013/06/11 12:59 -0700, Daevid Vincent 
Also, just for SG this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while clever is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres  
  
  17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres 
  ---
  11  1|10|19|31|32|59|  

Except that, it seems to me, it somehow reflects the reality of assigning 
attributes to the scenes (movies?) that you catalog. In a way, it looks very 
much like using a bitstring wherin each place stands for one attribute. If you 
then have also a bitstring for each user s likes and one for rows (peeves), 
telling howmany 1s are at the same place for the genres and liking (bit-AND, 
MySQL  followed by telling the number of 1s), and same for the genres and 
the row or peeve yields a number howmany match for liking, and how many match 
for becoming peeved. If the liking is enough greater than the becoming peeved, 
the scene and the user match.

Unhappily, although this, using bitstring for set of attributes to match, is an 
old and well understood topic, MySQL s support for bitstrings is poor, limited 
to integers (as C is so limited)--that is, to 64 bits. If you have more, you 
have to use more words. There is, furthermore, no function for telling 
howmany 1s (or 0s) there are in an integer.

Now, if, in a more perfect world, MySQL had bitstring, and, furthermore, MySQL 
s SET were mapped onto bitstring, where it belongs, you could not only use bit 
operations (MySQL s  | ^), but also name the bits as you like.

The problem with writing one s own bit-telling function is, of course, time, 
and hiding useful information from the optimizer. In any case, here is a 
function for it, using an old well worn trick that depends on binary arithmetic:

delimiter ?
create function bittell(B INTEGER) RETURNS INTEGER
DETERMINISTIC
NO SQL
COMMENT 'Howmany 1s in argument?'
begin
declare E integer;
SET E = 0;
WHILE B  0 DO
set B = (B-1)  B, E = E + 1;
end WHILE;
RETURN E;
end ?
delimiter ;

If you stick with the character-string set, with a slight change in 
representation you can use a simpler-looking pattern--not more efficient, if 
MySQL s implementation is good, but of easier reading: separate the decimal 
numerals with a character that is neither a decimal digit nor a REGEXP 
operator, and bound the whole string with it--comma or semicolon (among others) 
are good.
   ',1,10,19,31,32,59,' REGEXP ',10,+.*,38,' is 0
   ',1,10,19,31,32,59,' REGEXP ',10,+.*,32,' is 1
(See also FIND_IN_SET.)

Somewhere I read that for lack of support bitstring has been withdrawn from the 
SQL standard. This is such an obvious use; why is it not supported?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How do I select all rows of table that have some rows in another table (AND, not OR)

[hsv]

 2013/06/11 12:59 -0700, Daevid Vincent 
Also, just for SG this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while clever is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres  
  
  17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres 
  ---
  11  1|10|19|31|32|59|  

Except that, it seems to me, it somehow reflects the reality of assigning 
attributes to the scenes (movies?) that you catalog. In a way, it looks very 
much like using a bitstring wherin each place stands for one attribute. If, 
say, the bitstring for that which the user gladly picks something is called 
glad, and that for which the user is loath to pick something is called 
loath, an expression for fulfilling all attributes is
(glad  genre) = glad AND (loath  genre) = 0,
with no bit-telling. 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[Rick James]

Thinking out of the box... (And posting my reply at the 'wrong' end of the 
email.)...

Are there fewer than 64 genres?  Use a SET or BIGINT UNSIGNED.

AND sg.`genre_id` IN (10,38)
AND sg.`genre_id` NOT IN (22,61)

--
AND  genre  ((110) | (138)) != 0
AND  genre  ((122) | (161))  = 0

This would avoid having that extra table, and save a lot of space.

If you have more than 64 genres, then 
Plan A: clump them into some kind of application grouping and use multiple 
INTs/SETs.
Plan B: do mod  div arithmetic to compute which genre field to tackle.

For B, something like:
AND  (genre1  (10)) + (genre3  (18)) != 0
AND  (genre2  (12)) + (genre6  (11))  = 0
(That's assuming 10 bits per genre# field.  I would use 32 and INT UNSIGNED.)



 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Tuesday, June 11, 2013 4:17 PM
 To: mysql@lists.mysql.com
 Cc: 'shawn green'
 Subject: RE: How do I select all rows of table that have some rows in
 another table (AND, not OR)
 
 
 
  -Original Message-
  From: shawn green [mailto:shawn.l.gr...@oracle.com]
  Sent: Tuesday, June 11, 2013 2:16 PM
  To: mysql@lists.mysql.com
  Subject: Re: How do I select all rows of table that have some rows in
  another table (AND, not OR)
 
  Hello Daevid,
 
  On 6/11/2013 3:59 PM, Daevid Vincent wrote:
   I am trying to implement a filter so that a user could select
   various
  genres
   they want in or out. Perhaps they like 'action' and 'car chases'
   but don't like 'foreign' and 'drifting' (or whatever...)
  
   So I want something sort of like this, however IN() is using an OR
   comparison when I need it to be an AND
  
   SELECT DISTINCT
s.`scene_id` AS `id`,
GROUP_CONCAT(sg.`genre_id`) FROM
`dvds` AS d
JOIN `scenes_list` AS s
ON s.`dvd_id` = d.`dvd_id`
JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
AND sg.`genre_id` IN (10,38)
AND sg.`genre_id` NOT IN (22,61) GROUP BY
   s.`scene_id`;
  
   This is giving me way way too many rows returned.
  
   For example, I would expect this scene_id to be in the result set:
  
   SELECT * FROM scenes_genres WHERE scene_id = 17;
  
   scene_id  genre_id
     --
  17   1
  17   3
  17  10 --
  17  19
  17  38 --
  17  53
  17  58
  17  59
  
   And this scene ID to NOT be in the result set:
  
   SELECT * FROM scenes_genres WHERE scene_id = 11;
  
   scene_id  genre_id
     --
  11   1
  11  10 --
  11  19
  11  31
  11  32
-- but does not have 38
  11  59
  
   I've tried various subselect ideas, but of course this fails b/c
 genre_id
   can't be multiple things at one time (AND)
  
   JOIN `scenes_genres` AS sg
 ON sg.`scene_id` = s.`scene_id`
AND sg.`genre_id` IN (
  SELECT `genre_id` FROM `scenes_genres`
   WHERE `genre_id` = 10
  AND `genre_id` = 38
  AND `genre_id`  22
  AND `genre_id`  61
   )
  
   And straight up like this failure too...
  
  JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
   AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
   AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)
  
   So I'm sort of out of ideas at this point and hoping someone has a
   way
 to
  do
   this.
  
   Also, just for SG this is how we are currently implementing it, but
   we
  feel
   the REGEXP is killing our queries and while clever is a bit hacky
   and nullifies any indexes we have on the genres column as it
   requires a file_sort table scan to compare substrings basically...
  
   SELECT * FROM scene_all_genres WHERE scene_id = 17;
  
   scene_id  genres
     
  17  1|3|10|19|38|53|58|59|
  
   SELECT * FROM scene_all_genres WHERE scene_id = 11;
  
   scene_id  genres
     ---
  11  1|10|19|31|32|59|
  
   SELECT DISTINCT
s.`scene_id` AS `id`,
sg.`genres`
   FROM
`scene_all_genres` AS sg,
`dvds` AS d,
`scenes_list` AS s
   WHERE  dvd_id` = d.`dvd_id`
AND sg.`scene_id` = s.`scene_id`
AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]'
AND sg.`genres` NOT REGEXP
  '(([[::]]22[[::]])|([[::]]61[[::]]))'
  
   ;
  
   http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp
  
   [[::]], [[::]]
  
   These markers stand for word boundaries. They match the beginning
   and
 end
  of
   words, respectively. A word is a sequence of word characters

Re: How do I select all rows of table that have some rows in another table (AND, not OR)

[shawn green]

Hello Daevid,

On 6/11/2013 7:17 PM, Daevid Vincent wrote:




-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows in
another table (AND, not OR)

Hello Daevid,

... snip ...


Shawn, thank you for taking the time to reply. I wasn't expecting the
solution to be so much work with multiple statements like that. I was
thinking it could be done in one (or two, as in split out a portion of it in
PHP and re-insert it to the original SQL to avoid a JOIN or something). Part
of the issue is that we use PHP to generate the $sql string by appending
bits and pieces depending on the search criteria thereby keeping the 'path'
through the SQL statement simple and relatively linear. To implement this
would require significant re-writing and/or special cases where we could
introduce errors or omissions in the future. The frustrating part is that
the REGEXP query we use now only takes about 2 seconds on my DEV VM (same
database as PROD), however when the RDBMS is loaded it then takes up to 30
seconds so in theory it's not even that inefficient given the # rows. We do
use memcached for the results, but since there are so many combinations a
user could choose, our hit ratio is not so great and therefore the cache
isn't doing us much good and this is why the RDBMS can get loaded up easily.

How can an OR be so simple using IN() but AND be so overly complex?
Seems that mysql should have another function for ALL() that works just like
IN() to handle this kind of scenario.




As I said, we could have used a single command but you would have not 
been able to review the 'best' match scenario only those rows that were 
'complete' matches.


And, those three commands can easily be encapsulated within a stored 
procedure. Pass in two strings (one listing the values to find, one 
listing the values to reject) and use PREPARED STATEMENTS within the 
procedure to build the IN() lists within the first and second commands. 
 The value in the 'HAVING' clause in the last command (unless you use 
the other option of reviewing the list of 'closest' matches) can be set 
to the number of items in the list of things to find parameter to your 
procedure.


I was demonstrating a principle you could use and not necessarily giving 
you a full solution.  Some tweaking may be required.


Also,  by encapsulating what I wrote within a stored procedure, this 
changes my 3 statements to a single call that you can easily configure 
from your PHP application.


Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[Daevid Vincent]

This idea is so fancy pants and clever I *wish* it could have worked for me.
I checked and we actually have 65 genres currently (with more to come I'm
sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some
bitmasks and this solution is so elegant. It's unfortunate there isn't a way
to use more than 64-bits natively.

You're RICK JAMES Bitch! :-p   (please tell me you know the Dave Chappelles
skit I'm referring to)

 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Wednesday, June 12, 2013 9:39 AM
 To: Daevid Vincent; mysql@lists.mysql.com
 Cc: 'shawn green'
 Subject: RE: How do I select all rows of table that have some rows in
 another table (AND, not OR)
 
 Thinking out of the box... (And posting my reply at the 'wrong' end of the
 email.)...
 
 Are there fewer than 64 genres?  Use a SET or BIGINT UNSIGNED.
 
 AND sg.`genre_id` IN (10,38)
 AND sg.`genre_id` NOT IN (22,61)
 
 --
 AND  genre  ((110) | (138)) != 0
 AND  genre  ((122) | (161))  = 0
 
 This would avoid having that extra table, and save a lot of space.
 
 If you have more than 64 genres, then
 Plan A: clump them into some kind of application grouping and use multiple
 INTs/SETs.
 Plan B: do mod  div arithmetic to compute which genre field to tackle.
 
 For B, something like:
 AND  (genre1  (10)) + (genre3  (18)) != 0
 AND  (genre2  (12)) + (genre6  (11))  = 0
 (That's assuming 10 bits per genre# field.  I would use 32 and INT
 UNSIGNED.)
 
 
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Tuesday, June 11, 2013 4:17 PM
  To: mysql@lists.mysql.com
  Cc: 'shawn green'
  Subject: RE: How do I select all rows of table that have some rows in
  another table (AND, not OR)
 
 
 
   -Original Message-
   From: shawn green [mailto:shawn.l.gr...@oracle.com]
   Sent: Tuesday, June 11, 2013 2:16 PM
   To: mysql@lists.mysql.com
   Subject: Re: How do I select all rows of table that have some rows in
   another table (AND, not OR)
  
   Hello Daevid,
  
   On 6/11/2013 3:59 PM, Daevid Vincent wrote:
I am trying to implement a filter so that a user could select
various
   genres
they want in or out. Perhaps they like 'action' and 'car chases'
but don't like 'foreign' and 'drifting' (or whatever...)
   
So I want something sort of like this, however IN() is using an OR
comparison when I need it to be an AND
   
SELECT DISTINCT
 s.`scene_id` AS `id`,
 GROUP_CONCAT(sg.`genre_id`) FROM
 `dvds` AS d
 JOIN `scenes_list` AS s
 ON s.`dvd_id` = d.`dvd_id`
 JOIN `scenes_genres` AS sg
 ON sg.`scene_id` = s.`scene_id`
 AND sg.`genre_id` IN (10,38)
 AND sg.`genre_id` NOT IN (22,61) GROUP BY
s.`scene_id`;
   
This is giving me way way too many rows returned.
   
For example, I would expect this scene_id to be in the result set:
   
SELECT * FROM scenes_genres WHERE scene_id = 17;
   
scene_id  genre_id
  --
   17   1
   17   3
   17  10 --
   17  19
   17  38 --
   17  53
   17  58
   17  59
   
And this scene ID to NOT be in the result set:
   
SELECT * FROM scenes_genres WHERE scene_id = 11;
   
scene_id  genre_id
  --
   11   1
   11  10 --
   11  19
   11  31
   11  32
   -- but does not have 38
   11  59
   
I've tried various subselect ideas, but of course this fails b/c
  genre_id
can't be multiple things at one time (AND)
   
JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
 AND sg.`genre_id` IN (
   SELECT `genre_id` FROM `scenes_genres`
WHERE `genre_id` = 10
   AND `genre_id` = 38
   AND `genre_id`  22
   AND `genre_id`  61
)
   
And straight up like this failure too...
   
   JOIN `scenes_genres` AS sg
 ON sg.`scene_id` = s.`scene_id`
AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)
   
So I'm sort of out of ideas at this point and hoping someone has a
way
  to
   do
this.
   
Also, just for SG this is how we are currently implementing it, but
we
   feel
the REGEXP is killing our queries and while clever is a bit hacky
and nullifies any indexes we have on the genres column as it
requires a file_sort table scan to compare substrings basically...
   
SELECT * FROM scene_all_genres WHERE scene_id = 17;
   
scene_id  genres

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[Daevid Vincent]

Oh! I must have misread. I didn't see how you had a solution for  64 bits.
I may have to experiment with that!

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Wednesday, June 12, 2013 11:26 AM
 To: mysql@lists.mysql.com
 Subject: RE: How do I select all rows of table that have some rows in
 another table (AND, not OR)
 
 This idea is so fancy pants and clever I *wish* it could have worked for
 me. I checked and we actually have 65 genres currently (with more to come
 I'm sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me
 some bitmasks and this solution is so elegant. It's unfortunate there
 isn't a way to use more than 64-bits natively.
 
 You're RICK JAMES Bitch! :-p   (please tell me you know the Dave
 Chappelles skit I'm referring to)
 
  -Original Message-
  From: Rick James [mailto:rja...@yahoo-inc.com]
  Sent: Wednesday, June 12, 2013 9:39 AM
  To: Daevid Vincent; mysql@lists.mysql.com
  Cc: 'shawn green'
  Subject: RE: How do I select all rows of table that have some rows in
  another table (AND, not OR)
 
  Thinking out of the box... (And posting my reply at the 'wrong' end of
 the
  email.)...
 
  Are there fewer than 64 genres?  Use a SET or BIGINT UNSIGNED.
 
  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61)
 
  --
  AND  genre  ((110) | (138)) != 0
  AND  genre  ((122) | (161))  = 0
 
  This would avoid having that extra table, and save a lot of space.
 
  If you have more than 64 genres, then
  Plan A: clump them into some kind of application grouping and use
 multiple
  INTs/SETs.
  Plan B: do mod  div arithmetic to compute which genre field to tackle.
 
  For B, something like:
  AND  (genre1  (10)) + (genre3  (18)) != 0
  AND  (genre2  (12)) + (genre6  (11))  = 0
  (That's assuming 10 bits per genre# field.  I would use 32 and INT
  UNSIGNED.)
 
 
 
   -Original Message-
   From: Daevid Vincent [mailto:dae...@daevid.com]
   Sent: Tuesday, June 11, 2013 4:17 PM
   To: mysql@lists.mysql.com
   Cc: 'shawn green'
   Subject: RE: How do I select all rows of table that have some rows in
   another table (AND, not OR)
  
  
  
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows
 in
another table (AND, not OR)
   
Hello Daevid,
   
On 6/11/2013 3:59 PM, Daevid Vincent wrote:
 I am trying to implement a filter so that a user could select
 various
genres
 they want in or out. Perhaps they like 'action' and 'car
 chases'
 but don't like 'foreign' and 'drifting' (or whatever...)

 So I want something sort of like this, however IN() is using an
 OR
 comparison when I need it to be an AND

 SELECT DISTINCT
  s.`scene_id` AS `id`,
  GROUP_CONCAT(sg.`genre_id`) FROM
  `dvds` AS d
  JOIN `scenes_list` AS s
  ON s.`dvd_id` = d.`dvd_id`
  JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61) GROUP BY
 s.`scene_id`;

 This is giving me way way too many rows returned.

 For example, I would expect this scene_id to be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 17;

 scene_id  genre_id
   --
17   1
17   3
17  10 --
17  19
17  38 --
17  53
17  58
17  59

 And this scene ID to NOT be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 11;

 scene_id  genre_id
   --
11   1
11  10 --
11  19
11  31
11  32
  -- but does not have 38
11  59

 I've tried various subselect ideas, but of course this fails b/c
   genre_id
 can't be multiple things at one time (AND)

 JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (
SELECT `genre_id` FROM `scenes_genres`
 WHERE `genre_id` = 10
AND `genre_id` = 38
AND `genre_id`  22
AND `genre_id`  61
 )

 And straight up like this failure too...

JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
 AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
 AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)

 So I'm sort of out

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[Rick James]

I'm the ORIGINAL Rick James, B  (And, I'm still alive.)  LOL

If you are using PHP, you might want to stop at 31 bits per INT/SET.  PHP seems 
not to yet be in the 64-bit world.

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Wednesday, June 12, 2013 11:26 AM
 To: mysql@lists.mysql.com
 Subject: RE: How do I select all rows of table that have some rows in
 another table (AND, not OR)
 
 This idea is so fancy pants and clever I *wish* it could have worked for
 me.
 I checked and we actually have 65 genres currently (with more to come I'm
 sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some
 bitmasks and this solution is so elegant. It's unfortunate there isn't a
 way to use more than 64-bits natively.
 
 You're RICK JAMES Bitch! :-p   (please tell me you know the Dave
 Chappelles
 skit I'm referring to)
 
  -Original Message-
  From: Rick James [mailto:rja...@yahoo-inc.com]
  Sent: Wednesday, June 12, 2013 9:39 AM
  To: Daevid Vincent; mysql@lists.mysql.com
  Cc: 'shawn green'
  Subject: RE: How do I select all rows of table that have some rows in
  another table (AND, not OR)
 
  Thinking out of the box... (And posting my reply at the 'wrong' end of
  the email.)...
 
  Are there fewer than 64 genres?  Use a SET or BIGINT UNSIGNED.
 
  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61)
 
  --
  AND  genre  ((110) | (138)) != 0
  AND  genre  ((122) | (161))  = 0
 
  This would avoid having that extra table, and save a lot of space.
 
  If you have more than 64 genres, then
  Plan A: clump them into some kind of application grouping and use
  multiple INTs/SETs.
  Plan B: do mod  div arithmetic to compute which genre field to tackle.
 
  For B, something like:
  AND  (genre1  (10)) + (genre3  (18)) != 0
  AND  (genre2  (12)) + (genre6  (11))  = 0 (That's assuming
  10 bits per genre# field.  I would use 32 and INT
  UNSIGNED.)
 
 
 
   -Original Message-
   From: Daevid Vincent [mailto:dae...@daevid.com]
   Sent: Tuesday, June 11, 2013 4:17 PM
   To: mysql@lists.mysql.com
   Cc: 'shawn green'
   Subject: RE: How do I select all rows of table that have some rows
   in another table (AND, not OR)
  
  
  
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows
in another table (AND, not OR)
   
Hello Daevid,
   
On 6/11/2013 3:59 PM, Daevid Vincent wrote:
 I am trying to implement a filter so that a user could select
 various
genres
 they want in or out. Perhaps they like 'action' and 'car
 chases'
 but don't like 'foreign' and 'drifting' (or whatever...)

 So I want something sort of like this, however IN() is using an
 OR
 comparison when I need it to be an AND

 SELECT DISTINCT
  s.`scene_id` AS `id`,
  GROUP_CONCAT(sg.`genre_id`) FROM
  `dvds` AS d
  JOIN `scenes_list` AS s
  ON s.`dvd_id` = d.`dvd_id`
  JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61) GROUP BY
 s.`scene_id`;

 This is giving me way way too many rows returned.

 For example, I would expect this scene_id to be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 17;

 scene_id  genre_id
   --
17   1
17   3
17  10 --
17  19
17  38 --
17  53
17  58
17  59

 And this scene ID to NOT be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 11;

 scene_id  genre_id
   --
11   1
11  10 --
11  19
11  31
11  32
  -- but does not have 38
11  59

 I've tried various subselect ideas, but of course this fails b/c
   genre_id
 can't be multiple things at one time (AND)

 JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (
SELECT `genre_id` FROM `scenes_genres`
 WHERE `genre_id` = 10
AND `genre_id` = 38
AND `genre_id`  22
AND `genre_id`  61
 )

 And straight up like this failure too...

JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
 AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
 AND NOT (sg

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[Paul Halliday]

I am so, so glad that someone finally said what I think each time I see a 
message from you Mr. James. 

 Original message 
From: Rick James rja...@yahoo-inc.com 
Date: 06-12-2013  8:45 PM  (GMT-04:00) 
To: Daevid Vincent dae...@daevid.com,mysql@lists.mysql.com 
Subject: RE: How do I select all rows of table that have some rows in
  another table (AND, not OR) 
 
I'm the ORIGINAL Rick James, B  (And, I'm still alive.)  LOL

If you are using PHP, you might want to stop at 31 bits per INT/SET.  PHP seems 
not to yet be in the 64-bit world.

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Wednesday, June 12, 2013 11:26 AM
 To: mysql@lists.mysql.com
 Subject: RE: How do I select all rows of table that have some rows in
 another table (AND, not OR)
 
 This idea is so fancy pants and clever I *wish* it could have worked for
 me.
 I checked and we actually have 65 genres currently (with more to come I'm
 sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some
 bitmasks and this solution is so elegant. It's unfortunate there isn't a
 way to use more than 64-bits natively.
 
 You're RICK JAMES Bitch! :-p   (please tell me you know the Dave
 Chappelles
 skit I'm referring to)
 
  -Original Message-
  From: Rick James [mailto:rja...@yahoo-inc.com]
  Sent: Wednesday, June 12, 2013 9:39 AM
  To: Daevid Vincent; mysql@lists.mysql.com
  Cc: 'shawn green'
  Subject: RE: How do I select all rows of table that have some rows in
  another table (AND, not OR)
 
  Thinking out of the box... (And posting my reply at the 'wrong' end of
  the email.)...
 
  Are there fewer than 64 genres?  Use a SET or BIGINT UNSIGNED.
 
  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61)
 
  --
  AND  genre  ((110) | (138)) != 0
  AND  genre  ((122) | (161))  = 0
 
  This would avoid having that extra table, and save a lot of space.
 
  If you have more than 64 genres, then
  Plan A: clump them into some kind of application grouping and use
  multiple INTs/SETs.
  Plan B: do mod  div arithmetic to compute which genre field to tackle.
 
  For B, something like:
  AND  (genre1  (10)) + (genre3  (18)) != 0
  AND  (genre2  (12)) + (genre6  (11))  = 0 (That's assuming
  10 bits per genre# field.  I would use 32 and INT
  UNSIGNED.)
 
 
 
   -Original Message-
   From: Daevid Vincent [mailto:dae...@daevid.com]
   Sent: Tuesday, June 11, 2013 4:17 PM
   To: mysql@lists.mysql.com
   Cc: 'shawn green'
   Subject: RE: How do I select all rows of table that have some rows
   in another table (AND, not OR)
  
  
  
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows
in another table (AND, not OR)
   
Hello Daevid,
   
On 6/11/2013 3:59 PM, Daevid Vincent wrote:
 I am trying to implement a filter so that a user could select
 various
genres
 they want in or out. Perhaps they like 'action' and 'car
 chases'
 but don't like 'foreign' and 'drifting' (or whatever...)

 So I want something sort of like this, however IN() is using an
 OR
 comparison when I need it to be an AND

 SELECT DISTINCT
  s.`scene_id` AS `id`,
  GROUP_CONCAT(sg.`genre_id`) FROM
  `dvds` AS d
  JOIN `scenes_list` AS s
  ON s.`dvd_id` = d.`dvd_id`
  JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61) GROUP BY
 s.`scene_id`;

 This is giving me way way too many rows returned.

 For example, I would expect this scene_id to be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 17;

 scene_id  genre_id
   --
    17   1
    17   3
    17  10 --
    17  19
    17  38 --
    17  53
    17  58
    17  59

 And this scene ID to NOT be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 11;

 scene_id  genre_id
   --
    11   1
    11  10 --
    11  19
    11  31
    11  32
    -- but does not have 38
    11  59

 I've tried various subselect ideas, but of course this fails b/c
   genre_id
 can't be multiple things at one time (AND)

 JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (
    SELECT `genre_id` FROM `scenes_genres`
 WHERE `genre_id` = 10

How do I select all rows of table that have some rows in another table (AND, not OR)

[Daevid Vincent]

I am trying to implement a filter so that a user could select various genres
they want in or out. Perhaps they like 'action' and 'car chases' but
don't like 'foreign' and 'drifting' (or whatever...)

So I want something sort of like this, however IN() is using an OR
comparison when I need it to be an AND

SELECT DISTINCT 
s.`scene_id` AS `id`,
GROUP_CONCAT(sg.`genre_id`)
FROM
`dvds` AS d 
JOIN `scenes_list` AS s 
ON s.`dvd_id` = d.`dvd_id` 
JOIN `scenes_genres` AS sg 
ON sg.`scene_id` = s.`scene_id` 
AND sg.`genre_id` IN (10,38) 
AND sg.`genre_id` NOT IN (22,61)
GROUP BY s.`scene_id`;

This is giving me way way too many rows returned. 

For example, I would expect this scene_id to be in the result set:

SELECT * FROM scenes_genres WHERE scene_id = 17;

scene_id  genre_id  
  --
  17   1
  17   3
  17  10 --
  17  19
  17  38 --
  17  53
  17  58
  17  59

And this scene ID to NOT be in the result set:

SELECT * FROM scenes_genres WHERE scene_id = 11;

scene_id  genre_id  
  --
  11   1
  11  10 --  
  11  19
  11  31
  11  32
   -- but does not have 38
  11  59

I've tried various subselect ideas, but of course this fails b/c genre_id
can't be multiple things at one time (AND)

   JOIN `scenes_genres` AS sg 
 ON sg.`scene_id` = s.`scene_id` 
AND sg.`genre_id` IN (
  SELECT `genre_id` FROM `scenes_genres` 
   WHERE `genre_id` = 10 
  AND `genre_id` = 38
  AND `genre_id`  22 
  AND `genre_id`  61
   )

And straight up like this failure too...

  JOIN `scenes_genres` AS sg 
ON sg.`scene_id` = s.`scene_id` 
   AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) 
   AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)

So I'm sort of out of ideas at this point and hoping someone has a way to do
this.

Also, just for SG this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while clever is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres  
  
  17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres 
  ---
  11  1|10|19|31|32|59|  

SELECT DISTINCT 
s.`scene_id` AS `id`,
sg.`genres` 
FROM
`scene_all_genres` AS sg,
`dvds` AS d,
`scenes_list` AS s 
WHEREdvd_id` = d.`dvd_id` 
AND sg.`scene_id` = s.`scene_id` 
AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]' 
AND sg.`genres` NOT REGEXP '(([[::]]22[[::]])|([[::]]61[[::]]))'

;

http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

[[::]], [[::]]

These markers stand for word boundaries. They match the beginning and end of
words, respectively. A word is a sequence of word characters that is not
preceded by or followed by word characters. A word character is an
alphanumeric character in the alnum class or an underscore (_).



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How do I select all rows of table that have some rows in another table (AND, not OR)

[shawn green]

Hello Daevid,

On 6/11/2013 3:59 PM, Daevid Vincent wrote:

I am trying to implement a filter so that a user could select various genres
they want in or out. Perhaps they like 'action' and 'car chases' but
don't like 'foreign' and 'drifting' (or whatever...)

So I want something sort of like this, however IN() is using an OR
comparison when I need it to be an AND

SELECT DISTINCT
 s.`scene_id` AS `id`,
 GROUP_CONCAT(sg.`genre_id`)
FROM
 `dvds` AS d
 JOIN `scenes_list` AS s
 ON s.`dvd_id` = d.`dvd_id`
 JOIN `scenes_genres` AS sg
 ON sg.`scene_id` = s.`scene_id`
 AND sg.`genre_id` IN (10,38)
 AND sg.`genre_id` NOT IN (22,61)
GROUP BY s.`scene_id`;

This is giving me way way too many rows returned.

For example, I would expect this scene_id to be in the result set:

SELECT * FROM scenes_genres WHERE scene_id = 17;

scene_id  genre_id
  --
   17   1
   17   3
   17  10 --
   17  19
   17  38 --
   17  53
   17  58
   17  59

And this scene ID to NOT be in the result set:

SELECT * FROM scenes_genres WHERE scene_id = 11;

scene_id  genre_id
  --
   11   1
   11  10 --
   11  19
   11  31
   11  32
   -- but does not have 38
   11  59

I've tried various subselect ideas, but of course this fails b/c genre_id
can't be multiple things at one time (AND)

JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
 AND sg.`genre_id` IN (
   SELECT `genre_id` FROM `scenes_genres`
WHERE `genre_id` = 10
   AND `genre_id` = 38
   AND `genre_id`  22
   AND `genre_id`  61
)

And straight up like this failure too...

   JOIN `scenes_genres` AS sg
 ON sg.`scene_id` = s.`scene_id`
AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)

So I'm sort of out of ideas at this point and hoping someone has a way to do
this.

Also, just for SG this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while clever is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres
  
   17  1|3|10|19|38|53|58|59|

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres
  ---
   11  1|10|19|31|32|59|

SELECT DISTINCT
 s.`scene_id` AS `id`,
 sg.`genres`
FROM
 `scene_all_genres` AS sg,
 `dvds` AS d,
 `scenes_list` AS s
WHEREdvd_id` = d.`dvd_id`
 AND sg.`scene_id` = s.`scene_id`
 AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]'
 AND sg.`genres` NOT REGEXP '(([[::]]22[[::]])|([[::]]61[[::]]))'

;

http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

[[::]], [[::]]

These markers stand for word boundaries. They match the beginning and end of
words, respectively. A word is a sequence of word characters that is not
preceded by or followed by word characters. A word character is an
alphanumeric character in the alnum class or an underscore (_).



To me it looks like you want to know how to match N for N when looking 
for subset of properties. It's a multi-step process using plain SQL but 
it's fast:


1) Build a list of scenes containing the genres you want to see

CREATE TEMPORARY TABLE tmpList(key(scene_id, genre_id))
SELECT scene_id, genre_id
FROM scenes_genres
WHERE genre_id` IN (10,38)  -- in this case you have 2 terms to mach

2) From that list, determine which of those scenes also contain unwanted 
genres and remove them.


DELETE tmpList
FROM tmpList t
INNER JOIN scenes_genres sg
  on sg.scene_id = t.scene_id
  and sg.genre_id IN (22,61)

# at this point, tmpList contains all scenes that have any of the 
desired genres but none of the unwanted ones.


3) Check to see if any scene has all N matches.

SELECT scene_id, count(genre_id) as matches
FROM tmpList
GROUP BY scene_id
HAVING matches = 2
/* ^--- this is the number of terms you are trying to match. */


Of course you can modify the last query to eliminate the HAVING clause 
and pick the top 5 matching scenes (even if they are partial matches) 
like this


SELECT scene_id, count(genre_id) as matches
FROM tmpList
GROUP BY scene_id
ORDER BY matches DESC
LIMIT 5

Let us know if this is not what you wanted to do.



Note to the rest of the list:

Yes, we could have done most of this with self-joins or subqueries, an 
EXISTS, and a NOT EXISTS inside a single statement but I think the 
execution time would have been miserable. I also think that that 
approach also

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[Daevid Vincent]

 -Original Message-
 From: shawn green [mailto:shawn.l.gr...@oracle.com]
 Sent: Tuesday, June 11, 2013 2:16 PM
 To: mysql@lists.mysql.com
 Subject: Re: How do I select all rows of table that have some rows in
 another table (AND, not OR)
 
 Hello Daevid,
 
 On 6/11/2013 3:59 PM, Daevid Vincent wrote:
  I am trying to implement a filter so that a user could select various
 genres
  they want in or out. Perhaps they like 'action' and 'car chases' but
  don't like 'foreign' and 'drifting' (or whatever...)
 
  So I want something sort of like this, however IN() is using an OR
  comparison when I need it to be an AND
 
  SELECT DISTINCT
   s.`scene_id` AS `id`,
   GROUP_CONCAT(sg.`genre_id`)
  FROM
   `dvds` AS d
   JOIN `scenes_list` AS s
   ON s.`dvd_id` = d.`dvd_id`
   JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
   AND sg.`genre_id` IN (10,38)
   AND sg.`genre_id` NOT IN (22,61)
  GROUP BY s.`scene_id`;
 
  This is giving me way way too many rows returned.
 
  For example, I would expect this scene_id to be in the result set:
 
  SELECT * FROM scenes_genres WHERE scene_id = 17;
 
  scene_id  genre_id
    --
 17   1
 17   3
 17  10 --
 17  19
 17  38 --
 17  53
 17  58
 17  59
 
  And this scene ID to NOT be in the result set:
 
  SELECT * FROM scenes_genres WHERE scene_id = 11;
 
  scene_id  genre_id
    --
 11   1
 11  10 --
 11  19
 11  31
 11  32
 -- but does not have 38
 11  59
 
  I've tried various subselect ideas, but of course this fails b/c
genre_id
  can't be multiple things at one time (AND)
 
  JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
   AND sg.`genre_id` IN (
 SELECT `genre_id` FROM `scenes_genres`
  WHERE `genre_id` = 10
 AND `genre_id` = 38
 AND `genre_id`  22
 AND `genre_id`  61
  )
 
  And straight up like this failure too...
 
 JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
  AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
  AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)
 
  So I'm sort of out of ideas at this point and hoping someone has a way
to
 do
  this.
 
  Also, just for SG this is how we are currently implementing it, but we
 feel
  the REGEXP is killing our queries and while clever is a bit hacky and
  nullifies any indexes we have on the genres column as it requires a
  file_sort table scan to compare substrings basically...
 
  SELECT * FROM scene_all_genres WHERE scene_id = 17;
 
  scene_id  genres
    
 17  1|3|10|19|38|53|58|59|
 
  SELECT * FROM scene_all_genres WHERE scene_id = 11;
 
  scene_id  genres
    ---
 11  1|10|19|31|32|59|
 
  SELECT DISTINCT
   s.`scene_id` AS `id`,
   sg.`genres`
  FROM
   `scene_all_genres` AS sg,
   `dvds` AS d,
   `scenes_list` AS s
  WHEREdvd_id` = d.`dvd_id`
   AND sg.`scene_id` = s.`scene_id`
   AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]'
   AND sg.`genres` NOT REGEXP
 '(([[::]]22[[::]])|([[::]]61[[::]]))'
 
  ;
 
  http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp
 
  [[::]], [[::]]
 
  These markers stand for word boundaries. They match the beginning and
end
 of
  words, respectively. A word is a sequence of word characters that is not
  preceded by or followed by word characters. A word character is an
  alphanumeric character in the alnum class or an underscore (_).
 
 
 To me it looks like you want to know how to match N for N when looking
 for subset of properties. It's a multi-step process using plain SQL but
 it's fast:
 
 1) Build a list of scenes containing the genres you want to see
 
 CREATE TEMPORARY TABLE tmpList(key(scene_id, genre_id))
 SELECT scene_id, genre_id
 FROM scenes_genres
 WHERE genre_id` IN (10,38)  -- in this case you have 2 terms to mach
 
 2) From that list, determine which of those scenes also contain unwanted
 genres and remove them.
 
 DELETE tmpList
 FROM tmpList t
 INNER JOIN scenes_genres sg
on sg.scene_id = t.scene_id
and sg.genre_id IN (22,61)
 
 # at this point, tmpList contains all scenes that have any of the
 desired genres but none of the unwanted ones.
 
 3) Check to see if any scene has all N matches.
 
 SELECT scene_id, count(genre_id) as matches
 FROM tmpList
 GROUP BY scene_id
 HAVING matches = 2
 /* ^--- this is the number of terms you are trying to match. */
 
 
 Of course you can modify the last query to eliminate the HAVING clause
 and pick

RE: SELECT subquery problem

[cl]

  You can do:
  
  
  SELECT last_name, first_name, phone, if(pub_email=Y,email,) as email
  FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y
  ORDER BY last_name ASC
  

Gracias, Carlos. This worked fine!

---Fritz


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

SELECT subquery problem

[cl]

De-lurking here.

I am trying to figure out how to return results from a query. What I need to do 
is to return 4 columns from a database. This is easy:

SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE 
`current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC

This works fine, as expected.

But, I want to only display the value in `email` if the value in another field, 
`pub_email` = Y  So, the resultant output would look like this, for instance, 
if the value of `pub_email` =N for Mr. Wills:

Jones  John 555-555-   johnjo...@nowhere.com
Smith  Jim   555-222-   jimsm...@nothing.com
WillsChill  555-111-
Zorro  Felicity  555-999-  felicityzo...@madeup.com

Can't quite figure out how to express this.

TIA for your suggestions!


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: SELECT subquery problem

[Andrew Moore]

Try using a CASE construct in the select. Should work for this.

A


On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn stef...@web.de wrote:

 You cannot do this. A sql result alwas has the same number of columns in
 each row. You could have null or  in the column, though. This could be
 done via the if(,,)-statement of mysql or by using a union and two selects,
 one for pub_email=n and the other for the rest.


 Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr
 Von: cl c...@nimbleeye.com
 An: mysql@lists.mysql.com
 Betreff: SELECT subquery problem
 De-lurking here.

 I am trying to figure out how to return results from a query. What I need
 to do is to return 4 columns from a database. This is easy:

 SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE
 `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC

 This works fine, as expected.

 But, I want to only display the value in `email` if the value in another
 field, `pub_email` = Y So, the resultant output would look like this, for
 instance, if the value of `pub_email` =N for Mr. Wills:

 Jones John 555-555- johnjo...@nowhere.com
 Smith Jim 555-222- jimsm...@nothing.com
 Wills Chill 555-111-
 Zorro Felicity 555-999- felicityzo...@madeup.com

 Can't quite figure out how to express this.

 TIA for your suggestions!


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql]




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Aw: SELECT subquery problem

[Stefan Kuhn]

You cannot do this. A sql result alwas has the same number of columns in each 
row. You could have null or  in the column, though. This could be done via 
the if(,,)-statement of mysql or by using a union and two selects, one for 
pub_email=n and the other for the rest.


Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr
Von: cl c...@nimbleeye.com
An: mysql@lists.mysql.com
Betreff: SELECT subquery problem
De-lurking here.

I am trying to figure out how to return results from a query. What I need to do 
is to return 4 columns from a database. This is easy:

SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE 
`current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC

This works fine, as expected.

But, I want to only display the value in `email` if the value in another field, 
`pub_email` = Y So, the resultant output would look like this, for instance, 
if the value of `pub_email` =N for Mr. Wills:

Jones John 555-555- johnjo...@nowhere.com
Smith Jim 555-222- jimsm...@nothing.com
Wills Chill 555-111-
Zorro Felicity 555-999- felicityzo...@madeup.com

Can't quite figure out how to express this.

TIA for your suggestions!


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Complex MySQL Select Statement Help

[hsv]

 2013/02/02 12:58 -0600, Peter Brawley 
On 2013-02-01 10:18 PM, h...@tbbs.net wrote:
2013/01/31 22:24 -0600, Peter Brawley 
Is this what you mean?

Select,
pricelist
If( !IsNull(specialprice) And specialprice  unitprice And CurDate() Between 
startingDate And endingDate,
specialprice,
unitprice
) as used_price
From catalog
Where itemid='WB314';

PB

Maybe this is gilding the lily, but if specialprice is null, then
specialprice  unitprice
is not true

Read again: ...If( !IsNull( specialprice )...

Right: if
specialprice  unitprice
is true, then specialprice is not null. The null-test is absorbed. 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Complex MySQL Select Statement Help

[hsv]

 2013/01/31 22:24 -0600, Peter Brawley 
Is this what you mean?

Select,
pricelist
If( !IsNull(specialprice) And specialprice  unitprice And CurDate() Between 
startingDate And endingDate,
specialprice,
unitprice
) as used_price
From catalog
Where itemid='WB314';

PB 

Maybe this is gilding the lily, but if specialprice is null, then
specialprice  unitprice
is not true--and maybe if the null-test is left out it is less clear 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Complex MySQL Select Statement Help

[Peter Brawley]

On 2013-02-01 10:18 PM, h...@tbbs.net wrote:

2013/01/31 22:24 -0600, Peter Brawley 

Is this what you mean?

Select,
pricelist
If( !IsNull(specialprice) And specialprice  unitprice And CurDate() Between 
startingDate And endingDate,
specialprice,
unitprice
) as used_price
From catalog
Where itemid='WB314';

PB

Maybe this is gilding the lily, but if specialprice is null, then
specialprice  unitprice
is not true


Read again: ...If( !IsNull( specialprice )...

PB

--and maybe if the null-test is left out it is less clear





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Complex MySQL Select Statement Help

[Angela Barone]

Hello,

I'm trying to write a select query that grabs two prices from my db and 
displays them on a web page.  I want it to grab the `listprice`, and either the 
`unitprice` or the `specialprice` depending on the following criteria:
if the `specialprice` is not empty,
AND it's less than the `unitprice`,
AND the current date is between the `startingdate` and `endingdate`, 
then pull the `specialprice` otherwise pull the `unitprice`.

This is the code I've used up until now, and it works, but I need to 
add the date range, as described above:

?php $result = mysql_query(SELECT priceList, 
LEAST(unitprice,ifnull(specialprice,'')) AS used_price FROM catalog WHERE 
itemid='WB314',$db);
printf('font size=-1iList: $s%s/s/i/fontbr /', 
number_format(mysql_result($result,0,priceList),2));
printf('bfont color=#55Your Price:/font $%s/bbr /', 
number_format(mysql_result($result,0,used_price),2)); ?

This seems rather convoluted to me and I've been struggling with it all 
day.  Any help would be greatly appreciated!

Thank you so much!
Angela


Schema

NameTypeNULLDefault
startingd   textYes NULL
endingd textYes NULL
specialpricetinytextYes NULL
unitprice   tinytextYes NULL

• Date fields are formatted as 1/31/2013 and cannot be changed because the db 
is used by another script that I can't change.
• Field types can be changed if necessary, as long as the date format remains 
the same.

If you need more information, please let me know.

Re: Complex MySQL Select Statement Help

[Peter Brawley]

On 2013-01-31 8:13 PM, Angela Barone wrote:

Hello,

I'm trying to write a select query that grabs two prices from my db and 
displays them on a web page.  I want it to grab the `listprice`, and either the 
`unitprice` or the `specialprice` depending on the following criteria:
if the `specialprice` is not empty,
AND it's less than the `unitprice`,
AND the current date is between the `startingdate` and `endingdate`,
then pull the `specialprice` otherwise pull the `unitprice`.


Is this what you mean?

Select,
pricelist
If( !IsNull(specialprice) And specialprice  unitprice And CurDate() 
Between startingDate And endingDate,

specialprice,
unitprice
) as used_price
From catalog
Where itemid='WB314';

PB

-



This is the code I've used up until now, and it works, but I need to 
add the date range, as described above:

?php $result = mysql_query(SELECT priceList, 
LEAST(unitprice,ifnull(specialprice,'')) AS used_price FROM catalog WHERE 
itemid='WB314',$db);
printf('font size=-1iList: $s%s/s/i/fontbr /', 
number_format(mysql_result($result,0,priceList),2));
printf('bfont color=#55Your Price:/font $%s/bbr /', 
number_format(mysql_result($result,0,used_price),2)); ?

This seems rather convoluted to me and I've been struggling with it all 
day.  Any help would be greatly appreciated!

Thank you so much!
Angela


Schema

NameTypeNULLDefault
startingd   textYes NULL
endingd textYes NULL
specialpricetinytextYes NULL
unitprice   tinytextYes NULL

• Date fields are formatted as 1/31/2013 and cannot be changed because the db 
is used by another script that I can't change.
• Field types can be changed if necessary, as long as the date format remains 
the same.

If you need more information, please let me know.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Shawn Green]

Hi Neil,

On 11/22/2012 7:14 PM, h...@tbbs.net wrote:

2012/11/22 14:30 +, Neil Tompkins 

I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

 From this I what to get a distinct list of id where the type equals 2 and 5

Any ideas ?

This ugly one, which generalizes:

select id,group_concat(type) AS tl from the_table group by id having 
find_in_set('2',tl) and find_in_set('5',tl)

Ugly becaus it involves so much converting between number  string.

For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my tl and a string, say '1,2', to a procedure that using 
SUBSTRING_INDEX taking the strings for arrays ensures that all found in the first string 
is also in the second string. There are times when I wish SQL had arrays.


The fun part of solving this is to remember that SQL is a set-oriented 
language. For each element in the set, none of them can be both 2 and 5 
at the same time. So, you have to build two sets and check to see which 
rows are in both.


One pattern works if you need to aggregate for just a few terms

SELECT a.id
from (select distinct id from mytable where type=2) a
INNER JOIN (select distinct id from mytable where type=5) b
  on a.id=b.id

However, this gets numerically very expensive with more than a few JOINS 
to the pattern.  Also, there is no index on either of the temporary 
results (a or b) so this is a full Cartesian product of both tables. 
That means that although it gives you a correct answer, it will not 
scale to 10's of rows (or more) in either set.



So, here is a way to assemble the same result that uses much less 
resources. Remember, each row you want is a member of a set.


CREATE TEMPORARY TABLE tmpList (
  id int
, type int
, PRIMARY KEY (id,type)
)

INSERT IGNORE tmpList
SELECT id,type
FROM mytable
WHERE type in (2,5)

SELECT id, count(type) hits
FROM tmplist
GROUP BY id
HAVING hits=2

DROP TEMPORARY TABLE tmpList

Can you see why this works?

I created an indexed subset of rows that match either value (2 or 5) but 
only keep one example of each. I accomplished that by the combination of 
PRIMARY KEY and INSERT IGNORE. Then I counted many type values each ID 
value represented in the subset.  If I looked for 2 terms and I ended up 
with hits=2, then I know that those ID values matched on both terms.


You can expand on this pattern to also do partial (M of N search terms) 
or best-fit determinations.


I hope this was the kind of help you were looking for.

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[divesh kamra]

Hi

Is there is performance issue from this query on more then 5-10 million data





On Fri, Nov 23, 2012 at 11:17 AM, Mogens Melander mog...@fumlersoft.dkwrote:

 Ok, to make up for my bad joke, here's the answer
 to the original question.

 DROP TABLE IF EXISTS `test`.`atest`;
 CREATE TABLE  `test`.`atest` (
   `id` int(10) unsigned NOT NULL,
   `type` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 insert into atest(id,type)
 values(1000,5)
 ,(1001,5)
 ,(1002,2)
 ,(1001,2)
 ,(1003,2)
 ,(1005,2)
 ,(1006,1);

 SELECT DISTINCT id
 FROM atest
 WHERE `type` = 2 OR `type` = 5
 GROUP BY id
 HAVING count(DISTINCT `type`) = 2;

 On Thu, November 22, 2012 22:16, Michael Dykman wrote:
  Mogens,
 
  Platform could not be less relevant to a question of MySql syntax.
  The techniques we have been discussing have been available to every
  version of MySql post v3.23 and the class/job function he is applying
  it to is neither relevant to the problem nor any of our business,
  unless he volunteers to share it. Excepting only the working
  assumption that he is using a MySql version released in this century,
  I don't know how this would have informed my analysis or response.
 
   - michael dykman
 
  On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander mog...@fumlersoft.dk
  wrote:
 
  On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
  Basically I only what to return the IDs that have both types.
 
 
  And that's exactly what below statement will return.
 
  You forgot to include what platform you are on,
  which version of MySQL you are running and
  what class you are attending.
 
  All necessary information to provide a sufficient help.
 
 
  On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
  marek.gutow...@gmail.comwrote:
 
  SELECT DISTINCT id FROM table WHERE type IN ('2','5')
 
  should work
 
 
  On 22 November 2012 14:30, Neil Tompkins
  neil.tompk...@googlemail.comwrote:
 
  Hi,
 
  I'm struggling with what I think is a basic select but can't think
  how
  to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
  and
  5
 
  Any ideas ?
 
  Neil
 
 
 
 
  --
  This message has been scanned for viruses and
  dangerous content by MailScanner, and is
  believed to be clean.
 
 
 
 
  --
  Mogens Melander
  +66 8701 33224
 
  --
  This message has been scanned for viruses and
  dangerous content by MailScanner, and is
  believed to be clean.
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
  --
   - michael dykman
   - mdyk...@gmail.com
 
   May the Source be with you.
 
  --
  This message has been scanned for viruses and
  dangerous content by MailScanner, and is
  believed to be clean.
 
 


 --
 Mogens Melander
 +66 8701 33224

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Basic SELECT help

[Neil Tompkins]

Hi,

I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

From this I what to get a distinct list of id where the type equals 2 and 5

Any ideas ?

Neil

Re: Basic SELECT help

[marek gutowski]

SELECT DISTINCT id FROM table WHERE type IN ('2','5')

should work


On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.comwrote:

 Hi,

 I'm struggling with what I think is a basic select but can't think how to
 do it : My data is

 id,type

 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1

 From this I what to get a distinct list of id where the type equals 2 and 5

 Any ideas ?

 Neil

Re: Basic SELECT help

[Mike OK]

Hi Neil

Would something like this work.

SELECT DISTINCT id,type from your_table WHERE type=2 OR type=5;

Mike


- Original Message - 
From: Neil Tompkins neil.tompk...@googlemail.com

To: [MySQL] mysql@lists.mysql.com
Sent: Thursday, November 22, 2012 9:30 AM
Subject: Basic SELECT help



Hi,

I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

From this I what to get a distinct list of id where the type equals 2 and 
5


Any ideas ?

Neil




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Neil Tompkins]

Basically I only what to return the IDs that have both types.


On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.comwrote:

 SELECT DISTINCT id FROM table WHERE type IN ('2','5')

 should work


 On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.comwrote:

 Hi,

 I'm struggling with what I think is a basic select but can't think how to
 do it : My data is

 id,type

 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1

 From this I what to get a distinct list of id where the type equals 2 and
 5

 Any ideas ?

 Neil

Fwd: Basic SELECT help

[Michael Dykman]

response did not go to the list..


I assume that you mean the id must be associated with both type=5 AND
type=2 as opposed to type=5 OR type=2;

in some dialect of SQL (not mysql) you can do this:
select distinct id from 'table' where type=5
intersect
select distinct id from 'table' where type=2


As INTERSECT is not avilable under mysql, we will have to go the JOIN route

select distinct a.id from mytable a
inner join mytable b on (a.id=b.id)
where a.type= 2 and b.type = 5;

 - michael dykman

On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:
 Hi,

 I'm struggling with what I think is a basic select but can't think how to
 do it : My data is

 id,type

 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1

 From this I what to get a distinct list of id where the type equals 2 and 5

 Any ideas ?

 Neil



--
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.


-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Benaya Paul]

U can remove the type field it will work
On Nov 22, 2012 8:21 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:

 Basically I only what to return the IDs that have both types.


 On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.com
 wrote:

  SELECT DISTINCT id FROM table WHERE type IN ('2','5')
 
  should work
 
 
  On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.com
 wrote:
 
  Hi,
 
  I'm struggling with what I think is a basic select but can't think how
 to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
 and
  5
 
  Any ideas ?
 
  Neil
 
 
 

Re: Basic SELECT help

[Neil Tompkins]

How about if I have the following

SELECT DISTINCT id
FROM my_table
WHERE (type = 3 OR type  = 28 OR type  = 1)

In this instance, for the id 280149 it only has types 3 and 28 but *not *1.
 But using the OR statement returns id 280149




On Thu, Nov 22, 2012 at 2:53 PM, Benaya Paul benayap...@gmail.com wrote:

 U can remove the type field it will work
 On Nov 22, 2012 8:21 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 Basically I only what to return the IDs that have both types.


 On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.com
 wrote:

  SELECT DISTINCT id FROM table WHERE type IN ('2','5')
 
  should work
 
 
  On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.com
 wrote:
 
  Hi,
 
  I'm struggling with what I think is a basic select but can't think how
 to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
 and
  5
 
  Any ideas ?
 
  Neil
 
 
 

Re: Basic SELECT help

[Neil Tompkins]

Michael,

Thanks this kind of works if I'm checking two types.  But what about if I
have 5 types ?

On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 response did not go to the list..


 I assume that you mean the id must be associated with both type=5 AND
 type=2 as opposed to type=5 OR type=2;

 in some dialect of SQL (not mysql) you can do this:
 select distinct id from 'table' where type=5
 intersect
 select distinct id from 'table' where type=2


 As INTERSECT is not avilable under mysql, we will have to go the JOIN route

 select distinct a.id from mytable a
 inner join mytable b on (a.id=b.id)
 where a.type= 2 and b.type = 5;

  - michael dykman

 On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
 neil.tompk...@googlemail.com wrote:
  Hi,
 
  I'm struggling with what I think is a basic select but can't think how to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
 and 5
 
  Any ideas ?
 
  Neil



 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.


 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Michael Dykman]

Keep joining I think. In the absence of intersect (which incurs the cost of
a query per type anyhow ), this join pattern is the only option I can think
of.

On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com
wrote:

Michael,

Thanks this kind of works if I'm checking two types.  But what about if I
have 5 types ?

On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 
  response did not go to the list..
 
 
  I assume that you mean the id must be associated with bo...

 
 
  --
   - michael dykman
   - mdyk...@gmail.com
 
   May the Source be with you.
 

  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe...

Re: Basic SELECT help

[Ben Mildren]

SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com wrote:
 Michael,

 Thanks this kind of works if I'm checking two types.  But what about if I
 have 5 types ?

 On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 response did not go to the list..


 I assume that you mean the id must be associated with both type=5 AND
 type=2 as opposed to type=5 OR type=2;

 in some dialect of SQL (not mysql) you can do this:
 select distinct id from 'table' where type=5
 intersect
 select distinct id from 'table' where type=2


 As INTERSECT is not avilable under mysql, we will have to go the JOIN route

 select distinct a.id from mytable a
 inner join mytable b on (a.id=b.id)
 where a.type= 2 and b.type = 5;

  - michael dykman

 On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
 neil.tompk...@googlemail.com wrote:
  Hi,
 
  I'm struggling with what I think is a basic select but can't think how to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
 and 5
 
  Any ideas ?
 
  Neil



 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.


 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Neil Tompkins]

Do you know if I had multiple joins there would be a performance issue ?


On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman mdyk...@gmail.com wrote:

 Keep joining I think. In the absence of intersect (which incurs the cost
 of a query per type anyhow ), this join pattern is the only option I can
 think of.

 On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 Michael,

 Thanks this kind of works if I'm checking two types.  But what about if I
 have 5 types ?

 On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 
  response did not go to the list..
 
 
  I assume that you mean the id must be associated with bo...

 
 
  --
   - michael dykman
   - mdyk...@gmail.com
 
   May the Source be with you.
 

  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe...

Re: Basic SELECT help

[Ben Mildren]

Ah read it quickly and misread your requirement.  Joins are likely FTW
here.  The alternative would be to do something like this, but I'd opt
for the joins if you have a reasonably sized data set:

SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
WHERE id IN(x,y,z) GROUP BY id HAVING listid = 'x,y,z';

On 22 November 2012 15:10, Ben Mildren ben.mild...@gmail.com wrote:
 SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

 On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com wrote:
 Michael,

 Thanks this kind of works if I'm checking two types.  But what about if I
 have 5 types ?

 On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 response did not go to the list..


 I assume that you mean the id must be associated with both type=5 AND
 type=2 as opposed to type=5 OR type=2;

 in some dialect of SQL (not mysql) you can do this:
 select distinct id from 'table' where type=5
 intersect
 select distinct id from 'table' where type=2


 As INTERSECT is not avilable under mysql, we will have to go the JOIN route

 select distinct a.id from mytable a
 inner join mytable b on (a.id=b.id)
 where a.type= 2 and b.type = 5;

  - michael dykman

 On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
 neil.tompk...@googlemail.com wrote:
  Hi,
 
  I'm struggling with what I think is a basic select but can't think how to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
 and 5
 
  Any ideas ?
 
  Neil



 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.


 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Michael Dykman]

Of course there is a cost for the join, each link being a distinct
lookup query but that is the same cost the INTERSECT would impose.

It is not a bad as multiple joins generally might be as all the
lookups are against the same key in the same table which should keep
that index in ram. (type is indexed, yes?)

As you no doubt have noticed, the problem with these solutions:

   SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

is that they returns ids that have ANY of those values which is not
what you are asking for,

If your dataset is HUGE, there might be a performance problem which
might force you to reformulate as:

create temporary table `mytemp` select id, type from `mytable` WHERE
type IN(x,y,z);

select distinct a.id from `mytemp` a
  inner join `mytemp` b on (a.id=b.id)
  where a.type= 2 and b.type = 5;
  -- repeat inner join as needed

drop table mytemp;


On Thu, Nov 22, 2012 at 10:09 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:

 Do you know if I had multiple joins there would be a performance issue ?


 On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman mdyk...@gmail.com wrote:

 Keep joining I think. In the absence of intersect (which incurs the cost
 of a query per type anyhow ), this join pattern is the only option I can
 think of.

 On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 Michael,

 Thanks this kind of works if I'm checking two types.  But what about if I
 have 5 types ?

 On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 
  response did not go to the list..
 
 
  I assume that you mean the id must be associated with bo...

 
 
  --
   - michael dykman
   - mdyk...@gmail.com
 
   May the Source be with you.
 

  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe...






-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Ben Mildren]

*HAVING typelist = 'x,y,z';

On 22 November 2012 15:25, Ben Mildren ben.mild...@gmail.com wrote:
 Ah read it quickly and misread your requirement.  Joins are likely FTW
 here.  The alternative would be to do something like this, but I'd opt
 for the joins if you have a reasonably sized data set:

 SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
 WHERE id IN(x,y,z) GROUP BY id HAVING listid = 'x,y,z';

 On 22 November 2012 15:10, Ben Mildren ben.mild...@gmail.com wrote:
 SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

 On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com 
 wrote:
 Michael,

 Thanks this kind of works if I'm checking two types.  But what about if I
 have 5 types ?

 On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 response did not go to the list..


 I assume that you mean the id must be associated with both type=5 AND
 type=2 as opposed to type=5 OR type=2;

 in some dialect of SQL (not mysql) you can do this:
 select distinct id from 'table' where type=5
 intersect
 select distinct id from 'table' where type=2


 As INTERSECT is not avilable under mysql, we will have to go the JOIN route

 select distinct a.id from mytable a
 inner join mytable b on (a.id=b.id)
 where a.type= 2 and b.type = 5;

  - michael dykman

 On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
 neil.tompk...@googlemail.com wrote:
  Hi,
 
  I'm struggling with what I think is a basic select but can't think how to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
 and 5
 
  Any ideas ?
 
  Neil



 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.


 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Basic SELECT help

[Jason Trebilcock]

Having watched responses go back and forth, I'll throw my cave-man approach
into the mix.

select id from
(select distinct id, count(*) 
from my_table 
where type in (2,5)
group by id
having count(*) = 2)a;

And addressing one of your concerns about more than two variables...in this
example,you would have to update the values in the where clause and the
count.

It ain't the prettiest...and not ideal from a performance perspective, but
it does work. I guess it kind of depends on how far the real-world problem
strays from this small example.

 -Original Message-
 From: Neil Tompkins [mailto:neil.tompk...@googlemail.com]
 Sent: Thursday, November 22, 2012 8:30 AM
 To: [MySQL]
 Subject: Basic SELECT help
 
 Hi,
 
 I'm struggling with what I think is a basic select but can't think how
 to do it : My data is
 
 id,type
 
 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1
 
 From this I what to get a distinct list of id where the type equals 2
 and 5
 
 Any ideas ?
 
 Neil


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Michael Dykman]

Assuming that (id,type) is unique in the source data, that is a pretty
elegant method:

 select id from
 (select distinct id, count(*)
 from my_table
 where type in (2,5)
 group by id
 having count(*) = 2)a;


-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Neil Tompkins]

By unique you mean that no id and type would be duplicated like

1,1
1,1

Yes it isn't possible for duplicate id and type in more than 1 row


On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote:

 Assuming that (id,type) is unique in the source data, that is a pretty
 elegant method:

  select id from
  (select distinct id, count(*)
  from my_table
  where type in (2,5)
  group by id
  having count(*) = 2)a;
 

 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Neil Tompkins]

When trying this query I get

FUNCTION id does not exist

On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote:

 select id from
  (select distinct id, count(*)
  from my_table
  where type in (2,5)
  group by id
  having count(*) = 2)a;

Re: Basic SELECT help

[Neil Tompkins]

Ignore that it does work fine. Sorry

On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote:

 Assuming that (id,type) is unique in the source data, that is a pretty
 elegant method:

  select id from
  (select distinct id, count(*)
  from my_table
  where type in (2,5)
  group by id
  having count(*) = 2)a;
 

 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Neil Tompkins]

Doing a EXPLAIN on the SELECT statement it is using Using where; Using
temporary; Using filesort with 14000 rows of data.  How best to improve
this; when I already have indexed on id and type



On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote:

 Assuming that (id,type) is unique in the source data, that is a pretty
 elegant method:

  select id from
  (select distinct id, count(*)
  from my_table
  where type in (2,5)
  group by id
  having count(*) = 2)a;
 

 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Michael Dykman]

On Thu, Nov 22, 2012 at 11:58 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:

 By unique you mean that no id and type would be duplicated like

 1,1
 1,1

 Yes it isn't possible for duplicate id and type in more than 1 row
Yes, that's exactly what I meant.


 - mdyk...@gmail.com

 May the Source be with you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Mogens Melander]

Hmmm.

OR, IN and HAVING pops up.


On Thu, November 22, 2012 15:30, Neil Tompkins wrote:
 Hi,

 I'm struggling with what I think is a basic select but can't think how to
 do it : My data is

 id,type

 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1

 From this I what to get a distinct list of id where the type equals 2 and
 5

 Any ideas ?

 Neil

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Mogens Melander]

On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
 Basically I only what to return the IDs that have both types.


And that's exactly what below statement will return.

You forgot to include what platform you are on,
which version of MySQL you are running and
what class you are attending.

All necessary information to provide a sufficient help.


 On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
 marek.gutow...@gmail.comwrote:

 SELECT DISTINCT id FROM table WHERE type IN ('2','5')

 should work


 On 22 November 2012 14:30, Neil Tompkins
 neil.tompk...@googlemail.comwrote:

 Hi,

 I'm struggling with what I think is a basic select but can't think how
 to
 do it : My data is

 id,type

 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1

 From this I what to get a distinct list of id where the type equals 2
 and
 5

 Any ideas ?

 Neil




 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Michael Dykman]

Mogens,

Platform could not be less relevant to a question of MySql syntax.
The techniques we have been discussing have been available to every
version of MySql post v3.23 and the class/job function he is applying
it to is neither relevant to the problem nor any of our business,
unless he volunteers to share it. Excepting only the working
assumption that he is using a MySql version released in this century,
I don't know how this would have informed my analysis or response.

 - michael dykman

On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander mog...@fumlersoft.dk wrote:

 On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
 Basically I only what to return the IDs that have both types.


 And that's exactly what below statement will return.

 You forgot to include what platform you are on,
 which version of MySQL you are running and
 what class you are attending.

 All necessary information to provide a sufficient help.


 On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
 marek.gutow...@gmail.comwrote:

 SELECT DISTINCT id FROM table WHERE type IN ('2','5')

 should work


 On 22 November 2012 14:30, Neil Tompkins
 neil.tompk...@googlemail.comwrote:

 Hi,

 I'm struggling with what I think is a basic select but can't think how
 to
 do it : My data is

 id,type

 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1

 From this I what to get a distinct list of id where the type equals 2
 and
 5

 Any ideas ?

 Neil




 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




 --
 Mogens Melander
 +66 8701 33224

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[hsv]

 2012/11/22 14:30 +, Neil Tompkins 
I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

From this I what to get a distinct list of id where the type equals 2 and 5

Any ideas ?

This ugly one, which generalizes:

select id,group_concat(type) AS tl from the_table group by id having 
find_in_set('2',tl) and find_in_set('5',tl)

Ugly becaus it involves so much converting between number  string.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[hsv]

 2012/11/22 14:30 +, Neil Tompkins 
I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

From this I what to get a distinct list of id where the type equals 2 and 5

Any ideas ?

This ugly one, which generalizes:

select id,group_concat(type) AS tl from the_table group by id having 
find_in_set('2',tl) and find_in_set('5',tl)

Ugly becaus it involves so much converting between number  string.

For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my tl and a string, say '1,2', to a procedure that using 
SUBSTRING_INDEX taking the strings for arrays ensures that all found in the 
first string is also in the second string. There are times when I wish SQL had 
arrays.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Claudio Nanni]

On 11/22/2012 04:10 PM, Ben Mildren wrote:

SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

Ben you were almost there ;)

SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=num of 
params

The only bad is the hardcoded parameter in the HAVING, may be it might be 
improved.

Anyway if the query is handwritten then you just hand-modify that too,
if it is built from code I can't imagine counting the parameters in the code 
being so hard.

Cheers

Claudio




On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com wrote:

Michael,

Thanks this kind of works if I'm checking two types.  But what about if I
have 5 types ?

On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:


response did not go to the list..


I assume that you mean the id must be associated with both type=5 AND
type=2 as opposed to type=5 OR type=2;

in some dialect of SQL (not mysql) you can do this:
select distinct id from 'table' where type=5
intersect
select distinct id from 'table' where type=2


As INTERSECT is not avilable under mysql, we will have to go the JOIN route

select distinct a.id from mytable a
inner join mytable b on (a.id=b.id)
where a.type= 2 and b.type = 5;

  - michael dykman

On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:

Hi,

I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

 From this I what to get a distinct list of id where the type equals 2

and 5

Any ideas ?

Neil



--
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.


--
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql





--
Claudio

Re: Basic SELECT help

[Mogens Melander]

Ok, to make up for my bad joke, here's the answer
to the original question.

DROP TABLE IF EXISTS `test`.`atest`;
CREATE TABLE  `test`.`atest` (
  `id` int(10) unsigned NOT NULL,
  `type` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into atest(id,type)
values(1000,5)
,(1001,5)
,(1002,2)
,(1001,2)
,(1003,2)
,(1005,2)
,(1006,1);

SELECT DISTINCT id
FROM atest
WHERE `type` = 2 OR `type` = 5
GROUP BY id
HAVING count(DISTINCT `type`) = 2;

On Thu, November 22, 2012 22:16, Michael Dykman wrote:
 Mogens,

 Platform could not be less relevant to a question of MySql syntax.
 The techniques we have been discussing have been available to every
 version of MySql post v3.23 and the class/job function he is applying
 it to is neither relevant to the problem nor any of our business,
 unless he volunteers to share it. Excepting only the working
 assumption that he is using a MySql version released in this century,
 I don't know how this would have informed my analysis or response.

  - michael dykman

 On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander mog...@fumlersoft.dk
 wrote:

 On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
 Basically I only what to return the IDs that have both types.


 And that's exactly what below statement will return.

 You forgot to include what platform you are on,
 which version of MySQL you are running and
 what class you are attending.

 All necessary information to provide a sufficient help.


 On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
 marek.gutow...@gmail.comwrote:

 SELECT DISTINCT id FROM table WHERE type IN ('2','5')

 should work


 On 22 November 2012 14:30, Neil Tompkins
 neil.tompk...@googlemail.comwrote:

 Hi,

 I'm struggling with what I think is a basic select but can't think
 how
 to
 do it : My data is

 id,type

 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1

 From this I what to get a distinct list of id where the type equals 2
 and
 5

 Any ideas ?

 Neil




 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




 --
 Mogens Melander
 +66 8701 33224

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




 --
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Basic SELECT help

[Neil Tompkins]

Claudio

This is the solution i decided to go for as provided in a previous response.

Thanks
Neil

On 23 Nov 2012, at 00:41, Claudio Nanni claudio.na...@gmail.com wrote:

 On 11/22/2012 04:10 PM, Ben Mildren wrote:
 SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
 Ben you were almost there ;)
 
 SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=num 
 of params
 
 The only bad is the hardcoded parameter in the HAVING, may be it might be 
 improved.
 
 Anyway if the query is handwritten then you just hand-modify that too,
 if it is built from code I can't imagine counting the parameters in the code 
 being so hard.
 
 Cheers
 
 Claudio
 
 
 
 On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com 
 wrote:
 Michael,
 
 Thanks this kind of works if I'm checking two types.  But what about if I
 have 5 types ?
 
 On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:
 
 response did not go to the list..
 
 
 I assume that you mean the id must be associated with both type=5 AND
 type=2 as opposed to type=5 OR type=2;
 
 in some dialect of SQL (not mysql) you can do this:
 select distinct id from 'table' where type=5
 intersect
 select distinct id from 'table' where type=2
 
 
 As INTERSECT is not avilable under mysql, we will have to go the JOIN route
 
 select distinct a.id from mytable a
 inner join mytable b on (a.id=b.id)
 where a.type= 2 and b.type = 5;
 
  - michael dykman
 
 On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
 neil.tompk...@googlemail.com wrote:
 Hi,
 
 I'm struggling with what I think is a basic select but can't think how to
 do it : My data is
 
 id,type
 
 1000,5
 1001,5
 1002,2
 1001,2
 1003,2
 1005,2
 1006,1
 
 From this I what to get a distinct list of id where the type equals 2
 and 5
 Any ideas ?
 
 Neil
 
 
 --
  - michael dykman
  - mdyk...@gmail.com
 
  May the Source be with you.
 
 
 --
  - michael dykman
  - mdyk...@gmail.com
 
  May the Source be with you.
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 -- 
 Claudio
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[abhishek jain]

Hi,
Or if you are interested in limiting the number of rows add a limit clause,
so that entire resultset of entire table is not returned ,

I am not sure how will that be done , but i have seen some GUIs doing that,
would look for a solution from group.

Thanks
Abhishek 

 -Original Message-
 From: Martin Gainty [mailto:mgai...@hotmail.com]
 Sent: 24 September 2012 04:58
 To: fuller.art...@gmail.com; pownall...@gmail.com
 Cc: mysql@lists.mysql.com
 Subject: RE: How to block SELECT * FROM table; but not SELECT * FROMT
 table WHERE...;
 
 
 Possibly run your constructed query thru a regex expression e.g.
 String mydata = SELECT * from table WHERE ab;; Pattern pattern =
 Pattern.compile('WHERE'); Matcher matcher = pattern.matcher(mydata);
 if (matcher.find()) {  //WHERE clause found proceed normally } else
 throw new Exception(WHERE clause not found); Martin
 __
 Verzicht und Vertraulichkeitanmerkung/Note de déni et de
 confidentialité
 
 Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
 Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede
 unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig.
 Diese Nachricht dient lediglich dem Austausch von Informationen und
 entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten
 Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt
 uebernehmen.
 Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas
 le destinataire prévu, nous te demandons avec bonté que pour satisfaire
 informez l'expéditeur. N'importe quelle diffusion non autorisée ou la
 copie de ceci est interdite. Ce message sert à l'information seulement
 et n'aura pas n'importe quel effet légalement obligatoire. Étant donné
 que les email peuvent facilement être sujets à la manipulation, nous ne
 pouvons accepter aucune responsabilité pour le contenu fourni.
 
 
  Date: Sun, 23 Sep 2012 18:38:58 -0400
  Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT
  table WHERE...;
  From: fuller.art...@gmail.com
  To: pownall...@gmail.com
  CC: mysql@lists.mysql.com
 
  Tim,
 
  I think you misunderstood the question. Daniel wants to block Select
  queries that ask for all rwows, and permit only queries that ask for
  some rows, as restricted by the Where clause.
 
  Unfortunately, I don't think that can be done. But I'm not certain of
  that; there might be a trick.
 
  Arthur
  www.artfulsoftware.com
 
  On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com
 wrote:
 
select * from table where column=value means it will return only
   rows that match.  as long as you have proper indexing there should
 not be any issues.
  
   On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz 
   luis.daniel.lu...@gmail.com wrote:
  
  
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[Rick James]

Even if you could block them, they would be easy to get around:
  SELECT * FROM tbl WHERE 1;

If you have long running queries, you should investigate the reasons (other 
than lack of WHERE).

* MyISAM locks the table for any writes.  This prevents a SELECT from starting 
or a select can prevent the INSERT/UPDATE/DELETE from starting.  Consider 
switching to InnoDB.

* How big is the table?  Why does the user want the whole table (if it is a 
plain SELECT *)?  If you are doing Data Warehousing, consider summary tables.

Let's see the big picture; I expect there is a way to solve the _real_ problem, 
which I guess is performance.

 -Original Message-
 From: Arthur Fuller [mailto:fuller.art...@gmail.com]
 Sent: Sunday, September 23, 2012 3:39 PM
 To: Tim Pownall
 Cc: mysql@lists.mysql.com
 Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT
 table WHERE...;
 
 Tim,
 
 I think you misunderstood the question. Daniel wants to block Select
 queries that ask for all rwows, and permit only queries that ask for
 some rows, as restricted by the Where clause.
 
 Unfortunately, I don't think that can be done. But I'm not certain of
 that; there might be a trick.
 
 Arthur
 www.artfulsoftware.com
 
 On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com
 wrote:
 
   select * from table where column=value means it will return only
 rows
  that match.  as long as you have proper indexing there should not be
 any issues.
 
  On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz 
  luis.daniel.lu...@gmail.com wrote:
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[Luis Daniel Lucio Quiroz]

Helo,

Just wondering if is possible to block SELECT queries that doesnt have
a WHERE statement within.


LD

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[Reindl Harald]

Am 23.09.2012 20:23, schrieb Luis Daniel Lucio Quiroz:
 Just wondering if is possible to block SELECT queries that doesnt have
 a WHERE statement within.

no and the idea is broken by design

what is wrong with a select * from table with small
tbales having only a handful of records?

how will you work with broken select count(*) from table;?






signature.asc
Description: OpenPGP digital signature

Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[Tim Pownall]

 select * from table where column=value means it will return only rows that
match.  as long as you have proper indexing there should not be any issues.

On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz 
luis.daniel.lu...@gmail.com wrote:

 Helo,

 Just wondering if is possible to block SELECT queries that doesnt have
 a WHERE statement within.


 LD

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 

Thanks,

Tim Pownall
GNU/Linux Systems Monitoring
610-621-9712
pownall...@gmail.com

Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[Reindl Harald]

that does not change the fact that it is valid to NOT have a where
statement and for me it sound plain  stupid to think about blocking
queries without where-statements - the application developers will
hopefully kill any admin who finds a solution for this...

* i maintain a cms-system since 10 years
* mainmenu: select id,title from table order by sortfield;
* and YES in the backend the is no single reson to reduce the result
* and NO using a key would not make anything faster

so why would there be a WHERE make any sense and why does
someone like to break the application just for fun?

Am 23.09.2012 21:50, schrieb Tim Pownall:
  select * from table where column=value means it will return only rows that
 match.  as long as you have proper indexing there should not be any issues.
 
 On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz 
 luis.daniel.lu...@gmail.com wrote:
 
 Helo,

 Just wondering if is possible to block SELECT queries that doesnt have
 a WHERE statement within.



signature.asc
Description: OpenPGP digital signature

Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[Arthur Fuller]

Tim,

I think you misunderstood the question. Daniel wants to block Select
queries that ask for all rwows, and permit only queries that ask for some
rows, as restricted by the Where clause.

Unfortunately, I don't think that can be done. But I'm not certain of that;
there might be a trick.

Arthur
www.artfulsoftware.com

On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote:

  select * from table where column=value means it will return only rows that
 match.  as long as you have proper indexing there should not be any issues.

 On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz 
 luis.daniel.lu...@gmail.com wrote:

RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;

[Martin Gainty]

Possibly run your constructed query thru a regex expression e.g.
String mydata = SELECT * from table WHERE ab;;
Pattern pattern = Pattern.compile('WHERE');
Matcher matcher = pattern.matcher(mydata);
if (matcher.find())
{
 //WHERE clause found proceed normally
}
else throw new Exception(WHERE clause not found);
Martin 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.


 Date: Sun, 23 Sep 2012 18:38:58 -0400
 Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table 
 WHERE...;
 From: fuller.art...@gmail.com
 To: pownall...@gmail.com
 CC: mysql@lists.mysql.com
 
 Tim,
 
 I think you misunderstood the question. Daniel wants to block Select
 queries that ask for all rwows, and permit only queries that ask for some
 rows, as restricted by the Where clause.
 
 Unfortunately, I don't think that can be done. But I'm not certain of that;
 there might be a trick.
 
 Arthur
 www.artfulsoftware.com
 
 On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote:
 
   select * from table where column=value means it will return only rows that
  match.  as long as you have proper indexing there should not be any issues.
 
  On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz 
  luis.daniel.lu...@gmail.com wrote:
 
 
  

Re: why does select * from table oder by indexed_field not use key?

[Reindl Harald]

the mysql query optimizer is somehow stupid

a simple query, order by with a indexed column and
you have to use where order_by_field0 - why the
hell is mysqld not happy that a key is on the field
used in order by?

mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
++-+--+--+---+--+-+--+--++
| id | select_type | table| type | possible_keys | key  | 
key_len | ref  | rows | Extra  |
++-+--+--+---+--+-+--+--++
|  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL | NULL  
  | NULL |2 | Using filesort |
++-+--+--+---+--+-+--+--++
1 row in set (0.00 sec)

mysql EXPLAIN SELECT * FROM cms1_quickbar_groups where qg_sort0 ORDER BY 
qg_sort ASC;
++-+--+---+---+-+-+--+--+-+
| id | select_type | table| type  | possible_keys | key | 
key_len | ref  | rows | Extra   |
++-+--+---+---+-+-+--+--+-+
|  1 | SIMPLE  | cms1_quickbar_groups | range | qbq_key   | qbq_key | 2 
  | NULL |2 | Using where |
++-+--+---+---+-+-+--+--+-+
1 row in set (0.00 sec)


Am 11.07.2012 02:39, schrieb Akshay Suryavanshi:
 The statement will do a Full table scan, because of the following things : 
 Not using Where clause, and selecting
 all columns (*) within the query. Filesort is used since no index is used, 
 use a where clause with condition on
 column which is indexed and notice the explain plan. Also you can retrieve 
 specific columns on which indexes are
 created to use the feature of Covering index.
 
 On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.net wrote:
 
 my reason for create a key on qg_sort was primary
 for this query - but why is here 'filesort' used?
 
 mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
 
 ++-+--+--+---+--+-+--+--++
 | id | select_type | table| type | possible_keys | key  | 
 key_len | ref  | rows | Extra  |
 
 ++-+--+--+---+--+-+--+--++
 |  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL | 
 NULL| NULL |2 | Using filesort |
 
 ++-+--+--+---+--+-+--+--++
 1 row in set (0.01 sec)
 -
 cms1_quickbar_groups | CREATE TABLE `cms1_quickbar_groups` (
   `qg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
   `qg_titel` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
   `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT 
 '',
   `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`qg_id`),
   KEY `qbq_key` (`qg_sort`)
 ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 
 COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1
 
 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm





signature.asc
Description: OpenPGP digital signature

Re: why does select * from table oder by indexed_field not use key?

[Ewen Fortune]

Hi,

On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald h.rei...@thelounge.net wrote:
 the mysql query optimizer is somehow stupid

Its not stupid - remember its not trying to find the best index,
rather its trying to find the least costly plan
to return the data in the quickest manner.

For the optimizer in this case it believes its faster to do a full
table scan with filesort rather than read from the index
and have to scan the entire table anyway.

Quick test shows it is indeed faster to do a full table scan.

mysql show profiles;
+--++--+
| Query_ID | Duration   | Query
|
+--++--+
|1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
|2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups ORDER BY qg_sort ASC |
+--++--+
2 rows in set (0.00 sec)


Cheers,

Ewen


 a simple query, order by with a indexed column and
 you have to use where order_by_field0 - why the
 hell is mysqld not happy that a key is on the field
 used in order by?

 mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
 ++-+--+--+---+--+-+--+--++
 | id | select_type | table| type | possible_keys | key  | 
 key_len | ref  | rows | Extra  |
 ++-+--+--+---+--+-+--+--++
 |  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL | 
 NULL| NULL |2 | Using filesort |
 ++-+--+--+---+--+-+--+--++
 1 row in set (0.00 sec)

 mysql EXPLAIN SELECT * FROM cms1_quickbar_groups where qg_sort0 ORDER BY 
 qg_sort ASC;
 ++-+--+---+---+-+-+--+--+-+
 | id | select_type | table| type  | possible_keys | key | 
 key_len | ref  | rows | Extra   |
 ++-+--+---+---+-+-+--+--+-+
 |  1 | SIMPLE  | cms1_quickbar_groups | range | qbq_key   | qbq_key | 
 2   | NULL |2 | Using where |
 ++-+--+---+---+-+-+--+--+-+
 1 row in set (0.00 sec)


 Am 11.07.2012 02:39, schrieb Akshay Suryavanshi:
 The statement will do a Full table scan, because of the following things : 
 Not using Where clause, and selecting
 all columns (*) within the query. Filesort is used since no index is used, 
 use a where clause with condition on
 column which is indexed and notice the explain plan. Also you can retrieve 
 specific columns on which indexes are
 created to use the feature of Covering index.

 On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.net wrote:

 my reason for create a key on qg_sort was primary
 for this query - but why is here 'filesort' used?

 mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
 
 ++-+--+--+---+--+-+--+--++
 | id | select_type | table| type | possible_keys | key  
 | key_len | ref  | rows | Extra  |
 
 ++-+--+--+---+--+-+--+--++
 |  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL 
 | NULL| NULL |2 | Using filesort |
 
 ++-+--+--+---+--+-+--+--++
 1 row in set (0.01 sec)
 -
 cms1_quickbar_groups | CREATE TABLE `cms1_quickbar_groups` (
   `qg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
   `qg_titel` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
   `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT 
 '',
   `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`qg_id`),
   KEY `qbq_key` (`qg_sort`)
 ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 
 COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1



 --

 Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / CISO / Software-Development
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/

 http://www.thelounge.net/signature.asc.what.htm

Re: why does select * from table oder by indexed_field not use key?

[Reindl Harald]

Am 11.07.2012 11:43, schrieb Ewen Fortune:
 Hi,
 
 On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald h.rei...@thelounge.net 
 wrote:
 the mysql query optimizer is somehow stupid
 
 Its not stupid - remember its not trying to find the best index,
 rather its trying to find the least costly plan
 to return the data in the quickest manner.
 
 For the optimizer in this case it believes its faster to do a full
 table scan with filesort rather than read from the index
 and have to scan the entire table anyway.
 
 Quick test shows it is indeed faster to do a full table scan.
 
 mysql show profiles;
 +--++--+
 | Query_ID | Duration   | Query
 |
 +--++--+
 |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
 cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
 |2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
 cms1_quickbar_groups ORDER BY qg_sort ASC |
 +--++--+
 2 rows in set (0.00 sec)

this may be true for small data where it does not matter at all
but if this would be a large table it would cause a lot of I/O



signature.asc
Description: OpenPGP digital signature

Re: why does select * from table oder by indexed_field not use key?

[Ananda Kumar]

column used in the order by caluse, should be the first column in the
select statement to make the index work

On Wed, Jul 11, 2012 at 3:16 PM, Reindl Harald h.rei...@thelounge.netwrote:



 Am 11.07.2012 11:43, schrieb Ewen Fortune:
  Hi,
 
  On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald h.rei...@thelounge.net
 wrote:
  the mysql query optimizer is somehow stupid
 
  Its not stupid - remember its not trying to find the best index,
  rather its trying to find the least costly plan
  to return the data in the quickest manner.
 
  For the optimizer in this case it believes its faster to do a full
  table scan with filesort rather than read from the index
  and have to scan the entire table anyway.
 
  Quick test shows it is indeed faster to do a full table scan.
 
  mysql show profiles;
 
 +--++--+
  | Query_ID | Duration   | Query
  |
 
 +--++--+
  |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
  cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
  |2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
  cms1_quickbar_groups ORDER BY qg_sort ASC |
 
 +--++--+
  2 rows in set (0.00 sec)

 this may be true for small data where it does not matter at all
 but if this would be a large table it would cause a lot of I/O

Re: why does select * from table oder by indexed_field not use key?

[Stephen Tu]

On Wed, Jul 11, 2012 at 5:46 AM, Reindl Harald h.rei...@thelounge.netwrote:

  mysql show profiles;
 
 +--++--+
  | Query_ID | Duration   | Query
  |
 
 +--++--+
  |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
  cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
  |2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
  cms1_quickbar_groups ORDER BY qg_sort ASC |
 
 +--++--+
  2 rows in set (0.00 sec)

 this may be true for small data where it does not matter at all
 but if this would be a large table it would cause a lot of I/O


While I agree with you that Ewen's microbenchmark is not the most
convincing, I do agree with his reasoning, and I encourage you to try
benchmarking both options on your dataset. Remember to flush both the OS
disk cache and the mysql buffer pool between runs, so that your benchmarks
are actually reflecting cold runs instead of partially warmed up runs.

So why do I believe no index is faster for your particular query? Well, a
secondary index (qbq_key in your case) is usually key/value pairs of the
form [index key, page ID pointing to tuple]. So if we answered your query
with qbq_key, we don't need to do a sort, *but* we'll need to do roughly
one disk seek for each key in the index (I'm assuming here that qg_sort
values don't have any strong correlation with qg_id, and that the pages for
the table aren't already in the buffer pool/OS disk cache).

Compare that with a case where we don't use an index, so we must do a
filesort. Since mysql has an optimization (
https://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html - read
the modified filesort algorithm section) which stores the entire tuple
(if under a threshold size controlled by max_length_for_sort_data, which is
1024 bytes by default which your schema seems to fall under) when sorting
instead of just the [sort key, page ID], the filesort can actually avoid
much of random disk seeks (since mergesort itself is a very sequential IO
heavy algorithm). So for this plan, you end up basically doing several
sequential IOs over the entire table (whereas the previous plan just had to
read the entire table once, albeit randomly). Most likely the mysql
optimizer has calculated that several sequential scans over the table are
much faster than a bunch of random disk seeks which reads the table at
once. In fact, I believe the general rule of thumb for DB optimizers is
that if you need to read more than 10% of a table from an index, you are
better off using a sequential scan.

Like I said, I encourage you to measure the performance to convince
yourself that mysql is actually doing the right thing.

why does select * from table oder by indexed_field not use key?

[Reindl Harald]

my reason for create a key on qg_sort was primary
for this query - but why is here 'filesort' used?

mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
++-+--+--+---+--+-+--+--++
| id | select_type | table| type | possible_keys | key  | 
key_len | ref  | rows | Extra  |
++-+--+--+---+--+-+--+--++
|  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL | NULL  
  | NULL |2 | Using filesort |
++-+--+--+---+--+-+--+--++
1 row in set (0.01 sec)
-
cms1_quickbar_groups | CREATE TABLE `cms1_quickbar_groups` (
  `qg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
  `qg_titel` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
  `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
  `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`qg_id`),
  KEY `qbq_key` (`qg_sort`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 
COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1



signature.asc
Description: OpenPGP digital signature

Re: why does select * from table oder by indexed_field not use key?

[Akshay Suryavanshi]

Hi,

The statement will do a Full table scan, because of the following things :
Not using Where clause, and selecting all columns (*) within the query.
Filesort is used since no index is used, use a where clause with condition
on column which is indexed and notice the explain plan. Also you can
retrieve specific columns on which indexes are created to use the feature
of Covering index.

On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald h.rei...@thelounge.netwrote:

 my reason for create a key on qg_sort was primary
 for this query - but why is here 'filesort' used?

 mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;

 ++-+--+--+---+--+-+--+--++
 | id | select_type | table| type | possible_keys | key  |
 key_len | ref  | rows | Extra  |

 ++-+--+--+---+--+-+--+--++
 |  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL |
 NULL| NULL |2 | Using filesort |

 ++-+--+--+---+--+-+--+--++
 1 row in set (0.01 sec)
 -
 cms1_quickbar_groups | CREATE TABLE `cms1_quickbar_groups` (
   `qg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
   `qg_titel` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
   `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
   `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`qg_id`),
   KEY `qbq_key` (`qg_sort`)
 ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
 COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1

Re: Commit commands with SELECT

[Luis Motta Campos]

Hello 

COMMIT statements may or may not force the database to call fflush() to flush 
your double-write to disk. This may or may not affect your performance, 
depending on your scale, traffic, and how much you're trying to squeeze your 
hardware. If you're working on the borderline like I am, benchmark, benchmark, 
benchmark.

My 0.02€.

Kind regards,
--
Luis Motta Campos
is a DBA, Foodie, and Photographer

On 9 Apr 2012, at 20:47, Karen Abgarian wrote:

 I vote 1) yes 2) no
 
 It could be result of the app developer's convenience to just wrap anything 
 they submit to the database in a transaction.   Selects are not transaction 
 but autocommit/commit do no harm.   That might be the thinking. 
 
 
 On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:
 
 We have an application with blocks of code that begin with setting 
 autocommit off, and end with a commit.  The code in between does only 
 selects, no updating.
 
 1)  Am I correct in thinking that the autocommit and commit statements 
 don't really accomplish anything useful?
 
 2)  If the autocommit and commit statements are unneeded, do they add 
 enough additional overhead that I should be concerned about them?
 
 Kay Rozeboom
 Information Technology Enterprise
 Iowa Department of Administrative Services
 Telephone: 515.281.6139   Fax: 515.281.6137
 Email:  kay.rozeb...@iowa.gov
 
 
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Commit commands with SELECT

[Stephen Tu]

Wrapping even just selects around a transaction absolutely matters,
depending if you care about isolation.

Consider the following two clients running on the same mysql instance, w/
--transaction_isolation=serializable. Suppose we have the following innodb
table:
CREATE TABLE FOO (i INTEGER, j INTEGER);

Client 1:
SELECT * FROM foo WHERE i = 0;
SELECT * FROM foo WHERE i = 0;

Client 2:
UPDATE foo SET j = 1 WHERE i = 0;

Suppose the table starts out with a single tuple (0, 0). Now, if client 1
and client 2 are running at the same time, wrapping client 1's select
statements with a BEGIN/COMMIT removes the possibility of the following
interleaving:

C1: SELECT * FROM foo WHERE i = 0;
C2: UPDATE foo SET j = 1 WHERE i = 0;
C1: SELECT * FROM foo WHERE i = 0;

Without the BEGIN/COMMIT, the interleaving above is completely valid. Now
to answer your questions.

 On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:
 
  We have an application with blocks of code that begin with setting
 autocommit off, and end with a commit.  The code in between does only
 selects, no updating.
 
  1)  Am I correct in thinking that the autocommit and commit
 statements don't really accomplish anything useful?


No, you need to reason about whether or not the select statements need to
run in isolation for correctness (like the above example).


  2)  If the autocommit and commit statements are unneeded, do they
 add enough additional overhead that I should be concerned about them?


I don't think you gain any overhead by using explicit transactions. For
instance, a single select statement (I believe) is really equivalent to

BEGIN; SELECT ...; COMMIT;

However, you do incur overhead in the sense that the longer your
transaction block is, the more time you spend holding (read) locks, and
thus excluding writers.

Hope that helps,
--
Stephen Tu

Commit commands with SELECT

[Rozeboom, Kay [DAS]]

We have an application with blocks of code that begin with setting autocommit 
off, and end with a commit.  The code in between does only selects, no updating.

1)  Am I correct in thinking that the autocommit and commit statements 
don't really accomplish anything useful?

2)  If the autocommit and commit statements are unneeded, do they add 
enough additional overhead that I should be concerned about them?

Kay Rozeboom
Information Technology Enterprise
Iowa Department of Administrative Services
Telephone: 515.281.6139   Fax: 515.281.6137
Email:  kay.rozeb...@iowa.gov

Re: Commit commands with SELECT

[Karen Abgarian]

I vote 1) yes 2) no

It could be result of the app developer's convenience to just wrap anything 
they submit to the database in a transaction.   Selects are not transaction but 
autocommit/commit do no harm.   That might be the thinking. 


On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:

 We have an application with blocks of code that begin with setting autocommit 
 off, and end with a commit.  The code in between does only selects, no 
 updating.
 
 1)  Am I correct in thinking that the autocommit and commit statements 
 don't really accomplish anything useful?
 
 2)  If the autocommit and commit statements are unneeded, do they add 
 enough additional overhead that I should be concerned about them?
 
 Kay Rozeboom
 Information Technology Enterprise
 Iowa Department of Administrative Services
 Telephone: 515.281.6139   Fax: 515.281.6137
 Email:  kay.rozeb...@iowa.gov
 
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Multi select Query help...

[Hal�sz S�ndor]

 2012/03/01 19:56 -0800, Don Wieland 
I do not get the same results. Am I missing something? Hopefully  
something simple ;-)

O, you are. You do not want GROUP_CONCAT in the subquery. It gives you the 
comma-separated string whereto you referred, which, as far as the IN goes, is 
only one string for comparing for equality. You want the IDs separate, not 
joined into one string.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Multi select Query help...

[Don Wieland]

Appreciate a little guidance here:

Background: I have an invoicing system. Invoices are generated and  
(invoice and Invoice Items) and Payments are generated (Payments and  
Payment Items). Payment items are amount of the Payment Total  
allocated to payoff open invoices. So I may have 3 open invoice for  
$100 each and I may generate one payment for $300 with 3 payment items  
for $100 each to pay off those 3 open invoices.


In most cases, clients will pay their own invoices off, but in rare  
cases another client will pay an invoice for that client (ie...  
spouse, parent, etc...) My client want me to some how display when the  
payee (or one of the payees - there can be multiple) of the invoice IS  
NOT the same client as the invoice being paid.


So I need to display a result that show a comma delineated string of  
payees OMITTING the invoice's client_id. I only want to show a result  
if one or more of the payees are different than the invoice's client_id.


So now with the mySQL queries that are working:

First of all, the client_id of the invoice I am querying on is 251719.

query 1 = select group_concat(payment_id) from tl_trans_pmt_items  
where inv_id = 1033911


This produces a string 1033882,1021630,1021632. These are parent  
Payment records which have the payee client_ids.


So if I run a query:

query 2 = select group_concat(client_id) FROM tl_transactions WHERE  
transaction_id IN (1033882,1021630,1021632)  AND client_id != 251719


This produces a string 251711,251713. These are the client_ids of  
the Payment records OMITTING the Invoice's client_id


So far this works fine. Now where I run into issues is where I try to  
combine these queries together:


query 3 = select group_concat(client_id) FROM tl_transactions WHERE  
transaction_id IN ((select group_concat(payment_id) from  
tl_trans_pmt_items where inv_id = 1033911))  AND client_id != 251719


I do not get the same results. Am I missing something? Hopefully  
something simple ;-)


Don





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: strange select/join/group by with rollup issue....

[Arthur Fuller]

I'm not sure your method isn't working, but try changing changing the to
date part to  '2012-02-08'  and see what you get.
HTH,
Arthur