pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Strange Join question
2+ messages / 2 participants
[nested] [flat]

* Strange Join question
@ 2003-01-17 17:28  Noah Silverman <noah@allresearch.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Noah Silverman @ 2003-01-17 17:28 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have a challenging (for me) SQL question:

Two tables
(Note: these are fictitious, the real tables actually make sense, so no 
need to re-design our table structure)

Table 1
id | name | count
------------------------
1 |   foo    |  10
1 |   foo    |  20
2 |  bar     |  100


Table 2
id  | f1  | f2  | t1ref
-----------------------
1  | 10  |  20 | 1
2  | 50  | 40  | 2


The question:

I want to do the following select:
select table2.f1, table1.name   from table1,table2 where table1.id = 
table 2.id and table2.id = 2;

The problem is that I really only need the name from table2 returned 
once.  With this query, I get two records back.  Clearly this is 
because of the join that I am doing.  Is there a different way to 
perform this join, so that I only get back ONE record from table1 that 
matches?

Thanks,

-Noah




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Strange Join question
@ 2003-01-17 17:38  Patrick Hatcher <PHatcher@macys.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Patrick Hatcher @ 2003-01-17 17:38 UTC (permalink / raw)
  To: "Noah Silverman <noah" <noah@allresearch.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org


Can you do
select table2.f1, table1.name
from table1,table2
where table1.id =
table 2.id and table2.id = 2
GROUP BY table2.f1, table1.name;


Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM




                                                                                                                                 
                    Noah Silverman                                                                                               
                    <noah@allresearch.com>             To:     pgsql-performance@postgresql.org                                  
                    Sent by:                           cc:                                                                       
                    pgsql-performance-owner@post       Subject:     [PERFORM] Strange Join question                              
                    gresql.org                                                                                                   
                                                                                                                                 
                                                                                                                                 
                    01/17/2003 09:28 AM                                                                                          
                                                                                                                                 
                                                                                                                                 




Hi,

I have a challenging (for me) SQL question:

Two tables
(Note: these are fictitious, the real tables actually make sense, so no
need to re-design our table structure)

Table 1
id | name | count
------------------------
1 |   foo    |  10
1 |   foo    |  20
2 |  bar     |  100


Table 2
id  | f1  | f2  | t1ref
-----------------------
1  | 10  |  20 | 1
2  | 50  | 40  | 2


The question:

I want to do the following select:
select table2.f1, table1.name   from table1,table2 where table1.id =
table 2.id and table2.id = 2;

The problem is that I really only need the name from table2 returned
once.  With this query, I get two records back.  Clearly this is
because of the join that I am doing.  Is there a different way to
perform this join, so that I only get back ONE record from table1 that
matches?

Thanks,

-Noah


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)







^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-01-17 17:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-17 17:28 Strange Join question Noah Silverman <noah@allresearch.com>
2003-01-17 17:38 Re: Strange Join question Patrick Hatcher <PHatcher@macys.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox