Comparisons Sub-Select Issues 
Author Message
 Comparisons Sub-Select Issues
Hello,

How would I do a comparison sub select ie
select this where that>=(*select something where whatever='exactly');

Currently if I try to run a query in this format I get nothing back but if
i remove the > I get an exact match which I do not want

Can I do this???

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Mon, 12 Jan 2004 03:15:56 GMT
 Comparisons Sub-Select Issues

I do queries of this sort all of the time.  If I used
the following script to create a couple of tables:

--BEGIN SQL script--
DROP TABLE birthdays;
DROP TABLE key_dates;

BEGIN;

CREATE TABLE birthdays (
       username     varchar(20) PRIMARY KEY,
       dt           date
);

INSERT INTO birthdays (username, dt) VALUES ('jason',
'1971-10-14');
INSERT INTO birthdays (username, dt) VALUES
('kaelynn', '1976-02-29');
INSERT INTO birthdays (username, dt) VALUES
('brooklyn', '1999-01-19');
INSERT INTO birthdays (username, dt) VALUES
('zachary', '2001-05-05');

CREATE TABLE key_dates (
       name         varchar(20) PRIMARY KEY,
       dt           date
);

INSERT INTO key_dates (name, dt) VALUES ('first date',
'1997-02-15');
INSERT INTO key_dates (name, dt) VALUES
('anniversary', '1997-05-10');
INSERT INTO key_dates (name, dt) VALUES ('Y2K',
'2000-01-01');

COMMIT;
--END SQL script--

I could then query the database like so:

processdata=> SELECT * FROM birthdays WHERE dt >=
(SELECT dt FROM key_dates WHERE name = 'first date');
 username |     dt    
----------+------------
 brooklyn | 1999-01-19
 zachary  | 2001-05-05
(2 rows)

In fact, this works with all kinds of values.  If this
doesn't work for you then you have a serious problem
with your installation of PostgreSQL.

Or, more likely, I have simply misunderstood what your
problem is...

Hope this is helpful,
Jason


Quote:
> Hello,

> How would I do a comparison sub select ie
> select this where that>=(*select something where
> whatever='exactly');

> Currently if I try to run a query in this format I
> get nothing back but if
> i remove the > I get an exact match which I do not
> want

> Can I do this???

> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?

> http://www.postgresql.org/search.mpl

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl



Mon, 12 Jan 2004 05:37:54 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

2. Type, sub-type, and sub-sub-type database design

3. Master Parts/Sub Parts/Sub-Sub Parts

4. subselect bug (was Re: [GENERAL] DBLink: interesting issue)

5. SQL 7.0 Performance issue with Clustering and Sub nets

6. *Simple* date comparison issue???

7. 6.5 bug corr sub select

8. Select Case Grouping Sub Query

9. Slow Query - 160 fields are sub SELECT statements

10. sub select / join question

11. Update w/ sub-query? Update w/ select into

12. Selects with subselects??!


 
Powered by phpBB® Forum Software