If there are two, you will return two.
On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com
wrote:
Hi
With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
return two records for the record_id 3 ? Is it possible ?
Cheers
Neil
--
Bier met
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
Cheers
Neil
On Tue, Sep 21, 2010 at 4:40 PM, Johan De
I don't think that'll work, no. Why would you want to return duplicate data
? The whole point of an RDBMS is to *avoid* duplicate data :-)
On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com
wrote:
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a
table. I think in this case I need to change my design .
On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote:
I don't think that'll work, no. Why would you want to return duplicate data
? The
On 21/09/2010 16:44, Tompkins Neil wrote:
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
No, that isn't
Hi Neil, all!
Tompkins Neil wrote:
Hi
With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
return two records for the record_id 3 ? Is it possible ?
This is a case where you may safely use natural language and logic. The
command is
SELECT all fields FROM the
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Tuesday, September 21, 2010 11:48 AM
To: Johan De Meersman
Cc: [MySQL]
Subject: Re: SELECT WHERE IN help
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a
table. I think
Your best bet would be to dynamically build the field string using whatever you are coding the front end on. A simple configuration
paramter would tell you how many fields there are.
Now, if you don't need to retain your current table structure, I would recommend switching it to a name/value
I don't know if this can be done purely in SQL.
Programmatically, you could do something like
SHOW COLUMNS FROM tablename LIKE vector%;
That would give you the names (and types, which you can ignore) of the
desired columns. Then you can programmatically build up the list of columns.
Regards,
-Original Message-
From: Ephraim Dan [mailto:[EMAIL PROTECTED]
Sent: 27 February 2005 15:44
To: mysql@lists.mysql.com
Subject: select ... where bigint_col = 'big_value' doesn't work?
Hi,
We are migrating to mysql 4.1 (from 4.0). We have lots of BIGINT
columns, and lots of
AM Thomas wrote:
Hi there,
I'll answer your questions below, but I should tell you that it looks
like even three or four joins seem to make this too slow.
Theoretically, the user could select up to 15 or so GoalNumber values,
12 for Grade, and about 20 possibilities for Subject - clearly it
Such bounty of comments! Thank you, Michael Stassen.
I see how my logic was faulty, and that a more correct solution would
indeed be faster. Thanks for pointing that out and not making me feel
like too much of an idiot :) I'll try the revised solution. I am
clueful about join making
I am guessing the long duration is caused by having to do complete table
scans. How big is your dataset?
What about creating another index in resource_goals that includes
GoalNumber and ResourceID? Perhaps even Subject, Grade, and
NumericGrade As I learned just a couple days ago, making
Jeremy Cole wrote:
Hi,
Thanks, but unless I'm missing something, that will return the same
as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource
records 1,2,3, and 4 - too many matches. Am I missing something?
How about this:
SELECT resources.id, resources.title
FROM resources
LEFT
AM Thomas wrote:
Guarded exclamations of success!
This seems to be working for me so far - thank you! Here's an actual
example of a working query on my database; the field names are a little
different (sorry, I was trying to make my earlier example more
readable). The main change, though,
Hi there,
I'll answer your questions below, but I should tell you that it looks like
even three or four joins seem to make this too slow. Theoretically, the
user could select up to 15 or so GoalNumber values, 12 for Grade, and
about 20 possibilities for Subject - clearly it wouldn't be
Try this
Select *
from resources, goals
where resources.ID = goals.RESOURCE_ID
and (SUBJECT=English
and GRADE=1)
OR
(SUBJECT=English
and GRADE=2);
-Original Message-
From: AM Thomas [mailto:[EMAIL PROTECTED]
Sent: Sunday,
Thanks, but unless I'm missing something, that will return the same as
SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records
1,2,3, and 4 - too many matches. Am I missing something?
- AM
On Mon, 14 Feb 2005 12:30:44 -0600, Gordon [EMAIL PROTECTED] wrote:
Try this
Select *
from
Hi,
Thanks, but unless I'm missing something, that will return the same as
SUBJECT=English and (GRADE=1 or GRADE=2), which is resource
records 1,2,3, and 4 - too many matches. Am I missing something?
How about this:
SELECT resources.id, resources.title
FROM resources
LEFT JOIN goals AS goal_a
Guarded exclamations of success!
This seems to be working for me so far - thank you! Here's an actual
example of a working query on my database; the field names are a little
different (sorry, I was trying to make my earlier example more readable).
The main change, though, is that I did
Have a look at the manual page for EXISTS, you appear to need something like
SELECT * FROM resources AS r
WHERE EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=1 AND subject='English'
)
AND EXISTS (
SELECT resource_id FROM goals AS g
WHERE
AM Thomas wrote:
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I have two tables, resources and goals.
resources
Except that he/she is using 4.0, which doesn't support subqueries.
Michael
Peter Brawley wrote:
Have a look at the manual page for EXISTS, you appear to need something
like
SELECT * FROM resources AS r
WHERE EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=1
You need to change your INNER JOIN to a LEFT JOIN
SELECT
trucks.id, sum(history.time_sec) as total_seconds
FROM
trucks
LEFT JOIN
history
ON trucks.id = history_truckid
GROUP BY
trucks.id
ORDER BY
total_seconds desc
One other issue ---IMHO, the
Thanks, the LEFT JOIN worked.
I do have a question though, why is it considered best practice to list
all non-aggregated columns ( I assume you mean columns from trucks.*)
in the GROUP BY statement? I ask because I am interested in fast,
secure, standards-compliant code, I'm just not always
Because not doing so violates the SQL standard. Allowing you to
included non aggregated columns in the SELECT list is a non standard
MySQL extension to the SQL language. You will get an error in other
products, such as oracle, where you will get a xxx is not a group by
expression error.
At 15:40 -0800 1/19/03, Melissa Stranzl wrote:
I am trying to get my program to be searchable by
date, but it doesn't work. I get an error message in
perl, that follows the code I included. Any
suggestions are greatly appreciated.
PERL
--
@currenttime= localtime();
$y= $currenttime
So...
Select n.uid, n.name from names_table n
LEFT JOIN exclude ON n.uid = exclude.n_uid
WHERE exclude.n_uid IS NULL;
OK... this works, but I thought I would go a bit
further...
I have added a field in the exclude-table, cat_id.
This shows which catalogue the names have been
excluded from.
I
So...
Select n.uid, n.name from names_table n
LEFT JOIN exclude ON n.uid = exclude.n_uid
WHERE exclude.n_uid IS NULL;
OK... this works, but I thought I would go a bit
further...
I have added a field in the exclude-table, cat_id.
This shows which catalogue the names have been
excluded from.
I
http://www.mysql.com/doc/en/JOIN.html
mysql SELECT table1.* FROM table1
-LEFT JOIN table2 ON table1.id=table2.id
-WHERE table2.id IS NULL;
So...
Select n.uid, n.name from names_table n
LEFT JOIN exclude ON n.uid = exclude.n_uid
WHERE exclude.n_uid IS NULL;
Michelle de Beer wrote:
I have two tables. One with names and one for
excluding certain names. Exclude-table contains the
uid for the name excluded.
If I want to see which names has been excluded, this
query does the job:
Select n.uid, n.name from names_tables n, exclude
WHERE n.uid =
SELECT n.uid, n.name from names_tables n LEFT OUTER JOIN exclude
ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL
-Original Message-
From: Michelle de Beer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 3:22 PM
To: mysql list
Subject: Select Where !=
I have
Hi,
I don't think it is possible with out specifing the field names.
But we can get the soultion in the following way
If we have tabel calles 'test' with fields namely field1 and field2 then
you can get all the records that conatin 'c' with the following query
select * from
yes that gives the right results but i ask if i can do the same thing
without enumerating all fields like this:
SELECT * FROM [tablename] WHERE Field1 LIKE '%text%' OR Field2 LIKE '%text%'
OR ...
What i want is to do the same thing without knowing what are the field
names or how many fields
SELECT DISTINCT u.userid, IF( ul.listid, true, false)
FROM user u LEFT OUTER JOIN userlist ul ON u.userid = ul.userid
AND ul.listid = listid;
-Original Message-
From: David Otton [mailto:[EMAIL PROTECTED]]
[EMAIL PROTECTED] writes: [snipped fscking ML bullshit]
database,sql,query,table
David Otton writes:
Users that belong to some lists, but not the one I'm interested in :
this is the one that I'm stuck on. Any thoughts? Suggestions?
SELECT DISTINCT userid FROM userlist WHERE
Hi.
On Mon, Sep 10, 2001 at 02:38:56PM -0700, [EMAIL PROTECTED] wrote:
[...]
I need to find, for each user, whether they are a member of list n :
++--+
| userid | memberoflist |
++--+
| 1 | true |
| 2 |false |
| 3 |
I figured out the answer. It is:
SELECT * FROM users WHERE added (CURRENT_TIMESTAMP()
- INTERVAL 24 hour);
--- Andrew Tomazos [EMAIL PROTECTED] wrote:
I am running MySQL 3.23.39.
I have a column called added of type
timestamp(14)
in a table called users.
I set added to NULL on
On 31-Aug-2001 Jeremiah T. Folsom-Kovarik wrote:
Weird things have just started happening in at least one table of my MySQL
3.22.32 database. The symptoms are like this:
mysql describe orders;
+--+--+--+-+-+---+
| Field| Type
Thanks for your quick response! More below..
mysql select contact_info, expired from orders where order_num = '1';
+--+-+
| contact_info | expired |
+--+-+
| NULL | NULL|
+--+-+
^^
On 31-Aug-2001 Jeremiah T. Folsom-Kovarik wrote:
Thanks for your quick response! More below..
snip
Plus, all this just started happening yesterday, after everything worked
fine for many days. That leads me to believe there is a problem with a
corrupt table or something. How about
You have to select a certain row, there is no way to say all rows that are
NOT an int.
At 04:33 PM 1/16/2001 -0500, Brian Kaney wrote:
Is there a way to SELECT FROM table WHERE field_type IS (NOT) 'something'.
For example, 'something' could be TIMESTAMP, INT, FLOAT, AUTO_INCREMENT,
etc...I
42 matches
Mail list logo