[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: sub selects
From:       "Kevin Fries" <kfries () kurant ! com>
Date:       2003-04-30 16:08:42
[Download RAW message or body]

Daniel,
There is *some* problem with your nested select.  But your post confuses
me.  You're saying there's a syntax error,
But the quoted text 'SELECT locationID from locations...' does not occur
in the SELECT statement which you say 
is causing the problem.  I suspect you've got a typo in your e-mail.

If I read you correctly, you're saying that the "locations" column in
shotlist might contain a string which is a concatenated list of values
from the locations table ?  Is this correct?
Thus, maybe the Locations table has rows:
Locationid	Description
1		California
2		New York
3		Washington

Let's start with the simple case, where the shotlist.locations field
just contains one location.  The following seems like it should do what
you want...

SELECT * from shotlist where locations IN (select locationID from
locations)

If you're saying that you have *some* number of those locationID values
in any particular record of shotlist, then
the problem will be much harder to solve.  I cant' think of a way to ask
for that match-up.  Does this represent shotlist?

ShotDescription	ShotDate	locations
---------------------------------------
Something		3/3/2003	1
Something else	3/4/2003	1,2
Something more	3/5/2003	3,2
Something too	3/6/2003	4,7,8,9

If this is a good description of your table, then we're talking about
the same thing.  It's going to be very hard to correlate all the
locationid's in this locations field as belonging to the locations
table.

Kevin

-----Original Message-----
From: Daniel Rossi [mailto:daniel.rossi@sbs.com.au] 
Sent: Tuesday, April 29, 2003 8:13 PM
To: mysql@lists.mysql.com
Subject: sub selects


hi am am having a few problems with my sub selects , i have never tried
this before but assuming i now got myself a 4.1 server @ work i may
aswell try it

SELECT * from shotlist where locations IN (select locationID,location
from locations)

here is the query

basically i have a field in shotlist called locations , i have a table
called locations and a field in their called location , heh

anyway the fields in locations would look 1,2,3 for the corresponding
multiple location id's 

here is what mysql returns

You have an error in your SQL syntax.  Check the manual that corresponds
to your MySQL server version for the right syntax to use near 'SELECT
locationID FROM locations ) LIMIT 0, 30' at line 1

any help would be great



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]