How to use a MySQL view and ORDER BY clause?

Question

Question #1 - How to make a view of a table1 where if a certain field in the table is present in table2, then exclude that record from the view?

Question #2 - How to order the results in DESC order by the id in Table1.

SELECT table1.source_id
FROM table1
WHERE table1.source_id NOT IN (SELECT table2.source_id from table2)
ORDER BY table1.source_id DESC;

ORDER BY does not change which rows are selected. It changes only the order in which they are returned. — Mark Byers, Dec 23 '12 at 04:38
What you are looking for is a JOIN clause, check out some Stack Overflow questions related to joins. e.g. http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join — slifty, Dec 23 '12 at 04:47
Except I'm looking not to JOIN but do something where an id is NOT IN (Select ...) Y/N? Thanks — Ben, Dec 23 '12 at 05:14
It's not nice to change your question so radically that the answers no longer make sense. :( — Barmar, Dec 23 '12 at 07:34
Ben I think you don't understand what a JOIN is -- don't think of it so literally. JOIN just means you want to base your query on the content of multiple tables. You don't have to select content from both tables. — slifty, Dec 23 '12 at 15:04

Ravi · Answer 1 · 2012-12-23T05:36:40.713

I don't know, what are you talking about, but from your question I conclude this. Let say A and B are two tables and their common fields are fee_id. Then try this.

select a.name,b.fee_id from A a,B a where a.fee_id=b.fee_id

Above query will return all the data from both table by checking fee_id in both tables.

Now, as you said, you need ORDER BY. Then, do something like.

select a.name,b.fee_id from A a,B a where a.fee_id=b.fee_id ORDER BY a.name DESC

If you want to show your information in DESC order of name.

UPDATE

How to create view

create view viewname as (select * from table1)

Solution for your 1st question

Let say name field is common between both table and you want those record in that view which are UNIQUE in both table. I mean, if table1 has one record, but same record is not present in table2

create view viewname as (select * from table2 UNION (select * from table1 where name not in(select name from table2)))

Example :

NAME |                    NAME |
-------------            ----------------
ABC  |                   ABC   |
CDF  |                   GHI   |

It will return 

NAME |                    
-------------            
ABC  |                   
CDF  |     
GHI  |

Solution for 2nd question

You have provided this query in your question. And, i don't feel anything wrong in that.

SELECT source_id
FROM table1
WHERE source_id NOT IN (SELECT source_id from table2)
ORDER BY source_id DESC;

Thanks. I think you need to change the 'a' after the upper case B to a lower case 'b' ? Also, I am looking to do the opposite of the where a.fee_id = b.fee_id... I want to way "where not in"? Thanks in advance — Ben, Dec 23 '12 at 04:58
I've changed my question around a bit to make it more clear :) thanks — Ben, Dec 23 '12 at 05:09
Question #1 - How to make a view of a table1 where if a certain field in the table is present in table2, then exclude that record from the view? Question #2 - How to order the results in DESC order by the id in Table1. SELECT table1.source_id FROM table1 WHERE table1.source_id NOT IN (SELECT table2.source_id from table2) ORDER BY table1.source_id DESC; — Ben, Dec 23 '12 at 05:19
I think I follow your answr except two things: 1) I think you meant to make the part like this "A a,B a" have a lower case 'b'? 2) I am trying to select where the fee_id's are not equal (not where they are equal). Thanks — Ben, Dec 23 '12 at 05:20

score 1 · Accepted Answer · edited May 15 '17 at 08:48

I'd recommend you use an "anti-join" pattern. That's a LEFT JOIN with a predicate that excludes any rows that are matched,

SELECT t1.source_id
  FROM table1 t1
  LEFT JOIN table2 t2
    ON t2.source_id = t1.source_id 
 WHERE t2.source_id IS NULL
 ORDER
    BY t1.source_id DESC

(Performance of this statement will be best if there are indexes on both table1 an table2 with a leading column of source_id.)

Another alternative is to use a NOT EXISTS predicate

SELECT t1.source_id
  FROM table1 t1
 WHERE NOT EXISTS 
       ( SELECT 1
           FROM table2 t2
          WHERE t2.source_id = t1.source_id
       )
 ORDER
    BY t1.source_id DESC

(The MySQL optimizer may run that as an "anti-join" as well, rather than as a dependent subquery; you'd need to check the output from EXPLAIN to verify.)

On large sets, either of those is going to perform much BETTER than a query that includes a derived table, such as one with a NOT IN (subquery) predicate.

Mahmoud Gamal · Answer 3 · 2012-12-23T04:54:02.820

Use LEFT JOIN to get those fee_source that is not found neither in the a_aif table nor a_proxy table, with UNION(implicit distinct) or UNION ALL, then you can put an ORDER BY clause at the end of it like so:

SELECT *
FROM
(
    SELECT a_aif.fee_source_id source_id
    FROM a_aif 
    left JOIN a_fees ON a_fees.fee_source = a_aif.fee_source_id 
    WHERE a_fees.fee_source IS NULL
    UNION ALL
    SELECT a_proxy.fee_source_id
    FROM a_proxy
    left JOIN a_fees ON a_fees.fee_source = a_proxy.fee_source_id
    WHERE a_fees.fee_source IS NULL
) t
ORDER BY source_id DESC;

SQL Fiddle Demo

score 0 · Answer 4 · edited Jun 19 '19 at 17:06

0

I used OFFSET 0 ROWS to force order by clause

select count(*) from (
select column_name 
from table_A
order by column_name OFFSET 0 ROWS) abc

edited Jun 19 '19 at 17:06

Anton Menshov

2,266
14
34
55

answered Jun 19 '19 at 16:12

Mohit Kanojia

690
5
6

How to use a MySQL view and ORDER BY clause?

4 Answers4

SQL Fiddle Demo