    JOIN t2 ON t1.id=t2.id AND t1.date=t2.date

    JOIN t2 ON (t2.id = t1.id AND t2.date = t1.date)

    SELECT * 
    FROM t1
    JOIN t2 USING (id, date)

perhaps you&#39;ll need to use INNEER JOIN or
where t2.id is not null
if you want results only matching both conditions

In WPF 3.5SP1 i use the last feature StringFormat in DataBindings:

```
     &lt;TextBlock Text=&quot;{Binding Path=Model.SelectedNoteBook.OriginalDate, StringFormat=&#39;f&#39;}&quot;
                FontSize=&quot;20&quot; TextTrimming=&quot;CharacterEllipsis&quot; /&gt;
```

The problem I face is that the date is always formatted in English...although my system is in French ? How can i force the date to follow system date?

StringFormat Localization issues in wpf

SQL Server 2005 has great `sys.XXX` views on the system catalog which I use frequently.

What stumbles me is this: why is there a `sys.procedures` view to see info about your stored procedures, but there is no `sys.functions` view to see the same for your stored functions?

Doesn&#39;t anybody use stored functions? I find them very handy for e.g. computed columns and such!

Is there a specific reason `sys.functions` is missing, or is it just something that wasn&#39;t considered important enough to put into the `sys` catalog views? Is it available in SQL Server 2008?


SQL Server - where is &quot;sys.functions&quot;?

I have two tables with `date` and `id` fields. I want to join on both fields.  I tried 

    JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date)

that works, but it is very slow. is there a better way to do this?

MySQL how to join tables on two fields

I have two tables with <code>date</code> and <code>id</code> fields. I want to join on both fields. I tried
<pre><code class="hljs language-scss">JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date)
</code></pre>
that works, but it is very slow. is there a better way to do this?

I have a MySQL table with utf8 general ci collation. In the table, I can see two entries:

abad  
ab&#225;d

I am using a query that looks like this:

    SELECT *  FROM `words` WHERE `word` = &#39;ab&#225;d&#39;

The query result gives both words:

abad  
ab&#225;d

Is there a way to indicate that I only want MySQL to find the accented word? I want the query to only return  

ab&#225;d  

I have also tried this query:

    SELECT *  FROM `words` WHERE BINARY `word` = &#39;ab&#225;d&#39;

It gives me no results. Thank you for the help.

How to conduct an Accent Sensitive search in MySql

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a `TIMESTAMP` field, like: 

    SELECT COUNT(id)
    FROM stats
    WHERE record_date.YEAR = 2009
    GROUP BY record_date.YEAR

Or even:

    SELECT COUNT(id)
    FROM stats
    GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic. 

Thanks!

MySQL Query GROUP BY day / month / year

Using standard mysql functions is there a way to write a query that will return a list of days between two dates.

eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:

     2009-01-01 
     2009-01-02 
     2009-01-03
     2009-01-04 
     2009-01-05
     2009-01-06
     2009-01-07
     2009-01-08 
     2009-01-09
     2009-01-10
     2009-01-11
     2009-01-12
     2009-01-13

Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).

Get a list of dates between two dates

I&#39;m trying to figure out how to optimize a very slow query in MySQL (I didn&#39;t design this):

    SELECT COUNT(*) FROM change_event me WHERE change_event_id &gt; &#39;1212281603783391&#39;;
    +----------+
    | COUNT(*) |
    +----------+
    |  3224022 |
    +----------+
    1 row in set (1 min 0.16 sec)

Comparing that to a full count:

    select count(*) from change_event;
    +----------+
    | count(*) |
    +----------+
    |  6069102 |
    +----------+
    1 row in set (4.21 sec)


The explain statement doesn&#39;t help me here:

     explain SELECT COUNT(*) FROM change_event me WHERE change_event_id &gt; &#39;1212281603783391&#39;\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: me
             type: range
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 8
              ref: NULL
             rows: 4120213
            Extra: Using where; Using index
    1 row in set (0.00 sec)

OK, it still thinks it needs roughly 4 million entries to count, but I could count lines in a file faster than that!  I don&#39;t understand why MySQL is taking this long.

Here&#39;s the table definition:

    CREATE TABLE `change_event` (
      `change_event_id` bigint(20) NOT NULL default &#39;0&#39;,
      `timestamp` datetime NOT NULL,
      `change_type` enum(&#39;create&#39;,&#39;update&#39;,&#39;delete&#39;,&#39;noop&#39;) default NULL,
      `changed_object_type` enum(&#39;Brand&#39;,&#39;Broadcast&#39;,&#39;Episode&#39;,&#39;OnDemand&#39;) NOT NULL,
      `changed_object_id` varchar(255) default NULL,
      `changed_object_modified` datetime NOT NULL default &#39;1000-01-01 00:00:00&#39;,
      `modified` datetime NOT NULL default &#39;1000-01-01 00:00:00&#39;,
      `created` datetime NOT NULL default &#39;1000-01-01 00:00:00&#39;,
      `pid` char(15) default NULL,
      `episode_pid` char(15) default NULL,
      `import_id` int(11) NOT NULL,
      `status` enum(&#39;success&#39;,&#39;failure&#39;) NOT NULL,
      `xml_diff` text,
      `node_digest` char(32) default NULL,
      PRIMARY KEY  (`change_event_id`),
      KEY `idx_change_events_changed_object_id` (`changed_object_id`),
      KEY `idx_change_events_episode_pid` (`episode_pid`),
      KEY `fk_import_id` (`import_id`),
      KEY `idx_change_event_timestamp_ce_id` (`timestamp`,`change_event_id`),
      KEY `idx_change_event_status` (`status`),
      CONSTRAINT `fk_change_event_import` FOREIGN KEY (`import_id`) REFERENCES `import` (`import_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Version:

    $ mysql --version
    mysql  Ver 14.12 Distrib 5.0.37, for pc-solaris2.8 (i386) using readline 5.0

Is there something obvious I&#39;m missing?  (Yes, I&#39;ve already tried &quot;SELECT COUNT(change_event_id)&quot;, but there&#39;s no performance difference).

&quot;SELECT COUNT(*)&quot; is slow, even with where clause

I just had a fairly complex query I was working with and it was taking 8 seconds to run. EXPLAIN was showing a weird table order and my indexes were not all being used even with the FORCE INDEX hint. I came across the STRAIGHT_JOIN join keyword and started replacing some of my INNER JOIN keywords with it. I noticed considerable speed improvement. Eventually I just replaced all my INNER JOIN keywords with STRAIGHT_JOIN for this query and it now runs in .01 seconds.

My question is when do you use STRAIGHT_JOIN and when do you use INNER JOIN? Is there any reason to not use STRAIGHT_JOIN if you are writing good queries?

When to use STRAIGHT_JOIN with MySQL

Both these joins will give me the same results:

    SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK

vs

    SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK

Is there any difference between the statements in performance or otherwise? 

Does it differ between different **SQL** implementations? 

Difference between JOIN and INNER JOIN

This follows [this][1] prior question, which was answered. I actually discovered I could remove a join from that query, so now the working query is

    start_cards = DeckCard.find :all, :joins =&gt; [:card], :conditions =&gt; [&quot;deck_cards.deck_id = ? and cards.start_card = ?&quot;, @game.deck.id, true]  

This appears to work. However, when I try to move these DeckCards into another association, I get the ActiveRecord::ReadOnlyRecord error.

Here&#39;s the code

    for player in @game.players 
      player.tableau = Tableau.new
      start_card = start_cards.pop 
      start_card.draw_pile = false
      player.tableau.deck_cards &lt;&lt; start_card  # the error occurs on this line
    end

and the relevant Models (tableau are the players cards on the table)

    class Player &lt; ActiveRecord::Base
      belongs_to :game
      belongs_to :user
      has_one :hand
      has_one :tableau
    end

    class Tableau &lt; ActiveRecord::Base
      belongs_to :player
      has_many :deck_cards
    end  

    class DeckCard &lt; ActiveRecord::Base
      belongs_to :card
      belongs_to :deck  
    end

I am doing a similar action just after this code, adding `DeckCards` to the players hand, and that code is working fine. I wondered if I needed `belongs_to :tableau` in the DeckCard Model, but it works fine for the adding to player&#39;s hand. I do have a `tableau_id` and `hand_id` columns in the DeckCard table.

I looked up ReadOnlyRecord in the rails api, and it doesn&#39;t say much beyond the description.

  [1]: https://stackoverflow.com/questions/628000/rails-whats-wrong-with-this-multiple-join-with-conditions-on-the-associations &quot;this&quot;

What is causing this ActiveRecord::ReadOnlyRecord error?

I have a MySQL query that joins two tables 

 - Voters
 - Households

They join on `voters.household_id` and `household.id`.

Now what I need to do is to modify it where the voter table is joined to a third table called elimination, along `voter.id` and `elimination.voter_id`. However the catch is that I want to exclude any records in the voter table that have a corresponding record in the elimination table.

How do I craft a query to do this?

This is my current query:

    SELECT `voter`.`ID`, `voter`.`Last_Name`, `voter`.`First_Name`,
           `voter`.`Middle_Name`, `voter`.`Age`, `voter`.`Sex`,
           `voter`.`Party`, `voter`.`Demo`, `voter`.`PV`,
           `household`.`Address`, `household`.`City`, `household`.`Zip`
    FROM (`voter`)
    JOIN `household` ON `voter`.`House_ID`=`household`.`id`
    WHERE `CT` = &#39;5&#39;
    AND `Precnum` = &#39;CTY3&#39;
    AND  `Last_Name`  LIKE &#39;%Cumbee%&#39;
    AND  `First_Name`  LIKE &#39;%John%&#39;
    ORDER BY `Last_Name` ASC
    LIMIT 30 



MySQL Join Where Not Exists

I tried :

    UPDATE closure JOIN item ON ( item_id = id ) 
    SET checked = 0 
    WHERE ancestor_id = 1

And:

    UPDATE closure, item 
    SET checked = 0 
    WHERE ancestor_id = 1 AND item_id = id

Both works with MySQL, but those give me a syntax error in SQLite.

How can I make this UPDATE / JOIN works with SQLite version 3.5.9 ?


Content Type	Original Author	Original Content on Stackoverflow
Question	pedalpete	View Question on Stackoverflow
Solution 1 - Mysql	womble	View Answer on Stackoverflow
Solution 2 - Mysql	Chad Birch	View Answer on Stackoverflow
Solution 3 - Mysql	Eugene Kaurov	View Answer on Stackoverflow

MySQL how to join tables on two fields

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

SQL Server - where is "sys.functions"?

StringFormat Localization issues in wpf

Attributions