MySQL Query to Select Everything Except

Select all columns except one in MySQL?

Actually there is a way, you need to have permissions of course for doing this ...

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Replacing <table>, <database> and <columns_to_omit>

mysql query to select everything except

Using NOT IN:

SELECT w.*
  FROM WIDGET w
 WHERE w.widget_id NOT IN (SELECT c.widget
                             FROM CHOSEN c
                            WHERE c.user_id = $user_id)

Using NOT EXISTS:

SELECT w.*
  FROM WIDGET w
 WHERE NOT EXISTS (SELECT NULL
                     FROM CHOSEN c
                    WHERE c.widget_id = w.widget_id 
                      AND c.user_id = $user_id)

LEFT JOIN/IS NULL:

   SELECT w.*
     FROM WIDGET w
LEFT JOIN CHOSEN c ON c.widget_id = w.widget
                  AND c.user_id = $user_id
    WHERE w.widget IS NULL

Performance:

If the columns compared (widget_id in either table) are not nullable, LEFT JOIN/IS NULL performs the best on MySQL. If the columns are nullable (the value could be NULL), NOT IN or NOT EXISTS perform better.

Selecting all fields except only one field in mysql

you can do it easily like that

lets say your field is an id = 5

then

   select * from your_table where id !=5 

and if you mean columns

lets say you dont want select column3

then

   select column1,column2,column4 from tablename;

if you have many columns

    SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME),  '<columns_to_delete>,', '') 
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>'   AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

    PREPARE stmt1 FROM @sql;
   EXECUTE stmt1;

Select all rows except one in MySQL

You have a few options:

SELECT * FROM table WHERE id != 4;

SELECT * FROM table WHERE NOT id = 4;

SELECT * FROM table WHERE id <> 4;

Also, considering perhaps sometime in the future you may want to add/remove id's to this list, perhaps another table listing id's which you don't want selectable would be a good idea.

In which case you would have:

SELECT * FROM table
WHERE id NOT IN (SELECT id FROM exempt_items_table);

mysql query to select all except something

select ro.email
from registerorder ro 
inner join food f on f.namea = ro.namea
group by ro.email
having sum(f.type <> 'vegtables') = 0

SQL Query to Select Everything Except the Max Value

The max query needs to be in its own subquery, so your final SQL should be::

SELECT features.featureTitle AS title,
    features.featureSummary AS body, 
    features.postedOn AS dummy,
    DATE_FORMAT( features.postedOn,  '%M %d, %Y' ) AS posted,
    NULL,
    NULL,
    staff.staffName,
    features.featureID 
FROM 
    features 
    LEFT JOIN staff ON 
        features.staffID = staff.staffID
WHERE
   features.postedOn != (select max(features.postedOn) from features)

SELECT everything but the SELECTION from a table - MySQL

Try a LEFT OUTER JOIN as follows

select ns.*
from foo ns
left outer join 
(
   select month_year, max(nsa.created_at) created_at
   from foo nsa 
   group by month_year
 ) 
ns1 on ns1.month_year = ns.month_year and ns1.created_at <> 
ns.created_at
where ns1.month_year IS NULL;

---

Related Topics