MySQL MULTIPLES INNER JOIN

PHOTO

Fri Nov 13 2020 21:36:50 GMT+0000 (UTC)

Saved by @mvieira #sql

SELECT 
    orderNumber,
    orderDate,
    customerName,
    orderLineNumber,
    productName,
    quantityOrdered,
    priceEach
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
INNER JOIN products 
    USING (productCode)
INNER JOIN customers 
    USING (customerNumber)
ORDER BY 
    orderNumber, 
    orderLineNumber;

https://www.mysqltutorial.org/mysql-inner-join.aspx/

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

SQL

@mvieira

MySQL MULTIPLES INNER JOIN How to Use EXISTS, UNIQUE, DISTINCT, and OVERLAPS in SQL Statements - dummies postgresql - SQL OVERLAPS PostgreSQL Joins: Inner, Outer, Left, Right, Natural with Examples PostgreSQL Joins: A Visual Explanation of PostgreSQL Joins PL/pgSQL Variables ( Format Dates ) The Ultimate Guide to PostgreSQL Date By Examples Data Type Formatting Functions PostgreSQL - How to calculate difference between two timestamps? | TablePlus Date/Time Functions and Operators PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines CASE Statements in PostgreSQL - DataCamp SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN PostgreSQL DESCRIBE TABLE Quick and best way to Compare Two Tables in SQL - DWgeek.com sql - Best way to select random rows PostgreSQL - Stack Overflow PostgreSQL: Documentation: 13: 70.1. Row Estimation Examples Faster PostgreSQL Counting How to Add a Default Value to a Column in PostgreSQL - PopSQL How to Add a Default Value to a Column in PostgreSQL - PopSQL SQL Subquery - Dofactory SQL IN - SQL NOT IN - JournalDev DROP FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs SQL : Multiple Row and Column Subqueries - w3resource PostgreSQL: Documentation: 9.5: CREATE FUNCTION PostgreSQL CREATE FUNCTION By Practical Examples datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow database - Oracle order NULL LAST by default - Stack Overflow PostgreSQL: Documentation: 9.5: Modifying Tables PostgreSQL: Documentation: 14: SELECT

javascript - Import sql file in node.js and execute against PostgreSQL - Stack Overflow

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

mysql FIND_IN_SET equivalent to postgresql

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

Merge tables in GBQ

MERGE my-dataset.dimension_table as MAIN using
my-dataset.temporary_table as TEMP
on MAIN.PRD_SK = TEMP.PRD_SK
when matched then
UPDATE SET
MAIN.PRD_CATEGORY = TEMP.PRD_CATEGORY
when not matched then
INSERT VALUES(TEMP.PRD_SK, TEMP. PRD_ID, TEMP. PRD_SK, TEMP.
PRD_CATEGORY)

#sql #bigquery #cdd

get all pages where a session hit accounts

with account_session_ste as (
SELECT 
*
FROM `table`
where exists(select 1 from unnest(hits) h 
            where regexp_contains(h.page.hostname, r'signin.account.gov.uk'))
and _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY))
)
-- group by 1,2)
select 
fullVisitorId AS user_id,
hits.page.hostname as hostname,   
hits.page.pagePath as pagePath,
hits.hitNumber as hit_number 
-- *
from account_session_ste, unnest(hits) as hits

#sql #bigquery #cdn

GA CDN model data

DECLARE Counter INT64;

BEGIN TRANSACTION;
    SET Counter = 0;
    CREATE TEMP TABLE tmp
    (
    dte date,
    days INT64,
    users INT64
    );
    
    WHILE Counter < 28 DO
        INSERT INTO tmp

        with dte as (
            SELECT dte
            FROM unnest(generate_date_array(date('2021-02-01'), date('2021-12-30'))) dte
        ),
        ids as (
            SELECT clientId,
            PARSE_DATE("%Y%m%d", date) as dte
            FROM `govuk-bigquery-analytics.87773428.ga_sessions_2021*`
        WHERE NOT device.operatingSystem = "iOS"
        )

        SELECT dte.dte as dte, max(Counter) as days, count( distinct ids.clientId) AS users
        FROM dte, ids
        WHERE ids.dte BETWEEN DATE_SUB(dte.dte, INTERVAL Counter DAY) and dte.dte
        group by 1
        order by 1 asc;

        SET Counter = Counter + 1;

    END WHILE;
COMMIT TRANSACTION;
--select * from tmp


SELECT * FROM
  (SELECT * FROM tmp)
  PIVOT(SUM(users) FOR days IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27))
  order by 1 asc

#sql #postgres #duplicate

Postgres query check duplicate data

select "field1", "field2", count(*)
from "tableName"
group by "field1", "field2"
HAVING count(*) > 1

#split #sql #large-sql

SIMPLE WAY SPLIT LARGE SQL FILE

### MAKE DIR
mkdir split && cd split

### SPLIT FILE
split -l 1000 /<path>/output-google.sql /<path>/split/split-

### CHANGE EXTENTION
ls | xargs -I % mv % %.sql

#sql #mysql #mysql5.7

Create a MySQL 5.7 database with charset UTF-8

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create queue table

CREATE TABLE dbo.my_queue
(
    id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    created_at SMALLDATETIME NOT NULL,
    popped_at SMALLDATETIME NOT NULL,
    errors VARCHAR(MAX) NULL,
    payload NVARCHAR(MAX) NOT NULL
);

select * from (select * from table order by data_column asc limit count(*)/2) 
order by data_column desc limit 1;

Build Pass through query using Regex to replace Form fields in query

Private Sub Select_Sector()

Dim rs As DAO.Recordset

Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")

Dim qdef As QueryDef
Set qdef = getCurrentDb.QueryDefs("qry_Select_Sector")
qdef.Connect = CurrentDb.TableDefs("BOCClientIndex").Connect


RegEx.Pattern = "IIf\(\[ServiceStatus\]=3,30,20\)\)=([0-9]+)"
qdef.SQL = RegEx.Replace(qdef.SQL, "IIf([ServiceStatus]=3,30,20))=" & [Forms]![MainMenu_Services]![SelectedStatusIndicator])

RegEx.Pattern = "\(View_qryServiceProviderOrganisationalStructure\.SectorCode\)=([0-9]+)"
qdef.SQL = RegEx.Replace(qdef.SQL, "(View_qryServiceProviderOrganisationalStructure.SectorCode)=" & [Forms]![MainMenu_Services]![SectorCode])


'For Testing purposes only - Do not use in production code
Set rs = qdef.OpenRecordset

Dim i As Long
For i = 0 To rs.Fields.Count - 1
        Debug.Print rs.Fields(i).Name,
    Next
    rs.MoveFirst
    Do Until rs.EOF
        Debug.Print
        For i = 0 To rs.Fields.Count - 1
            Debug.Print rs.Fields(i).value,
        Next
        rs.MoveNext
    Loop
End Sub

  
create or replace function decode_url(url text)
  returns text as
$BODY$
DECLARE result text;
BEGIN
    if url isnull then
        return null;
    end if;

    BEGIN
        with str AS (
            select
                   case when url ~ '^%[0-9a-fA-F][0-9a-fA-F]'
                   then array['']
                   end
            || regexp_split_to_array(url, '(%[0-9a-fA-F][0-9a-fA-F])+', 'i') plain,

            array(select (regexp_matches(url, '((?:%[0-9a-fA-F][0-9a-fA-F])+)', 'gi'))[1]) encoded
            )

        select string_agg(plain[i] || coalesce(convert_from(decode(replace(encoded[i], '%',''), 'hex'), 'utf8'),''),'')
        from str, (select generate_series(1, array_upper(encoded, 1) + 2) i FROM str) serie
        into result;

    EXCEPTION WHEN OTHERS THEN
        raise notice 'failed: %', url;
        return url;
    END;

    return coalesce(result, url);

END;

$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT;

#sql #postgres #columns

table_has_columns

CREATE OR REPLACE
FUNCTION table_has_column(tablename TEXT,
OUT res boolean)
 RETURNS boolean

AS $func$
-- DECLARE res boolean DEFAULT FALSE;

BEGIN
SELECT
    (count(constraint_name)>0)::boolean AS res
INTO
    res
FROM
    information_schema.table_constraints
WHERE
    table_name = tablename
    AND constraint_type = 'PRIMARY KEY';
END;

$func$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION table_has_pk(tablename TEXT,
OUT res boolean) RETURNS boolean AS $func$ 
res boolean DEFAULT FALSE;

BEGIN
SELECT
    (count(constraint_name)>0)::boolean AS res
INTO
    res
FROM
    information_schema.table_constraints
WHERE
    table_name = tablename
    AND constraint_type = 'PRIMARY KEY';
END;

$func$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION table_has_column(tablename TEXT,
columnname TEXT,
OUT res boolean)
 RETURNS boolean
 
AS $func$ 

BEGIN
SELECT
    (count(column_name) > 0)::boolean AS res
INTO
    res
FROM
    information_schema.columns
WHERE
    table_name = tablename
    AND column_name = columnname;
END;

$func$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION table_has_columns(tablename TEXT,
VARIADIC columnname TEXT) RETURNS boolean AS $func$
 DECLARE res boolean DEFAULT FALSE;

DECLARE
  x TEXT;

res boolean DEFAULT TRUE;

BEGIN
  FOREACH x IN ARRAY columnname LOOP
      EXIT
WHEN res = FALSE;

SELECT
    table_has_column(tablename,
    x) AS res
INTO
    res;
END;
END;

$func$ LANGUAGE plpgsql;

random_text.sql

CREATE OR REPLACE FUNCTION
random_text(n_length INTEGER default 50)
RETURNS TEXT
LANGUAGE SQL
AS $$

select
string_agg(substr(characters, (random() * length(characters) + 0.5)::integer, 1), '') as random_word
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-')) as symbols(characters)
-- length of word
join generate_series(1, n_length) on 1 = 1;

$$;

#sql #arel #query

Order elements from a request in a specific order

user = User.first

result = user.user_books.in_order_of(:status, %w[to_read currently_reading read])

=> #<ActiveRecord::Relation [#<UserBook id: 3, user_id: 1, status: "to_read">, #<UserBook id: 4, user_id: 1, status: "to_read">, #<UserBook id: 5, user_id: 1, status: "currently_reading">, #<UserBook id: 6, user_id: 1, status: "read">]>

Browse more snippets >>