public inbox for [email protected]
help / color / mirror / Atom feedselect items based on 2 columns
4+ messages / 4 participants
[nested] [flat]
* select items based on 2 columns
@ 2022-08-08 08:24 Shaozhong SHI <[email protected]>
2022-08-08 09:32 ` Re: select items based on 2 columns Frank Streitzig <[email protected]>
2022-08-08 16:17 ` Re: select items based on 2 columns Pierre Chevalier <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Shaozhong SHI @ 2022-08-08 08:24 UTC (permalink / raw)
To: pgsql-sql <[email protected]>
The following is the type of data:
id id_b in out
51 57 false false
51 42 true false
51 32 false false
51 76 false true
51 49 true false
How to do the following:
select id from tbl if in is true and out is true are found?
Regards,
David
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: select items based on 2 columns
2022-08-08 08:24 select items based on 2 columns Shaozhong SHI <[email protected]>
@ 2022-08-08 09:32 ` Frank Streitzig <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Frank Streitzig @ 2022-08-08 09:32 UTC (permalink / raw)
To: Shaozhong SHI <[email protected]>; +Cc: pgsql-sql <[email protected]>
Am Mon, Aug 08, 2022 at 09:24:14AM +0100 schrieb Shaozhong SHI:
> The following is the type of data:
>
> id id_b in out
> 51 57 false false
> 51 42 true false
> 51 32 false false
> 51 76 false true
> 51 49 true false
>
>
> How to do the following:
>
> select id from tbl if in is true and out is true are found?
Untested:
select t.id from tbl t where t.in and t.out;
Best regards,
Frank
>
> Regards,
>
> David
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: select items based on 2 columns
2022-08-08 08:24 select items based on 2 columns Shaozhong SHI <[email protected]>
@ 2022-08-08 16:17 ` Pierre Chevalier <[email protected]>
2022-08-08 16:30 ` Re: select items based on 2 columns Carl Sopchak <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Pierre Chevalier @ 2022-08-08 16:17 UTC (permalink / raw)
To: [email protected]
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 │
└────┘
À+
Pierre
Le 08/08/2022 à 10:24, Shaozhong SHI a écrit :
> The following is the type of data:
>
> id id_b in out
> 51 57 false false
> 51 42 true false
> 51 32 false false
> 51 76 false true
> 51 49 true false
>
>
> How to do the following:
>
> select id from tbl if in is true and out is true are found?
>
> Regards,
>
> David
--
Pierre Chevalier
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: select items based on 2 columns
2022-08-08 08:24 select items based on 2 columns Shaozhong SHI <[email protected]>
2022-08-08 16:17 ` Re: select items based on 2 columns Pierre Chevalier <[email protected]>
@ 2022-08-08 16:30 ` Carl Sopchak <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Carl Sopchak @ 2022-08-08 16:30 UTC (permalink / raw)
To: [email protected]
<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:[email protected]">
<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@mail.gmail.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>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-08-08 16:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-08 08:24 select items based on 2 columns Shaozhong SHI <[email protected]>
2022-08-08 09:32 ` Frank Streitzig <[email protected]>
2022-08-08 16:17 ` Pierre Chevalier <[email protected]>
2022-08-08 16:30 ` Carl Sopchak <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox