working on it ...

Filters

Explore Public Snippets

Sort by

Found 14 snippets matching: "join tables"

    public by p.kontalis  2083  0  5  0

    SQL Join with Static Columns

    Identifies the origin of each row after the join of two tables.
    DROP TABLE IF EXISTS signed_A;
    DROP TABLE IF EXISTS signed_B;
    CREATE TEMPORARY TABLE signed_A AS (SELECT 'TableA' AS STATUS, A.col1, A.col2 FROM A);
    CREATE TEMPORARY TABLE signed_B AS (SELECT 'TableB' AS STATUS, hostings.col3 FROM B);
    SELECT * FROM A LEFT JOIN B ON A.col2=B.col3;

    external by Tom Worster  151185  0  3  0

    Using 3-way join tables in Yii 2 Active Record

    Using 3-way join tables in Yii 2 Active Record: 3-way-joins.md
    I have an interesting Yii 2 AR design problem.
    
    [The DB has](http://musicbrainz.org/relationships) 12 primary entity tables related via a full mesh of 72 join tables. Each relation also has a type (and attributes) but those aren't stored in the join tables – they are in additional tables that the 72 join tables reference. So each of the 72 join tables provides [a 3-way join](http://musicbrainz.org/doc/MusicBrainz_Database/Schema#Relationship_table_structure) between one entity, another entity and another table called link that points to the link_type.
    
    My problem is to write the AR models and relation methods.
    
    Let's make this more concrete with two of the primary entities: artist and recording, related through l_artist_recording. Each record of l_artist_recording has an FK to the link table, through which I can load the link_type name. For example, an artist-recording relation might have link_type "performer", "conductor", or "producer" etc.
    
    This is a simplified schma good enough for thinking about the AR problem.
    
    ![Simplified schema](https://monosnap.com/file/qUjynxqri5JTz0Peu6jaLDfk2NcjOw.png)
    
    An obvious approach is to write an AR model for each of the tables in the diagram. Then Artist models can relate to Recordings and LinkTypes via ArtistRecording. This allows Artist to know the LinkType of the relation to individual Recording model because Recording and LinkType are both properties of each ArtistRecording model. But this requires 72 models for the 72 join tables and it's clumsy to work this way.
    
    I would far prefer if link_type.name were populated into related Recording models when they are loaded. Then, for example, if `$artist` is an Artist model instance it would have `$artist->recordings` which is an array of Recording models, each of which has a `Recording::$name` column attribute loaded from recording.name and a `Recording::$link_type_name` instance variable loaded from link_type.name.
    
    I can relate Artist to Recording without needing a model for artist_recording
    
    ```php
        public function getRecordings() {
            return $this
                ->hasMany(Recording::className(), ['id' => 'recording_id'])
                ->viaTable('artist_recording', ['artist_id' => 'id']);
        }
    ```
    
    But I don't know how to elaborate that so it loads fields from tables related via the third leg of the 3-way join into the Recording models. I am inclined to believe AR *can* do this. But I don't know *how*.
    
    
    
    

    external by bastiankoetsier  243  1  4  0

    Forces eloquent to join tables instead of eager-loading. useful for filtering & orderin

    Forces eloquent to join tables instead of eager-loading. useful for filtering & orderin: eloquent-join.php
    /**
     * @param $query
     * @param $relation_name
     * @param string $operator
     * @param string $type
     * @param bool $where
     * @return mixed
     */
    public function scopeModelJoin($query, $relation_name, $operator = '=', $type = 'left', $where = false)
    {
        /** @var \Illuminate\Database\Eloquent\Relations\Relation $relation */
        $relation = $this->$relation_name();
        $table = $relation->getRelated()->getTable();
        $one = $relation->getQualifiedParentKeyName();
        $two = $relation->getForeignKey();
    
        if (empty($query->columns)) {
            $query->select($this->getTable().".*");
        }
        foreach (\Schema::getColumnListing($table) as $related_column) {
            $query->addSelect(new Expression("`$table`.`$related_column` AS `$table.$related_column`"));
        }
        return $query->join($table, $one, $operator, $two, $type, $where); //->with($relation_name);
    }
    
    
    

    external by kata-kata  297  0  3  0

    survey_pin with join tables

    survey_pin with join tables: survey_pin.sql
    select p0.survey_pin
    		,  (select count(sm.id) from survey sm where sm.survey_pin = p0.survey_pin) as cnt_rows_for_pin
    		, @rownum := @rownum + 1 AS rank
    		, ssd.*
    		, p0.id  as P1_id,  p0.partnerName as P1_Name, p0.partnerType as P1_Rel
    			, p0.vaginal as P1_V_Sex, p0.vaginalSexNumber as P1_V_Sex_N, p0.vaginalSexCondomNumber as P1_V_Sex_Cond_N
    			, p0.oral as P1_O_Sex, p0.oralSexNumber as P1_O_Sex_N, p0.oralSexCondomNumber as P1_O_Sex_Cond_N
    			, p0.anal as P1_A_Sex, p0.analSexNumber as P1_A_Sex_N, p0.analSexCondomNumber as P1_A_Sex_Cond_N
    			, p0.otherWomen as P1_Sex_OW, p0.otherMan as P1_Sex_OM
    		, p1.id  as P2_id,  p1.partnerName as P2_Name, p1.partnerType as P2_Rel
    			, p1.vaginal as P2_V_Sex, p1.vaginalSexNumber as P2_V_Sex_N, p1.vaginalSexCondomNumber as P2_V_Sex_Cond_N
    			, p1.oral as P2_O_Sex, p1.oralSexNumber as P2_O_Sex_N, p1.oralSexCondomNumber as P2_O_Sex_Cond_N
    			, p1.anal as P2_A_Sex, p1.analSexNumber as P2_A_Sex_N, p1.analSexCondomNumber as P2_A_Sex_Cond_N
    			, p1.otherWomen as P2_Sex_OW, p1.otherMan as P2_Sex_OM
    		, p2.id  as P3_id,  p2.partnerName as P3_Name, p2.partnerType as P3_Rel
    			, p2.vaginal as P3_V_Sex, p2.vaginalSexNumber as P3_V_Sex_N, p2.vaginalSexCondomNumber as P3_V_Sex_Cond_N
    			, p2.oral as P3_O_Sex, p2.oralSexNumber as P3_O_Sex_N, p2.oralSexCondomNumber as P3_O_Sex_Cond_N
    			, p2.anal as P3_A_Sex, p2.analSexNumber as P3_A_Sex_N, p2.analSexCondomNumber as P3_A_Sex_Cond_N
    			, p2.otherWomen as P3_Sex_OW, p2.otherMan as P3_Sex_OM	
    		, p3.id  as P4_id,  p3.partnerName as P4_Name, p3.partnerType as P4_Rel
    			, p3.vaginal as P4_V_Sex, p3.vaginalSexNumber as P4_V_Sex_N, p3.vaginalSexCondomNumber as P4_V_Sex_Cond_N
    			, p3.oral as P4_O_Sex, p3.oralSexNumber as P4_O_Sex_N, p3.oralSexCondomNumber as P4_O_Sex_Cond_N
    			, p3.anal as P4_A_Sex, p3.analSexNumber as P4_A_Sex_N, p3.analSexCondomNumber as P4_A_Sex_Cond_N
    			, p3.otherWomen as P4_Sex_OW, p3.otherMan as P4_Sex_OM
    		, p4.id  as P5_id,  p4.partnerName as P5_Name, p4.partnerType as P5_Rel
    			, p4.vaginal as P5_V_Sex, p4.vaginalSexNumber as P5_V_Sex_N, p4.vaginalSexCondomNumber as P5_V_Sex_Cond_N
    			, p4.oral as P5_O_Sex, p4.oralSexNumber as P5_O_Sex_N, p4.oralSexCondomNumber as P5_O_Sex_Cond_N
    			, p4.anal as P5_A_Sex, p4.analSexNumber as P5_A_Sex_N, p4.analSexCondomNumber as P5_A_Sex_Cond_N
    			, p4.otherWomen as P5_Sex_OW, p4.otherMan as P5_Sex_OM
    		, sl.id  as SELF_id,  sl.partnerName as SELF_Name, sl.partnerType as SELF_Rel
    			, sl.vaginal as SELF_V_Sex, sl.vaginalSexNumber as SELF_V_Sex_N, sl.vaginalSexCondomNumber as SELF_V_Sex_Cond_N
    			, sl.oral as SELF_O_Sex, sl.oralSexNumber as SELF_O_Sex_N, sl.oralSexCondomNumber as P5_O_Sex_Cond_N
    			, sl.anal as P5_A_Sex, sl.analSexNumber as SELF_A_Sex_N, sl.analSexCondomNumber as SELF_A_Sex_Cond_N
    			, sl.otherWomen as SELF_Sex_OW, sl.otherMan as SELF_Sex_OM
    	
    	from survey p0
    		join (SELECT @rownum := 0) r
    		left join screening_survey_detail ssd on ssd.survey_pin = p0.survey_pin 
    		left join survey p1 on p1.id = (select min(sq.id) from survey sq 
    					where sq.survey_pin = p0.survey_pin and sq.id > p0.id )
    		left join survey p2 on p2.id = (select min(sq.id) from survey sq 
    					where sq.survey_pin = p0.survey_pin and sq.id > p1.id )
    		left join survey p3 on p3.id = (select min(sq.id) from survey sq 
    					where sq.survey_pin = p0.survey_pin and sq.id > p2.id )
    		left join survey p4 on p4.id = (select min(sq.id) from survey sq 
    					where sq.survey_pin = p0.survey_pin and sq.id > p3.id )
    		left join survey sl on sl.id = (select max(sq.id) from survey sq 
    					where sq.survey_pin = p0.survey_pin and sq.id > p4.id )
    	where p0.ID = (select min(sm.id) from survey sm where sm.survey_pin = p0.survey_pin)
    		and p0.survey_pin <> 0 /*= 1678 /*<> 0*/
    	order by rank
    
    

    external by manlon  171  0  2  0

    repro case for issue with join tables in non-default db

    repro case for issue with join tables in non-default db: secondary_db_join_table_test.rb
    begin
      require 'bundler/inline'
    rescue LoadError => e
      $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
      raise e
    end
    
    gemfile(true) do
      source 'https://rubygems.org'
    
      # repros in 4.x and on master
      gem 'activerecord', '4.2.3'
    
      #gem 'rails', github: 'rails/rails'
      #gem 'arel', github: 'rails/arel'
      
      gem 'sqlite3'
    end
    
    require 'active_record'
    require 'minitest/autorun'
    require 'logger'
    
    ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'primary.sqlite3')
    ActiveRecord::Base.logger = Logger.new(STDOUT)
    
    # Abstract base class for models in the default database
    class Primary < ActiveRecord::Base
      self.abstract_class = true
    end
    
    # Abstract base class for models in the secondary database
    class Secondary < ActiveRecord::Base
      establish_connection(adapter: 'sqlite3', database: 'secondary.sqlite3')
      self.abstract_class = true
    end
    
    ActiveRecord::Schema.define do
      create_table :primary_models, force: true
      create_table :primary_associateds, force: true
      create_join_table :primary_associateds, :primary_models, force: true
    end
    
    # quick and dirty schema definition for the secondary db
    sql = <<-EOSQL 
        DROP TABLE IF EXISTS "secondary_models"
        DROP TABLE IF EXISTS "secondary_associateds"
        DROP TABLE IF EXISTS "secondary_associateds_models"
        CREATE TABLE "secondary_models" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)
        CREATE TABLE "secondary_associateds" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)
        CREATE TABLE "secondary_associateds_models" ("secondary_model_id" INTEGER NOT NULL, "secondary_associated_id" INTEGER NOT NULL)
    EOSQL
    sql.split("\n").each{|line| Secondary.connection.execute line }
    
    
    # A pair of HABTM-associated models in each db
    class PrimaryModel < Primary
      has_and_belongs_to_many :primary_associateds
    end
    class PrimaryAssociated < Primary; end
    
    class SecondaryModel < Secondary
      has_and_belongs_to_many :secondary_associateds
    end
    class SecondaryAssociated < Secondary; end
    
    class JoinTableTest < Minitest::Test
      def test_associations_primary
        model = PrimaryModel.create!
        associated = PrimaryAssociated.create!
        assert_equal 0, model.primary_associateds.count
        model.primary_associateds << associated
        assert_equal 1, model.primary_associateds.reload.count
      end
    
      def test_associations_secondary
        model = SecondaryModel.create!
        associated = SecondaryAssociated.create!
    
        # normal SELECT works
        assert_equal 0, model.secondary_associateds.count
    
        # adding to the association proxy raises: ActiveRecord::StatementInvalid: Could not find table 'secondary_associateds_models'
        model.secondary_associateds << associated
        assert_equal 1, model.secondary_associateds.reload.count
      end
    end
    
    

    external by skateman  162  0  2  0

    PseudoColumns can mask columns with default values, useful when joining tables in ActiveRecord

    PseudoColumns can mask columns with default values, useful when joining tables in ActiveRecord: pseudo_columns.rb
    module PseudoColumns
      extend ActiveSupport::Concern
    
      module ClassMethods
        # Specify the columns in {name => default_value} format
        def pseudo_columns(columns = {})
          columns.each do |k, v|
            define_method(k) do
              attributes.fetch(__method__.to_s, v)
            end
          end
        end
      end
    end
    
    
    

    external by PatrickCallaghan  102  0  2  0

    Gets the Top10 account balances from the joined tables 'Users' and 'Accounts'

    Gets the Top10 account balances from the joined tables 'Users' and 'Accounts': OrderTop10AccountsSparkSharkDemo.md
    The default is to put this on its own node so you will need to start DSE with `dse cassandra -k` to create a spark analytics node.
    
    First run the https://github.com/PatrickCallaghan/order-management-demo project to populate the cassandra cluster (follow instructions in README). This project is based on the first bootcamp project and is a fairly general order management system (eg. users, accounts, products, orders). This can take a while so you may only want to run it for a minute or two to generate some data.
    
    NOTE you will need to add the following table to your schema
    
    ```sql
    CREATE TABLE useraccounts (
      user_id text,
      balance double,
      city text,
      first text,
      last text,
      state text,
      PRIMARY KEY (user_id)
    )
    ```
    
    In a new window start the shark shell `dse shark`
    
    Then you can use any of the scripts in the `src/main/resources/hive` directory to run the hive scripts through shark. 
    
    Example: 
    ```sql
    INSERT INTO TABLE products_by_vendor_hive
    SELECT recommendation, vendor, COUNT(distinct order_id) AS MyCount, product_id, product_name 
    FROM product_orders_by_vendor 
    GROUP BY recommendation, vendor, product_id, product_name SORT BY vendor, MyCount DESC;
    ```
    
    Close the shark terminal and run a spark shell using `dse spark`
    
    Now you can run the following commands to join the `users` and `accounts` table and filter some data. The result will be:
    
    ```scala
    case class Account (user_id: String, account_id: String, balance: Double, last_updated: java.util.Date);
    
    case class User (user_id: String, city_name: String, country_code: String, dob: java.util.Date, email: String, first_name: String, gender: String, last_name: String,
    middle_name: String, phone_number: String, state_name: String, street_address: String, 
    zip_code: String);
    
    case class UserAccount (user_id: String, balance: Double, first: String, last: String, city: String, state: String)
    
    val accounts = sc.cassandraTable[Account]("order_management", "accounts").cache
      
    val users = sc.cassandraTable[User]("order_management", "users").cache
    
    val accountsByUserId = accounts.keyBy(f => f.user_id);
    val usersByUserId = users.keyBy(f => f.user_id);
    
    //Join the tables by the user_id
    val joinedUsers = accountsByUserId.join(usersByUserId).cache
    
    //Create RDD with a the new object type which maps to our new table
    val userAccountObjects = joinedUsers.map(f => (new UserAccount(f._2._1.user_id, f._2._1.balance, f._2._2.first_name, f._2._2.last_name, f._2._2.city_name, f._2._2.state_name))).cache
    
    //get the top ten results 
    val top10 = userAccountObjects.collect.toList.sortBy(_.balance).reverse.take(10)
    
    val newRdd = sc.parallelize(top10);
    
    //save to Cassandra
    newRdd.saveToCassandra("order_management", "useraccounts")
    ```
    
    

    external by DrifterD  23  0  1  0

    多表连接 更新 删除

    多表连接 更新 删除: join tables.sql
    -- 多表更新 不能使用 limit, order by 
    update a inner join b  on a.col3=b.col3
    Set a.col1=b.col1,a.col2=b.col2
    Where 。。。。。
    
    -- 多表删除可以使用select 中所有类型的联合,也可以使用 left join ,或者inner join,或者right join
    DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
    或:
    DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
    
    --删除 jiabieming加别名
    delete alias from tableName alias where alias.xxx=xxx
    
    

    external by Encore Shao  17  0  1  0

    Mixing query in PostgreSQL (join tables)

    Mixing query in PostgreSQL (join tables): mixing_query_in_postgreSQL.sql
    SELECT salesforce_accounts.*, salesforce_tasks.*, salesforce_accounts.id AS salesforce_account_id, salesforce_tasks.id AS salesforce_task_id FROM "salesforce_accounts" 
    LEFT JOIN salesforce_tasks ON salesforce_accounts.salesforce_id = salesforce_tasks.account_id 
    LEFT JOIN companies ON companies.salesforce_id = salesforce_accounts.salesforce_id 
    WHERE 
    (
      (
        salesforce_tasks.owner_id = 'xxxxxxxxxxxxx' 
        AND salesforce_tasks.is_deleted = 'f' 
        AND salesforce_tasks.activity_date <= '2017-06-11' 
        AND salesforce_tasks.activity_date >= '2017-04-04' 
        AND salesforce_tasks.status = 'Not Started' 
        AND salesforce_tasks.activity_date < '2017-06-02'
      ) 
      OR 
      ( 
        salesforce_accounts.is_deleted = 'f'
        AND salesforce_accounts.owner_id = 'xxxxxxxxxxxxx'
        AND salesforce_accounts.id NOT IN (
          SELECT distinct(salesforce_accounts.id) FROM salesforce_accounts
          RIGHT OUTER JOIN salesforce_tasks ON salesforce_tasks.account_id = salesforce_accounts.salesforce_id
          WHERE (salesforce_accounts.owner_id = 'xxxxxxxxxxxxx')
          AND (salesforce_tasks.status = 'Not Started')
        )
        AND
        (
          salesforce_tasks.id IN (
          SELECT max(salesforce_tasks.id) FROM salesforce_tasks
          WHERE (salesforce_tasks.status = 'Completed')
          AND salesforce_tasks.is_deleted = 'f'
          GROUP BY salesforce_tasks.account_id
        )
        OR
        salesforce_tasks.id IS NULL
        OR
        salesforce_tasks.is_deleted = 't'
      )
    )) 
    ORDER BY companies.score DESC NULLS LAST, salesforce_tasks.activity_date ASC NULLS LAST, salesforce_tasks.id ASC LIMIT 10 OFFSET 0
    
    

    external by DilzGithub  13  0  1  0

    WHERE query when using it wiith join queries.

    WHERE query when using it wiith join queries.: WHERE query when join tables ___ Module.php
    <?php
    
    public function get_profile_data()
            {
                $this->db->Select('*');
                $this->db->join('faculties','faculties.faculty_id = profiles.faculty_id','left');
                $this->db->join('departments','departments.dept_id = profiles.dept_id','left');
                $this->db->join('users','users.user_id = profiles.user_id','left');
                // when using where in this case should mention the table name also. because there are some join queries.
                //  so module will not able to get the correct where field if not mention the table name.
                // query -> where('table name.field',then specific data);
                $this->db->where('profiles.display',0);
                $this->db->from('profiles');
                $result = $this->db->get();
                return $result->result();
    
    
            }
    
    ?>
    
    
    • Public Snippets
    • Channels Snippets