Select clause in JOIN statement

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Select clause in JOIN statement
3+ messages / 3 participants
[nested] [flat]

* Select clause in JOIN statement
@ 2013-06-13 22:40  JORGE MALDONADO <jorgemal1960@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: JORGE MALDONADO @ 2013-06-13 22:40 UTC (permalink / raw)
  To: pgsql-sql

Is it valid to specify a SELECT statement as part of a JOIN clause?

For example:

SELECT table1.f1, table1.f2 FROM table1
INNER JOIN
(SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 =
table_aux.f1

Respectfully,
Jorge Maldonado

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

* Re: Select clause in JOIN statement
@ 2013-06-13 23:10  Luca Vernini <lucazeo@gmail.com>
  parent: JORGE MALDONADO <jorgemal1960@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Luca Vernini @ 2013-06-13 23:10 UTC (permalink / raw)
  To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql

It works.
Also consider views.

Just used this on a my db:

SELECT * FROM tblcus_customer
INNER JOIN
( SELECT * FROM tblcus_customer_status WHERE status_id > 0) AS b
ON tblcus_customer.status = b.status_id


You can even join with a function result.

Regards,

Luca.

2013/6/14 JORGE MALDONADO <jorgemal1960@gmail.com>:
> Is it valid to specify a SELECT statement as part of a JOIN clause?
>
> For example:
>
> SELECT table1.f1, table1.f2 FROM table1
> INNER JOIN
> (SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 =
> table_aux.f1
>
> Respectfully,
> Jorge Maldonado


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



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

* Re: Select clause in JOIN statement
@ 2013-06-14 07:14  Andreas Joseph Krogh <andreak@officenet.no>
  parent: Luca Vernini <lucazeo@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Andreas Joseph Krogh @ 2013-06-14 07:14 UTC (permalink / raw)
  To: pgsql-sql

<div>På fredag 14. juni 2013 kl. 01:10:51, skrev Luca Vernini &lt;<a href="mailto:lucazeo@gmail.com" target="_blank">lucazeo@gmail.com</a>&gt;:</div>

<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="display:inline; font-family: monospace; font-size: 12px;">It works.<br>
Also consider views.<br>
<br>
Just used this on a my db:<br>
<br>
SELECT * FROM tblcus_customer<br>
INNER JOIN<br>
( SELECT * FROM tblcus_customer_status WHERE status_id &gt; 0) AS b<br>
ON tblcus_customer.status = b.status_id</div>
</blockquote>

<div> </div>

<div>This query is the same as a normal JOIN:</div>

<div>
<style type="text/css"></style>
<pre class="western" style="background: #ffffff; border: none; padding: 0in">
<font color="#000080"><font face="DejaVu Sans Mono"><b>SELECT</b> </font></font><font color="#000000"><font face="DejaVu Sans Mono">*</font></font>
<font face="DejaVu Sans Mono"><font color="#000080"><b>FROM </b></font><font color="#000000">tblcus_customer</font></font>
<font color="#000000">    </font><font color="#000080"><font face="DejaVu Sans Mono"><b>INNER JOIN</b></font></font>
<font color="#000080">    </font><font color="#000000"><font face="DejaVu Sans Mono">tblcus_customer_status b</font></font>
<font color="#000000">        </font><font color="#000080"><font face="DejaVu Sans Mono"><b>ON </b></font></font><font color="#000000"><font face="DejaVu Sans Mono">tblcus_customer.status = b.status_id </font></font><font color="#000080"><font face="DejaVu Sans Mono"><b>AND </b></font></font><font color="#000000"><font face="DejaVu Sans Mono">b.status_id &gt; </font></font><font color="#0000ff"><font face="DejaVu Sans Mono">0</font></font>
</pre>
or</div>

<div>
<style type="text/css"></style>
<pre class="western" style="background: #ffffff; border: none; padding: 0in">
<font color="#000080"><font face="DejaVu Sans Mono"><b>SELECT</b></font> </font><font color="#000000"><font face="DejaVu Sans Mono">*</font></font>
<font face="DejaVu Sans Mono"><font color="#000080"><b>FROM </b></font><font color="#000000">tblcus_customer</font></font>
<font color="#000000">    </font><font color="#000080"><font face="DejaVu Sans Mono"><b>INNER JOIN</b></font></font>
<font color="#000080">    </font><font color="#000000"><font face="DejaVu Sans Mono">tblcus_customer_status b</font></font>
<font color="#000000">        </font><font color="#000080"><font face="DejaVu Sans Mono"><b>ON </b></font></font><font color="#000000"><font face="DejaVu Sans Mono">tblcus_customer.status = b.status_id</font></font>
<font face="DejaVu Sans Mono"><font color="#000080"><b>WHERE </b></font><font color="#000000">b.status_id &gt; </font><font color="#0000ff">0</font></font></pre>
But you can JOIN on SELECTs selecting arbitrary stuff.</div>

<div> </div>

<div class="origo-email-signature">--<br>
Andreas Joseph Krogh &lt;andreak@officenet.no&gt;      mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br;
Public key: http://home.officenet.no/~andreak/public_key.asc</div;

<div> </div>


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

end of thread, other threads:[~2013-06-14 07:14 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-06-13 22:40 Select clause in JOIN statement JORGE MALDONADO <jorgemal1960@gmail.com>
2013-06-13 23:10 ` Luca Vernini <lucazeo@gmail.com>
2013-06-14 07:14   ` Andreas Joseph Krogh <andreak@officenet.no>

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