How to Use Multiple "With"

Can I use multiple with?

Try:

With DependencedIncidents AS
(
    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
    (
        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 
        CROSS JOIN [Incident] AS X
            WHERE
                patindex('%' + A.[Col] + '%', X.[SQL]) > 0
    ) AS INC
),
lalala AS
(
    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
    (
        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 
        CROSS JOIN [Incident] AS X
            WHERE
                patindex('%' + A.[Col] + '%', X.[SQL]) > 0
    ) AS INC
)

And yes, you can reference common table expression inside common table expression definition. Even recursively. Which leads to some very neat tricks.

Can we have multiple WITH AS in single sql - Oracle SQL

You can do this as:

WITH abc AS( select
             FROM ...)
, XYZ AS(select
         From abc ....) /*This one uses "abc" multiple times*/
  Select 
  From XYZ....   /*using abc, XYZ multiple times*/

How to use multiple WITH statements in one PostgreSQL query?

Per the other comments the second Common Table Expression [CTE] is preceded by a comma not a WITH statement so

WITH cte1 AS (SELECT...)
, cte2 AS (SELECT...)
SELECT *
FROM
    cte1 c1
    INNER JOIN cte2 c2
    ON ........

In terms of your actual query this syntax should work in PostgreSql, Oracle, and sql-server, well the later typically you will proceed WITH with a semicolon (;WTIH), but that is because typically sql-server folks (myself included) don't end previous statements which need to be ended prior to a CTE being defined...

Note however that you had a second syntax issue in regards to your WHERE statement. WHERE date IN table_2 is not valid because you never actually reference a value/column from table_2. I prefer INNER JOIN over IN or Exists so here is a syntax that should work with a JOIN:

WITH table_1 AS (
SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date
)

, table_2 AS (
SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date
)

SELECT * 
FROM
     table_1 t1
     INNER JOIN 
     table_2 t2
     ON t1.date = t2.date
;

If you want to keep the way you had it which typically EXISTS would be better than IN but to to use IN you need an actual SELECT statement in your where.

SELECT * 
FROM
     table_1 t1
WHERE t1.date IN (SELECT date FROM table_2);

IN is very problematic when date could potentially be NULL so if you don't want to use a JOIN then I would suggest EXISTS. AS follows:

SELECT * 
FROM
     table_1 t1
WHERE EXISTS (SELECT * FROM table_2 t2 WHERE t2.date = t1.date);

To use multiple with statement by union all

The syntax is

With l1 ( a ) as ( Select ... )
   , l2 ( a ) as ( ... )
  Select ... From ...
  Union
  Select ... From ...

AWS Athena (Presto) - multiple WITH statements

have you tried with a as ( ) , b as () select * from a,b ?

Use multiple WITH tablename AS (…) statements using Postgres version 9.3

When you use CTE you only need to write with in the first CTE and use , comma to connect multiple CTE.

with currentyeargrade as ( 
      select ....
      from (...
             ...)t),
previousyeargrade as(
      select ....
      from (...
             ...)y)
select *
from currentyeargrade cyg
inner join previousyeargrade pvg on pvg.userid = cyg.userid

Here is a sample for you

CREATE TABLE T(col int);

insert into T values (1);

Query 1:

with currentyeargrade as  (SELECT * FROM T), 
previousyeargrade as (SELECT * FROM T)
select *
from previousyeargrade cyg
CROSS JOIN currentyeargrade pvg 

Results:

| col | col |
|-----|-----|
|   1 |   1 |

sql query with multiple where statements

You need to consider that GROUP BY happens after the WHERE clause conditions have been evaluated. And the WHERE clause always considers only one row, meaning that in your query, the meta_key conditions will always prevent any records from being selected, since one column cannot have multiple values for one row.

And what about the redundant meta_value checks? If a value is allowed to be both smaller and greater than a given value, then its actual value doesn't matter at all - the check can be omitted.

According to one of your comments you want to check for places less than a certain distance from a given location. To get correct distances, you'd actually have to use some kind of proper distance function (see e.g. this question for details). But this SQL should give you an idea how to start:

SELECT items.* FROM items i, meta_data m1, meta_data m2
    WHERE i.item_id = m1.item_id and i.item_id = m2.item_id
    AND m1.meta_key = 'lat' AND m1.meta_value >= 55 AND m1.meta_value <= 65
    AND m2.meta_key = 'lng' AND m2.meta_value >= 20 AND m2.meta_value <= 30

How to use multiple conditions, including selecting on quantile in Python

You can use & or | to join the conditions

import numpy as np

df['Distance_Below_100'] = np.where(df['Population_density'].gt(df['Population_density'].quantile(0.75)) & df['Distance'].lt(100), 1, 0)

print(df)

    Row  Population_density  Distance  Distance_Below_100
0     1                 400        50                   0
1     2                 500        30                   0
2     3                 300        40                   0
3     4                 200       120                   0
4     5                 500        60                   0
5     6                1000        50                   1
6     7                3300        30                   1
7     8                 500        90                   0
8     9                 700       100                   0
9    10                1000       110                   0
10   11                 900       200                   0
11   12                 850        30                   0

---

Related Topics