A query problem

#1

I have table I which when has G, F, L
I can query to get a list:

select L, U from I where G='111';

This gives me a list in ascending order like:
L U
---- ----
1000 1049
2000 2010
etc.

I can now manually loop (outside of MySql) over the list from table I
and build a query on table II such that after putting in these example
values it evaluates to

select V from II where V>=1000 and V<=1049
UNION ALL
select V from II where V>=2000 and V<=2010
etc.

this is the query for the list of all the permitted values of V.

What I would _really_ like to do is build a single query such that it
pulls each of the lower and upper bounds from table I and puts them in
properly.

That means that supposing table II has the following values for V:
1023, 1037, 1075, 2007 that the query fragment above would return
1023, 1037, 2007. IOW, the value 1075 is not within proper bounds of
upper and lower so it is omitted.

Any ideas?

--
Shelly

#2

On 8ÔÂ13ÈÕ, ÉÏÎç3Ê±22·Ö, sheldonlg <sheldo... (AT) thevillages (DOT) net> wrote:

Quote:

I have table I which when has G, F, L
I can query to get a list:

select L, U from I where G='111';

This gives me a list in ascending order like:
L U
---- ----
1000 1049
2000 2010
etc.

I can now manually loop (outside of MySql) over the list from table I
and build a query on table II such that after putting in these example
values it evaluates to

select V from II where V>=1000 and V<=1049
UNION ALL
select V from II where V>=2000 and V<=2010
etc.

this is the query for the list of all the permitted values of V.

What I would _really_ like to do is build a single query such that it
pulls each of the lower and upper bounds from table I and puts them in
properly.

That means that supposing table II has the following values for V:
1023, 1037, 1075, 2007 that the query fragment above would return
1023, 1037, 2007. IOW, the value 1075 is not within proper bounds of
upper and lower so it is omitted.

Any ideas?

--
Shelly

select V from II where (v>=1000 and v<=1049) or (V>=2000 and v<=2010)
I'am sorry I can't test and verify ,may be it can solve

#3

On 2011-08-12 21:22, sheldonlg wrote:

Quote:

I have table I which when has G, F, L
I can query to get a list:

select L, U from I where G='111';

This gives me a list in ascending order like:
L U
---- ----
1000 1049
2000 2010
etc.

This may just be an unfortunate choice of words, but is a common
misunderstanding so ...

If you assume that 1000 comes before 2000 in the result you will be
disappointed sooner or later. The result is *unordered*, it can be
returned in what ever order. The only way you can be sure of a specific
order is by adding an order by clause to your query:

select L, U from I where G='111'
order by L;

In addition, the result of a query is not a list, it's a table.

Quote:

I can now manually loop (outside of MySql) over the list from table I
and build a query on table II such that after putting in these example
values it evaluates to

select V from II where V>=1000 and V<=1049
UNION ALL
select V from II where V>=2000 and V<=2010
etc.

select II.V
from I
join II
on II.V between I.L and I.U
where I.G = '111';

/Lennart

[...]

#4

On 8/13/2011 3:51 AM, wheat wrote:

Quote:

On 8ÔÂ13ÈÕ, ÉÏÎç3Ê±22·Ö, sheldonlg<sheldo... (AT) thevillages (DOT) net> wrote:
I have table I which when has G, F, L
I can query to get a list:

select L, U from I where G='111';

This gives me a list in ascending order like:
L U
---- ----
1000 1049
2000 2010
etc.

I can now manually loop (outside of MySql) over the list from table I
and build a query on table II such that after putting in these example
values it evaluates to

select V from II where V>=1000 and V<=1049
UNION ALL
select V from II where V>=2000 and V<=2010
etc.

this is the query for the list of all the permitted values of V.

What I would _really_ like to do is build a single query such that it
pulls each of the lower and upper bounds from table I and puts them in
properly.

That means that supposing table II has the following values for V:
1023, 1037, 1075, 2007 that the query fragment above would return
1023, 1037, 2007. IOW, the value 1075 is not within proper bounds of
upper and lower so it is omitted.

Any ideas?

--
Shelly

select V from II where (v>=1000 and v<=1049) or (V>=2000 and v<=2010)
I'am sorry I can't test and verify ,may be it can solve

No, that won't work because I have to have a query that will have N or
those (>= and <=) sets where both the number of sets and their values
are determined by another selection.

--
Shelly

#5

"sheldonlg" <sheldonlg (AT) thevillages (DOT) net> wrote

Quote:

I have table I which when has G, F, L
I can query to get a list:

select L, U from I where G='111';

This gives me a list in ascending order like:
L U
---- ----
1000 1049
2000 2010
etc.

I can now manually loop (outside of MySql) over the list from table I and
build a query on table II such that after putting in these example values
it evaluates to

select V from II where V>=1000 and V<=1049
UNION ALL
select V from II where V>=2000 and V<=2010
etc.

this is the query for the list of all the permitted values of V.

What I would _really_ like to do is build a single query such that it
pulls each of the lower and upper bounds from table I and puts them in
properly.

That means that supposing table II has the following values for V:
1023, 1037, 1075, 2007 that the query fragment above would return
1023, 1037, 2007. IOW, the value 1075 is not within proper bounds of
upper and lower so it is omitted.

Any ideas?

If you will forgive the join style, then:

select V from Table2, Table1
where G=111
and V >= L and V <= U

I've substituted "Table1" for "I" and "Table2" for "II" as I think its
slightly clearer.
--
Brian Cryer
http://www.cryer.co.uk/brian

#6

On 8/17/2011 10:04 AM, Brian Cryer wrote:

Quote:

"sheldonlg" <sheldonlg (AT) thevillages (DOT) net> wrote in message
news:j23ut5$po5$1 (AT) dont-email (DOT) me...
I have table I which when has G, F, L
I can query to get a list:

select L, U from I where G='111';

This gives me a list in ascending order like:
L U
---- ----
1000 1049
2000 2010
etc.

I can now manually loop (outside of MySql) over the list from table I
and build a query on table II such that after putting in these example
values it evaluates to

select V from II where V>=1000 and V<=1049
UNION ALL
select V from II where V>=2000 and V<=2010
etc.

this is the query for the list of all the permitted values of V.

What I would _really_ like to do is build a single query such that it
pulls each of the lower and upper bounds from table I and puts them in
properly.

That means that supposing table II has the following values for V:
1023, 1037, 1075, 2007 that the query fragment above would return
1023, 1037, 2007. IOW, the value 1075 is not within proper bounds of
upper and lower so it is omitted.

Any ideas?

If you will forgive the join style, then:

select V from Table2, Table1
where G=111
and V >= L and V <= U

I've substituted "Table1" for "I" and "Table2" for "II" as I think its
slightly clearer.

No, that won't work for the same reason as I answered another responder.
We don't know how many TableXs there are and we don't know the limits
of L and U in each one.

The answer was to use the BETWEEN syntax in the on condition. That
worked fine.

Also, I never use implicit joins. I always use explicit joins.
Further, even if I were to "forgive" the implicit joins, you did not
specify where the G was coming from and, furthermore, V is present in
both tables.

--
Shelly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

A query problem

comp.databases.mysql comp.databases.mysql

A query problem - 08-12-2011 , 02:22 PM

Re: A query problem - 08-13-2011 , 02:51 AM

Re: A query problem - 08-13-2011 , 02:53 AM

Re: A query problem - 08-13-2011 , 06:23 AM

Re: A query problem - 08-17-2011 , 09:04 AM

Re: A query problem - 08-17-2011 , 02:33 PM