CODEWITHSUNDEEP
c#sqlite
user2126878
user2126878

Reputation: 7

How to find common rows in two sets of query results?

Right now I have two string queries:

SELECT * FROM hmdb WHERE shamsidate MATCH '1376/05/24 1385/11/12'

and,

SELECT * FROM hmdb WHERE hmdb MATCH 'content:red OR keyword:red v_other:true'

and this is the way I show the results of each one:

            public void searchRecords()
            {
            SQLiteConnection m_dbConnection;
            m_dbConnection = new SQLiteConnection("Data Source=hmdb.sqlite;version=3;");

            try
            {
                m_dbConnection.Open();
                SQLiteDataAdapter db = new SQLiteDataAdapter(queryString, m_dbConnection);
                DataTable dt = new DataTable("hmdb");
                db.Fill(dt);
                listView1.ItemsSource = dt.DefaultView;
            }
            catch (Exception e)
            {
                string ex = e.ToString();
                System.Windows.MessageBox.Show(ex);
            }
            finally
            {
                if (m_dbConnection != null)
                {
                    m_dbConnection.Close();
                    m_dbConnection.Dispose();
                }

            }
        }

Now, I wanted to know how can I get the common rows in both of these queries in order to show them in my listView; or better yet, is there a way to do just one query and get the desired result?

Upvotes: 0

Views: 107

Answers (4)

Vrashabh Irde
Vrashabh Irde

Reputation: 14367

If By Common rows you mean only common to both queries? Then use an AND.

SELECT * FROM hmdb WHERE shamsidate MATCH '1376/05/24 1385/11/12'
AND (hmdb MATCH 'content:red OR keyword:red v_other:true')

If you mean display common rows in both queries along with rows that query 1 matches alone and query 2 matches alone - use UNION –

SELECT * FROM hmdb WHERE shamsidate MATCH '1376/05/24 1385/11/12'
UNION
SELECT * FROM hmdb WHERE hmdb MATCH 'content:red OR keyword:red v_other:true'

Upvotes: 0

dcaswell
dcaswell

Reputation: 3167

I think you're looking for:

SELECT * FROM hmdb WHERE shamsidate MATCH '1376/05/24 1385/11/12'
INTERSECT
SELECT * FROM hmdb WHERE hmdb MATCH 'content:red OR keyword:red v_other:true'

The INTERSECT keyword is explained here

Upvotes: 2

Nikhil Agrawal
Nikhil Agrawal

Reputation: 48550

I think best would be to have and condition in the queries.

SELECT * FROM hmdb WHERE shamsidate MATCH '1376/05/24 1385/11/12' and hmdb MATCH 'content:red OR keyword:red v_other:true'

Had it been two different tables, you could have used Joins.

Upvotes: 0

Yaakov Ellis
Yaakov Ellis

Reputation: 41530

You have two queries:

SELECT * FROM hmdb WHERE shamsidate MATCH '1376/05/24 1385/11/12'
SELECT * FROM hmdb WHERE hmdb MATCH 'content:red OR keyword:red v_other:true'

Just combine the where conditions and use one query instead using an AND clause (and parentheses to keep the logic consistent):

SELECT * FROM hmdb 
WHERE shamsidate MATCH '1376/05/24 1385/11/12' 
      AND (hmdb MATCH 'content:red OR keyword:red v_other:true')

Upvotes: 0

Related Questions