| From: | Carl Sopchak <carl(at)sopchak(dot)me> |
|---|---|
| To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
| Subject: | Re: select items based on 2 columns |
| Date: | 2022-08-08 16:30:50 |
| Message-ID: | 7f08717a-6784-46aa-a22c-50d295e1fdeb@sopchak.me |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>You can also quote column names that are SQL keywords, so "in"
and "out" would also work, but I also agree that it's not great
practice. However, IMHO, IN and OUT doesn't tell you a lot. In
and out of what? Inbound/outbound? In_water / out_of_water? You
get the idea. Using clear column names helps prevent misuse
(intentional or not).<br>
</p>
<div class="moz-cite-prefix">On 8/8/22 12:17, Pierre Chevalier
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:d74da674-0902-026e-8a63-a2b24086c9af(at)free(dot)fr">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<div class="moz-cite-prefix">
<pre>Hello,
Short answer:
SELECT id FROM tbl WHERE in and out;
BUT this will never work as is.
Longer answer:
First of all, it would be better not to name fields with word having a meaning in SQL: "in" is not accepted as a valid field name.
>From now on, to get rid of this problem, I renamed the in and out fields by appending an "x" at the end: "in" becomes "inx" and "out" becomes "outx".
Here is the complete answer; I added some data matching your criteria:
-- Create the table:
CREATE TEMPORARY TABLE tbl (id integer, id_b integer, inx boolean, outx boolean);
-- Put some data into it:
INSERT INTO tbl (id, id_b, inx, outx) VALUES
(51, 57, false, false),
(51, 42, true, false),
(51, 32, false, false),
(51, 76, false, true),
(51, 49, true, false),
(51, 47, true, true);
-- Select id where inx is true and outx is true:
SELECT id FROM tbl WHERE inx and outx;
-- Output:
┌────┐
│ id │
├────┤
│ 51 │
└────┘</pre>
</div>
<div class="moz-cite-prefix"> </div>
<div class="moz-cite-prefix">
<pre>À+
Pierre
</pre>
</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">Le 08/08/2022 à 10:24, Shaozhong SHI
a écrit :<br>
</div>
<blockquote type="cite"
cite="mid:CA+i5JwYCPBDcenq_godJqtjApLyOeJHxo-Joova3wrAYtM6QOA(at)mail(dot)gmail(dot)com">
<meta http-equiv="content-type" content="text/html;
charset=UTF-8">
<div dir="ltr">The following is the type of data:
<div><br>
</div>
<div>id id_b in out</div>
<div>51 57 false false</div>
<div>51 42 true false</div>
<div>51 32 false false</div>
<div>51 76 false true</div>
<div>51 49 true false</div>
<div><br>
</div>
<div><br>
</div>
<div>How to do the following:</div>
<div><br>
</div>
<div>select id from tbl if in is true and out is true are
found?</div>
<div><br>
</div>
<div>Regards,</div>
<div><br>
</div>
<div>David</div>
</div>
</blockquote>
<p><br>
</p>
<pre class="moz-signature" cols="72">--
Pierre Chevalier
</pre>
</blockquote>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2022-08-09 21:10:57 | Re: Possible bug (or at least unexpected behavior) |
| Previous Message | Pierre Chevalier | 2022-08-08 16:17:50 | Re: select items based on 2 columns |