Re: Basic SELECT help
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
*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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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: SELECT Help
Hi Travis, That query kind of gives me the desired result. However, if is showing me 1, 18, 11, 23, 3, 2010-11-14 17:18:17 record and not 2, 11, 10, 3, 6, 2010-12-20 22:17:13, which is when they changed teams. Any thoughts ? Cheers Neil On Thu, Feb 3, 2011 at 10:32 PM, Travis Ard travis_...@hotmail.com wrote: Something like this might help you find all of the times where your user_id switched to a different team_id: select team_id, user_id, min(last_changed) from (select home_team_id as team_id, home_user_id as user_id, last_changed from data union all select away_team_id as team_id, away_user_id as user_id, last_changed from data) s1 where s1.user_id = 3 group by team_id, user_id; -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, February 03, 2011 6:34 AM To: [MySQL] Subject: SELECT Help Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil
SELECT Help
Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil
RE: SELECT Help
Something like this might help you find all of the times where your user_id switched to a different team_id: select team_id, user_id, min(last_changed) from (select home_team_id as team_id, home_user_id as user_id, last_changed from data union all select away_team_id as team_id, away_user_id as user_id, last_changed from data) s1 where s1.user_id = 3 group by team_id, user_id; -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, February 03, 2011 6:34 AM To: [MySQL] Subject: SELECT Help Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT help.
Thank you very much to all who responded. I ended up using Shawn's solution, the others seem good as well. Thanks again. Have a great weekend. Richard [EMAIL PROTECTED] wrote: Try this: SELECT c_no , SUM(1) as total_tx , SUM(if(`date` = now() - interval 6 month,1,0)) as recent_tx FROM transactions_table GROUP BY c_no HAVING total_tx 4 and recent_tx = 0; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 01/05/2006 10:43:15 AM: - Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
SELECT help.
Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. Sincerely, Richard Reina A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: SELECT help.
- Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
3.23.54 Thanks. Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Richard Reina To: Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: SELECT help.
Try this: SELECT c_no , SUM(1) as total_tx , SUM(if(`date` = now() - interval 6 month,1,0)) as recent_tx FROM transactions_table GROUP BY c_no HAVING total_tx 4 and recent_tx = 0; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 01/05/2006 10:43:15 AM: - Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
Richard Reina wrote: Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? transactions_table | ID | C_NO |DATE | AMOUT | | 2901 | 387 | 2003-10-09 | 23.00 | Obviously my table has many more entries. Thank you for any help. Sincerely, Richard Reina A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower. Something like: SELECT C_NO FROM transactions_table GROUP BY C_NO HAVING COUNT(*) = 4 AND COUNT(DATE CURDATE() - INTERVAL 6 MONTH) = 0; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
This should work: select c_name, count(t1.id) as t_count from customers c inner join transactions t1 on c.c_no = t1.c_no left join transactions t2 on c.c_no = t2.c_no and t2.date '2005-06-05' where t2.id is null group by c.c_no having t_count 4; There may be more efficient way of doing this though, if your tables are very large. This might work too: select c_name, count(t.id) as t_count, max(t.date) as t_latest from customers c inner join transactions t on c.c_no = t.c_no group by c.c_no having t_count 4 and t_latest '2005-06-05'; HTH, James Harvard At 7:29 am -0800 5/1/06, Richard Reina wrote: Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table| | ID|C_NO|DATE | AMOUT| |2901 | 387|2003-10-09 | 23.00| Obviously my table has many more entries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
Richard, Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? Something like this? SELECT c_no, COUNT(c_no) AS cnt FROM transactions_table WHERE NOT EXISTS ( SELECT c_no FROM transaction_table WHERE DATEDIFF(NOW(),date)=182 ) GROUP BY c_no HAVING cnt 4; PB - Richard Reina wrote: Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|"2003-10-09"|23.00| Obviously my table has many more entries. Thank you for any help. Sincerely, Richard Reina A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT help
Hi, I am tring to do a select from 2 tables. Table1: sku title Table 2: sku feature SELECT table1.title, table2.feature FROM table1, table2 WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seems to work to some extint, but I am getting way too many results (more than 3). It's returning all combinations of sku and feauture even if they don't share the same sku. I modified the select to: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seemed to work almost correctly. I have some items in table2 that don't have a feature and therefor don't have a row associated with them. For example, if I have 3 items in each table, the above select works fine. If I have 3 items in table1 and 2 items in table2 the above query only gives me 2 results. table1 will always be fully populated and table2 might be missing some features. How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help
You want a LEFT JOIN: SELECT table1.title, table2.feature FROM table1 LEFT JOIN table2 USING (sku) WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC I strongly suggest picking up Paul DuBois' MySQL: http://www.kitebird.com/mysql-book/ Eamon Daly - Original Message - From: Grant Giddens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 28, 2005 6:00 PM Subject: SELECT help Hi, I am tring to do a select from 2 tables. Table1: sku title Table 2: sku feature SELECT table1.title, table2.feature FROM table1, table2 WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seems to work to some extint, but I am getting way too many results (more than 3). It's returning all combinations of sku and feauture even if they don't share the same sku. I modified the select to: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seemed to work almost correctly. I have some items in table2 that don't have a feature and therefor don't have a row associated with them. For example, if I have 3 items in each table, the above select works fine. If I have 3 items in table1 and 2 items in table2 the above query only gives me 2 results. table1 will always be fully populated and table2 might be missing some features. How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? Thanks, Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help
Gran Giddens writes: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC ... How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? This is a job for 'LEFT JOIN' :) Given this data from your described tables: mysql select * from table1; +--+---+ | sku | title | +--+---+ |1 | A | |2 | B | |3 | C | +--+---+ 3 rows in set (0.00 sec) mysql select * from table2; +--+-+ | sku | feature | +--+-+ |1 | a | |1 | aa | |2 | b | |2 | bb | |2 | bbb | +--+-+ 5 rows in set (0.00 sec) SELECT table1.title, table2.feature FROM table1 LEFT JOIN table2 using (sku) WHERE table1.sku in (1, 2, 3) ORDER BY FIELD(table1.sku, 1, 2, 3) ASC mysql SELECT table1.title, table2.feature - FROM table1 LEFT JOIN table2 using (sku) - WHERE table1.sku in (1, 2, 3) - ORDER BY FIELD(table1.sku, 1, 2, 3) ASC - ; +---+-+ | title | feature | +---+-+ | A | a | | A | aa | | B | bbb | | B | b | | B | bb | | C | NULL| +---+-+ 6 rows in set (0.04 sec) Take a look at the manual for 'LEFT JOIN' to see where I came up with this information. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select help
Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select help
Hey Rob, You're looking for a group by to allow mysql to aggregate over the IP's: SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY ip DESC limit 10; -Matt -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:03 PM To: [EMAIL PROTECTED] Subject: Select help Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
select count(*) as cnt group by ip order by cnt desc limit 10; rmck wrote: Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select help
Woops! Forget I said that, you wanted to order by the most occurrences. Sorry. SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY 2 DESC limit 10; Heh... I should learn to read one of these days... -Matt -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:03 PM To: [EMAIL PROTECTED] Subject: Select help Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
On Thu, 2004-07-01 at 10:03, rmck wrote: Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? Yes. You need to count the number of times an IP appears and sort by that count, then limit it: SELECT ip, COUNT(ip) as num FROM iptable GROUP BY ip ORDER BY num DESC LIMIT 10 -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
Hi, Ok. This is good!! Thank you! Zoli Egor Egorov [EMAIL PROTECTED] 2004-04-30 03:30 PM To: [EMAIL PROTECTED] cc: (bcc: Zoltan Gyurasits/GYO/COMP/PHILIPS) Subject:Re: SQL SELECT HELP Classification: [EMAIL PROTECTED] wrote: Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. You need something like: SELECT DISTINCT t3.id FROM table2 t2 INNER JOIN table1 t1 ON t1.value=t2.value RIGHT JOIN table1 t3 ON t1.id=t3.id WHERE t1.id IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
[EMAIL PROTECTED] wrote: Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. You need something like: SELECT DISTINCT t3.id FROM table2 t2 INNER JOIN table1 t1 ON t1.value=t2.value RIGHT JOIN table1 t3 ON t1.id=t3.id WHERE t1.id IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT HELP
Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Is this possible? Is there anyone who can help me to create this SELETC? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre, have a look at JOIN. This can solve your problem. Thomas Spahni On Fri, 30 Apr 2004, Andre MATOS wrote: Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Is this possible? Is there anyone who can help me to create this SELETC? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Hi Robert, the criteria for the record_1 and record_15 is that both are in the same table, but in different records and to find each one it is necessary to perform a WHERE clause. Let's I give you the real example: My problem is while inserting a new record in my table named ScanQuantification, I will need to update another table named Scan, IF a value from the field ScanQuantificationValue from table ScanQuantification is equal or greater than a calculate value. The calculate value comes from this formula: ( ( A + B ) / 2 + C) where: A is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 8 B is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 9 C is a value find from the field TrialBaseValue from table Trial This is easy to do using the PHP language. However I will have different Trials and each one has different formula. That's why I want to put the SELECT to work for me. Thanks Andre On Fri, 30 Apr 2004, Robert J Taylor wrote: Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
Hi, Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. Michael Stassen [EMAIL PROTECTED] 2004-04-28 06:13 PM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
I hope it should work: Select table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null OR if you want distinct IDs Select distinct table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null though I didn't test it, if it does (or doesn't) let me know Regards Nitin - Original Message - From: [EMAIL PROTECTED] To: Michael Stassen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 1:15 PM Subject: Re: SQL SELECT HELP Hi, Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. Michael Stassen [EMAIL PROTECTED] 2004-04-28 06:13 PM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
Hi, Thanx the answer! I have tried it, but didn't work correctly. The result was 1,2,3 :(( Nitin [EMAIL PROTECTED] 2004-04-29 09:54 AM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] Michael Stassen [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I hope it should work: Select table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null OR if you want distinct IDs Select distinct table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null though I didn't test it, if it does (or doesn't) let me know Regards Nitin - Original Message - From: [EMAIL PROTECTED] To: Michael Stassen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 1:15 PM Subject: Re: SQL SELECT HELP Hi, Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. Michael Stassen [EMAIL PROTECTED] 2004-04-28 06:13 PM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
SQL SELECT HELP
Hi, I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( Thank you in advanced, Zoli
Re: SQL SELECT HELP
[EMAIL PROTECTED] wrote: I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] 2004-04-28 12:10 PM To: [EMAIL PROTECTED] cc: (bcc: Zoltan Gyurasits/GYO/COMP/PHILIPS) Subject:Re: SQL SELECT HELP Classification: [EMAIL PROTECTED] wrote: I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select help - multiple where/limits
hi wondering whether someone can set me straight on whether it's possible to request a set of records from a single table with multiple conditions. for instance, a story table, containing id, title, text, section and published_date. what i would like is to retrieve is the 5 most recently published stories from each section (currently there are nine sections). so, do i have to do this in nine separate queries or can i do something like: SELECT id, title, text, sectioned, published_date FROM stories WHERE (section = 'events' order by published_date desc limit 5) and (section = 'features' order by published_date desc limit 5) etc... many thanks kris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help - multiple where/limits
Kris Burford [EMAIL PROTECTED] wrote: hi wondering whether someone can set me straight on whether it's possible to request a set of records from a single table with multiple conditions. for instance, a story table, containing id, title, text, section and published_date. what i would like is to retrieve is the 5 most recently published stories from each section (currently there are nine sections). so, do i have to do this in nine separate queries or can i do something like: SELECT id, title, text, sectioned, published_date FROM stories WHERE (section = 'events' order by published_date desc limit 5) and (section = 'features' order by published_date desc limit 5) If I've got you right you need UNION: (SELECT id, title, text, sectioned, published_date FROM stories WHERE section = 'events' ORDER BY published_date DESC LIMIT 5) UNION (SELECT id, title, text, sectioned, published_date FROM stories WHERE section = 'features' ORDER BY published_date DESC LIMIT 5); http://www.mysql.com/doc/en/UNION.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL select help required please
I am trying to create a html search results page with the following: SELECT * FROM tbl_allarticles WHERE (fld_headline LIKE'%userinput%' OR fld_summary LIKE'%userinput%' OR fld_body LIKE'%userinput%') AND fld_category LIKE 'catvalue' The above works fine, but the below code is giving me some jip (It is simply a command to look for dates between user inputted start and end dates, but it is not working) it just follows on from the above code: AND fld_reldate BETWEEN 'startdatevalue' AND 'enddatevalue' What problem are you having exactly? An error, or just not getting the results you expected? Maybe you need to format the date in mmdd format before giving it to MySQL. The problem I am having is that I am getting a Data type mismatch in criteria expression error. The way I understand this to work is the quotes around the start and end date values make these values variables which will be issued at runtime by the user when submitting the form on the previous page. Is that right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL select help required please
I am trying to create a html search results page with the following: SELECT * FROM tbl_allarticles WHERE (fld_headline LIKE'%userinput%' OR fld_summary LIKE'%userinput%' OR fld_body LIKE'%userinput%') AND fld_category LIKE 'catvalue' The above works fine, but the below code is giving me some jip (It is simply a command to look for dates between user inputted start and end dates, but it is not working) it just follows on from the above code: AND fld_reldate BETWEEN 'startdatevalue' AND 'enddatevalue' I have tried to enclose it with brackets in several different places, without brackets too. Any help would be appreciated. TIA Mat
Re: SQL select help required please
Matthew Stuart wrote: I am trying to create a html search results page with the following: SELECT * FROM tbl_allarticles WHERE (fld_headline LIKE'%userinput%' OR fld_summary LIKE'%userinput%' OR fld_body LIKE'%userinput%') AND fld_category LIKE 'catvalue' The above works fine, but the below code is giving me some jip (It is simply a command to look for dates between user inputted start and end dates, but it is not working) it just follows on from the above code: AND fld_reldate BETWEEN 'startdatevalue' AND 'enddatevalue' I have tried to enclose it with brackets in several different places, without brackets too. Any help would be appreciated. TIA Mat What problem are you having exactly? An error, or just not getting the results you expected? Maybe you need to format the date in mmdd format before giving it to MySQL. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select help
Hello I want to select from the table sum of logins for each day. For example: Date Logins 2004-01-22 10 2004-01-23 12 Any ideas if such select is possible? +--+--+ | Field| Type | +--+--+ | login_count | int(4) | | last_login | datetime | _ Rethink your business approach for the new year with the helpful tips here. http://special.msn.com/bcentral/prep04.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
Hi, I want to select from the table sum of logins for each day. Would this help: mysql select date_format(your_date_column, %Y-%m-%d), count(*) - from your_table - group by date_format(your_date_column, %Y-%m-%d); Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
- Original Message - From: Mike Mapsnac [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 23, 2004 11:42 PM Subject: Select help Hello I want to select from the table sum of logins for each day. Here's one way to do it. SELECT SUBSTRING(last_login, 1, 10) AS day, login_count FROM table GROUP BY day ORDER BY day ASC; For example: Date Logins 2004-01-22 10 2004-01-23 12 Any ideas if such select is possible? +--+--+ | Field| Type | +--+--+ | login_count | int(4) | | last_login | datetime | Mikael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT help
HI all! I want make the SELECT for this situation. Example: Twarehouse -- IDname --- 01Spring_WH 02Screw_WH 03Toll_WH Tparts -- IDname --- 01M3 screw 02M4 screw 03Hammer Tmovement -- part_ID incoming_IDoutgoing_ID quantity ---- - 01010210 0201031 0302015 I want build list about movement, with warehouse's names SELECT twarehouse.name, ??# incoming warehouse name twarehouse.name, ??# outgoing warehouse name tparts.name, tmovement.quantity FROM tparts, twarehouse INNER JOIN tmovement ON tmovement.part_ID=tparts.ID AND tmovement.incoming_ID=twarehouse.ID AND tmovement.outgoing_ID=twarehouse.ID Please help me, who can . How can I do?? I don't want make temorary table, if is possible Thank You!
RE: SELECT help
Try with : SELECT tparts.name, a.name, b.name, tmovement.quantity FROMtmovement inner join tparts on (tmovement.part_id = tparts.Id) inner join twarehouse a on ( tmovement.incoming_id = a.Id ) inner join twarehouse b on ( tmovement.outgoing_id = b.Id ) Ulises -Mensaje original- De: Gyurasits Zoltan [mailto:[EMAIL PROTECTED] Enviado el: Martes 18 de Febrero de 2003 04:11 PM Para: MYSQL Lista Asunto: SELECT help HI all! I want make the SELECT for this situation. Example: Twarehouse -- IDname --- 01Spring_WH 02Screw_WH 03Toll_WH Tparts -- IDname --- 01M3 screw 02M4 screw 03Hammer Tmovement -- part_ID incoming_IDoutgoing_ID quantity ---- - 01010210 0201031 0302015 I want build list about movement, with warehouse's names SELECT twarehouse.name, ??# incoming warehouse name twarehouse.name, ??# outgoing warehouse name tparts.name, tmovement.quantity FROM tparts, twarehouse INNER JOIN tmovement ON tmovement.part_ID=tparts.ID AND tmovement.incoming_ID=twarehouse.ID AND tmovement.outgoing_ID=twarehouse.ID Please help me, who can . How can I do?? I don't want make temorary table, if is possible Thank You!
Re: SELECT help
Hi This is working... Thank You Zoltan - Original Message - From: Cabanillas Dulanto, Ulises [EMAIL PROTECTED] To: MYSQL Lista [EMAIL PROTECTED] Sent: Friday, July 18, 2003 10:54 PM Subject: RE: SELECT help Try with : SELECT tparts.name, a.name, b.name, tmovement.quantity FROMtmovement inner join tparts on (tmovement.part_id = tparts.Id) inner join twarehouse a on ( tmovement.incoming_id = a.Id ) inner join twarehouse b on ( tmovement.outgoing_id = b.Id ) Ulises -Mensaje original- De: Gyurasits Zoltan [mailto:[EMAIL PROTECTED] Enviado el: Martes 18 de Febrero de 2003 04:11 PM Para: MYSQL Lista Asunto: SELECT help HI all! I want make the SELECT for this situation. Example: Twarehouse -- IDname --- 01Spring_WH 02Screw_WH 03Toll_WH Tparts -- IDname --- 01M3 screw 02M4 screw 03Hammer Tmovement -- -- -- part_ID incoming_IDoutgoing_ID quantity ---- - 01010210 0201031 0302015 I want build list about movement, with warehouse's names SELECT twarehouse.name, ??# incoming warehouse name twarehouse.name, ??# outgoing warehouse name tparts.name, tmovement.quantity FROM tparts, twarehouse INNER JOIN tmovement ON tmovement.part_ID=tparts.ID -- -- AND tmovement.incoming_ID=twarehouse.ID AND tmovement.outgoing_ID=twarehouse.ID Please help me, who can . How can I do?? I don't want make temorary table, if is possible Thank You! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select help
Hello list, I'm really new to mysql and databases in general. I have a select form that contains a very long list of options, and what I want to do is store the selected item as a number instead of the items name in order to speed up searches. My problem comes when a search is done and I can't figure out how to return the query with the items name not the number. Any suggestions and help is greatly appreciated. Dan Cox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help
I'm new, too, so someone correct me if I'm wrong, but... if you make it an ENUM field in a table you can store it using the value in the selection, retrieve it as the same value, and still get all the advantages of numeric storage. Todd On Sunday, July 6, 2003, at 02:38 PM, Dan Cox wrote: Hello list, I'm really new to mysql and databases in general. I have a select form that contains a very long list of options, and what I want to do is store the selected item as a number instead of the items name in order to speed up searches. My problem comes when a search is done and I can't figure out how to return the query with the items name not the number. Any suggestions and help is greatly appreciated. Dan Cox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help
Another option is to have another table with the item name and number using that as a lookup table. IMHO it all depends on the data if the select is static (or rarely changes) an enum would be best. If is changes a lot then I would use another table to store it in. -Michael I protect you, skin brother. -mriswith On Sun, 6 Jul 2003, Todd O'Bryan wrote: I'm new, too, so someone correct me if I'm wrong, but... if you make it an ENUM field in a table you can store it using the value in the selection, retrieve it as the same value, and still get all the advantages of numeric storage. Todd On Sunday, July 6, 2003, at 02:38 PM, Dan Cox wrote: Hello list, I'm really new to mysql and databases in general. I have a select form that contains a very long list of options, and what I want to do is store the selected item as a number instead of the items name in order to speed up searches. My problem comes when a search is done and I can't figure out how to return the query with the items name not the number. Any suggestions and help is greatly appreciated. Dan Cox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select help
Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
John, Looks like I'm first with the wrong answer again. This time for sure. How about: AND os.os_id 8 = 8 Where 8 is the value that you're looking for. -ms -Original Message- From: Michael Shulman [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:38 AM To: 'John Hoskins' Cc: '[EMAIL PROTECTED]' Subject: RE: select help No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
This one worked. Thank you. On Wed, 2 Apr 2003, Michael Shulman wrote: John, Looks like I'm first with the wrong answer again. This time for sure. How about: AND os.os_id 8 = 8 Where 8 is the value that you're looking for. -ms -Original Message- From: Michael Shulman [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:38 AM To: 'John Hoskins' Cc: '[EMAIL PROTECTED]' Subject: RE: select help No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
If you want to be a bit more generic you could do something like this: # store the desired OS ID into a variable SELECT @desired_id := os_id FROM os_table WHERE os_name = win nt; # now find the solutions that match with the os_id SELECT o.os_id, o.os_name, s.os_code, s.solution FROM os_table o, solution_table s WHERE (o.os_id s.os_code) = @desired_id; +---+-+-+-+ | os_id | os_name | os_code | solution| +---+-+-+-+ | 8 | win nt | 24 | nt and 2000 dun fix | | 8 | win nt | 255 | no-pay contact CSRs | +---+-+-+-+ 2 rows in set (0.01 sec) -- Jeff Shapiro | Starlight Spectacular Ride Webmaster| June 21st,2003 at midnight www.starlightspectacular.org | Benefiting the Trails Open Space Coalition On 4/2/03 at 15:40, John Hoskins spoke thusly: This one worked. Thank you. On Wed, 2 Apr 2003, Michael Shulman wrote: John, Looks like I'm first with the wrong answer again. This time for sure. How about: AND os.os_id 8 = 8 Where 8 is the value that you're looking for. -ms -Original Message- From: Michael Shulman [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:38 AM To: 'John Hoskins' Cc: '[EMAIL PROTECTED]' Subject: RE: select help No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help
Jeff Shapiro wrote: If you want to be a bit more generic you could do something like this: # store the desired OS ID into a variable SELECT @desired_id := os_id FROM os_table WHERE os_name = win nt; # now find the solutions that match with the os_id SELECT o.os_id, o.os_name, s.os_code, s.solution FROM os_table o, solution_table s WHERE (o.os_id s.os_code) = @desired_id; Why not combine them into a single intriguing non-equijoin? SELECT * FROM os_table o, solution_table s WHERE (o.os_id s.os_code) = o.os_id AND o.os_name = win nt; Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select help
Probably a simple query but, I need to find select a field with data that exists in one table but does not exist in a field in another table. example: table1.name table2.name --- bob john susan jane tom tom johnbob janejohn jane tom result should be = susan any help is appreciated -JH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select help
On 26-Feb-2003 John Hoskins wrote: Probably a simple query but, I need to find select a field with data that exists in one table but does not exist in a field in another table. example: table1.name table2.name --- bob john susan jane tom tom john bob jane john jane tom result should be = susan any help is appreciated look for 'LEFT JOIN' in the manual. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL: Select HELP!
select * from processo_arquivos order by DATE desc limit 10 -Original Message- From: Felipe Moreno - MAILING LISTS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 12:42 PM To: [EMAIL PROTECTED] Subject: MySQL: Select HELP! Importance: High Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL: Select HELP!
Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL: Select HELP!
SELECT * FROM processo_arquivos ORDER BY DATE DESC LIMIT 0,10 -Original Message- From: Felipe Moreno - MAILING LISTS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 1:42 PM To: [EMAIL PROTECTED] Subject: MySQL: Select HELP! Importance: High Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL: Select HELP!
Try this: SELECT DATE, COD FROM processo_arquivos ORDER BY DATE DESC LIMIT 10 Unfortunately, that puts them in reverse order. There's probably a better way, with a more sophisticated use of the LIMIT keyword, that puts them in the right order. -Alex On Tuesday, December 3, 2002, at 10:41 AM, Felipe Moreno - MAILING LISTS wrote: Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT HELP
How do I use the select feature in this situation. I have a total of 5 fields that deal with names.. Three of the five are completely separate, and use one field for both first and last name. the last two fields are the first and last name of the person submitting the information.. When I do a select if someone searches for joe or smith it will pull up the info on all fields. but if someone searches for joe smith it will ONLY pull up the entries that match in the first three fields. it is imperative that I make this work. I can't change the layout of the table at this point, but I'm sure there's a way to combine two fields in the select and try to match two fields in the table to one field in the search form.. Here's a sample of the code.. (SELECT * FROM newsibsdatabase WHERE (firstname LIKE '%$searchname%' OR lastname LIKE '%$searchname%' OR adopteename LIKE '%$searchname%') now, can I add in a option that is something like this OR (firstname lastname LIKE '%$searchname$') how would this work.. Thanks in advance.. Jake. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT HELP
I believe you could use CONCAT(firstname, ' ', lastname) LIKE '%$searchname%' if you leave the space out of the middle it probably won't work, and you might want to trim firstname lastname to make sure no leading or trailing spaces exist. Hope this helps, P -Original Message- From: *Himerus* [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 10:09 PM To: [EMAIL PROTECTED] Subject: SELECT HELP How do I use the select feature in this situation. I have a total of 5 fields that deal with names.. Three of the five are completely separate, and use one field for both first and last name. the last two fields are the first and last name of the person submitting the information.. When I do a select if someone searches for joe or smith it will pull up the info on all fields. but if someone searches for joe smith it will ONLY pull up the entries that match in the first three fields. it is imperative that I make this work. I can't change the layout of the table at this point, but I'm sure there's a way to combine two fields in the select and try to match two fields in the table to one field in the search form.. Here's a sample of the code.. (SELECT * FROM newsibsdatabase WHERE (firstname LIKE '%$searchname%' OR lastname LIKE '%$searchname%' OR adopteename LIKE '%$searchname%') now, can I add in a option that is something like this OR (firstname lastname LIKE '%$searchname$') how would this work.. Thanks in advance.. Jake. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT help
Hi, This is my first posting, although i've been signed up to the list for a while. My problem is this. Table A (5000 rows) ID, NAME, SCORE Table B (1000 rows) ID, NAME, SCORE I want all records from Table A and those from Table B where they match, for this i'm using a right join. However, there are rows in Table B which don't match any in Table A, but i need to include these as well. Any help would be appreciated. Ben. Sir, I haven't seen an answer to your question, so here's one way of getting what you want. What you want is basically the union of three groups of rows: the rows from A and B that match, the rows from A that don't match B, and the rows from B that don't match A. Your outer join returns the first two groups. The last group is returned by a difference query: the rows in B that don't match any of the rows in A (B - A). Since MySQL doesn't yet support UNION, you will have to load the result tables from both queries into another table, and then SELECT * from that table. I have a description of the standard difference query on my website; http:/users.starpower.net/rjhalljr, click on MySQL on the sidebar, click on SQL, and look for the difference query topic. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT help
Hi, This is my first posting, although i've been signed up to the list for a while. My problem is this. Table A (5000 rows) ID, NAME, SCORE Table B (1000 rows) ID, NAME, SCORE I want all records from Table A and those from Table B where they match, for this i'm using a right join. However, there are rows in Table B which don't match any in Table A, but i need to include these as well. Any help would be appreciated. Ben. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT help
Sir, haven't you posted this before? It looks familiar. You can't apply an aggregate function to an entire table if the SELECT statement has a GROUP BY clause. The aggregate function will return totals for the groups, not for the entire table. Bob Hall Can someone help me combine this statement ... SELECT d.*, b.invoice_id FROM domain_info d LEFT JOIN billing_info b ON d.domain_id=b.domain_id WHERE billing_cycle = '12' OR billing_cycle = 'Z' OR billing_cycle = 'C' GROUP BY domain_name with this statement ... SELECT sum(ammount_due) FROM billing_info WHERE domain_id = $domain_id AND status = 0 The code is for billing software to manage the accounts of my server clients. In the first statement I am selecting information about each domain from the table domain_info that are billable for this billing cycle. Also in the first statement I am checking to see if any invoice_ids exist for the domain in the table billing_info. If there are no ids, the code knows to add a setup fee to the charge. If there are ids, then they domain has already been charges in the past and been billed for the one-time setup fee. The second statment gets the entire ammount owed from the table billing_info. Anytime a domain incurs a charge (positive) or pays an invoice (neagtive), a line is added with this in the ammount_due collumn. I can't seem to get an error free result ... can anyone offer suggestions on some working codes that gets both of these statments into one? Thanks! Nick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT help
Can someone help me combine this statement ... SELECT d.*, b.invoice_id FROM domain_info d LEFT JOIN billing_info b ON d.domain_id=b.domain_id WHERE billing_cycle = '12' OR billing_cycle = 'Z' OR billing_cycle = 'C' GROUP BY domain_name with this statement ... SELECT sum(ammount_due) FROM billing_info WHERE domain_id = $domain_id AND status = 0 The code is for billing software to manage the accounts of my server clients. In the first statement I am selecting information about each domain from the table domain_info that are billable for this billing cycle. Also in the first statement I am checking to see if any invoice_ids exist for the domain in the table billing_info. If there are no ids, the code knows to add a setup fee to the charge. If there are ids, then they domain has already been charges in the past and been billed for the one-time setup fee. The second statment gets the entire ammount owed from the table billing_info. Anytime a domain incurs a charge (positive) or pays an invoice (neagtive), a line is added with this in the ammount_due collumn. I can't seem to get an error free result ... can anyone offer suggestions on some working codes that gets both of these statments into one? Thanks! Nick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select HELP from HERE where HELP_NEEDED = TRUE !
hi, i have a web serverrunning redhat linux 7 i installed mysql 3.23.30 two weeks ago and it has begun to cause problems i get a "lost connection during query" every half a minute, basicly my web site is dead i tried instaling the 3.23.32 ( stable you call it! ) and i get the same response i'm in desparate need for help... !!! H E L P ! ! ! 10q amir [EMAIL PROTECTED]
