Effective way to check whether particular data exist in each row and column or not?

mysql check if row exists before insert
sql query to check if value exists in column
mysql check if row exists stored procedure
check if a row exists in sql
mysql check if value exists in table
mysql check if record exists
mysql exists
php sql check if record exists

1) I want to pick row which contain data x in below table

2) I want to pick column which contain data x in below table

xtable

 ID C1 C2 C3 C4
--- -- -- --- --
  1 A  -  -   -
  2 -  A  -   -
  3 A  -  A   -
  4 -  A  -   -
  5 -  -  -   -

Solution i tried

SELECT CASE 
         WHEN exists (SELECT 1 FROM xtable WHERE C1 = 'A') THEN ROWNUM
         WHEN exists (SELECT 1 FROM xtable WHERE C2 = 'A') THEN ROWNUM
         WHEN exists (SELECT 1 FROM xtable WHERE C3 = 'A') THEN ROWNUM
         WHEN exists (SELECT 1 FROM xtable WHERE C4 = 'A') THEN ROWNUM
         ELSE 0
         END "Exist"
    FROM xtable;

But i wont work

Give some effective solution for above problem

Sample data:

SQL> select * From test;

        ID C1  C2  C3  C4
---------- --- --- --- ---
         1 A
         2     A
         3 A       A
         4     A
         5

Rows that contain 'A':

SQL> select id
  2  from test
  3  where c1 = 'A' or c2 = 'A' or c3 = 'A' or c4 = 'A';

        ID
----------
         1
         2
         3
         4

SQL>

Columns that contain 'A':

SQL> select
  2    case when sum(case when c1 = 'A' then 1 else 0 end) > 0 then 'yes' else 'no' end c1,
  3    case when sum(case when c2 = 'A' then 1 else 0 end) > 0 then 'yes' else 'no' end c2,
  4    case when sum(case when c3 = 'A' then 1 else 0 end) > 0 then 'yes' else 'no' end c3,
  5    case when sum(case when c4 = 'A' then 1 else 0 end) > 0 then 'yes' else 'no' end c4
  6  from test;

C1  C2  C3  C4
--- --- --- ---
yes yes yes no

SQL>

Top 3 best ways to check if Mysql row exists -Querychat, By using MySQL EXISTS function, not only can you check the existence of a row in the of using '*', you can consider extracting only a single value as a column. a single record to check if there is any row or a table present with specific data. Check if one column value exists in another column using MATCH. You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column.

Do you just want to unpivot and select?

select t.*
from ((select id, c1 as c, 'c1' as which from t
      ) union all
      (select id, c2, 'c2' as which from t
      ) union all
      (select id, c3, 'c3' as which from t
      ) union all
      (select id, c4, 'c4' as which from t
      ) union all
      (select id, c5, 'c5' as which from t
      ) 
     ) t
where c = 'A'

Best way to check if a data exists in a data table? - Build, I have 2 data table like this I want to check if Emp Code in dt 1 exists use a FOR EACH ROW loop and pass dt1 as input and variable LookupDataTableThis activity enables you to search for a provided value in a specified DataTable lookup value in perticular column and if it is not there in that column,� Yes: [code]SELECT TOP 1 MyField FROM MyTable WHERE MyField = 'Value' [/code]If your RDBMS doesn't support TOP, put LIMIT 1 at the end instead. This query will return a record if there's a match.

A query for both 1 & 2.

SELECT ID
, CASE WHEN C1=X.C THEN 'yes' WHEN C1 IS NOT NULL THEN 'no' END AS C1
, CASE WHEN C2=X.C THEN 'yes' WHEN C2 IS NOT NULL THEN 'no' END AS C2
, CASE WHEN C3=X.C THEN 'yes' WHEN C3 IS NOT NULL THEN 'no' END AS C3
, CASE WHEN C4=X.C THEN 'yes' WHEN C4 IS NOT NULL THEN 'no' END AS C4
FROM XTABLE T
CROSS JOIN (SELECT 'A' C FROM DUAL) X
WHERE X.C IN (C1,C2,C3,C4)

A test on db<>fiddle here

Example of result:

ID | C1   | C2   | C3   | C4  
-: | :--- | :--- | :--- | :---
 1 | yes  | null | null | null
 2 | null | yes  | null | no  
 3 | yes  | null | yes  | null
 4 | null | null | null | yes 

Best way to test if a row exists in a MySQL table, To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when� I have table structure and data as below. I want SQL Query to retreive data only for those columns which having atleast one not null value in it, in above case i want data comes out to be i.e. i don't need column Col2,Col5 and Col6, also which column having all null value is not fixed.

If you're looking for whether at least one non-null value exists in the rows or columns of your table, then one option to use would be combinations of decode(), sign() and nvl2() functions together ;

For rows :

SELECT ID, 
       decode( sign( nvl2(C1,1,0)+nvl2(C2,1,0)+nvl2(C3,1,0)+nvl2(C4,1,0) ), 1, 'yes','no') 
   as "Exists"  
  FROM xtable

For columns :

SELECT decode( sign(sum( nvl2(C1,1,0) )), 1, 'yes','no') as "Exists in C1",
       decode( sign(sum( nvl2(C2,1,0) )), 1, 'yes','no') as "Exists in C2",
       decode( sign(sum( nvl2(C3,1,0) )), 1, 'yes','no') as "Exists in C3",
       decode( sign(sum( nvl2(C4,1,0) )), 1, 'yes','no') as "Exists in C4"
  FROM xtable

Demo

If you're looking for exact matching for the letter 'A', then consider using :

SELECT ID, 
       decode( sign( decode(C1,'A',1,0)+
                     decode(C2,'A',1,0)+
                     decode(C3,'A',1,0)+
                     decode(C4,'A',1,0) ), 1, 'yes','no') as "Exists"  
  FROM xtable 

and

SELECT decode( sign(sum( decode(C1,'A',1,0) )), 1, 'yes','no') as "Exists in C1",
       decode( sign(sum( decode(C2,'A',1,0) )), 1, 'yes','no') as "Exists in C2",
       decode( sign(sum( decode(C3,'A',1,0) )), 1, 'yes','no') as "Exists in C3",
       decode( sign(sum( decode(C4,'A',1,0) )), 1, 'yes','no') as "Exists in C4"
  FROM xtable

e.g.replace nvl2(Cn,1,0) expressions with decode(Cn,'A',1,0)

Demo

Check if a value exists in a column in a MySQL table?, Selected Reading; UPSC IAS Exams Notes � Developer's Best Practices � Questions mysql> create table DemoTable807( ClientId int NOT NULL ClientCountryName varchar(100) ); Query OK, 0 rows affected (0.64 sec) Following is the query to check if a value exists in a column in a MySQL table − Disclaimer: This site is started with intent to serve the ASP.Net Community by providing forums (question-answer) site where people can help each other. The content posted here is free for public and is the content of its poster. The site does not provide any warranties for the posted content.

How to check or find if value exists in another column?, Supposing, I have two columns of data which contains some duplicate values in these both And now, I want to find if the values in Column A also exists in Column C. To compare them one by one is not a good method, here, I can talk about B: Select Each row under the Based on section. Best; Popular; Newest ; Oldest. I assume that the question is asking how to determine whether a table is empty or not… If you’re just trying to confirm that a table is nonempty, you can do this most efficiently with something like [code]select 1 from mytab limit 1; [/code]This w

What is the way to check whether a value exists in a column of a , If we are not sure f the exact value of the column to be checked then we can use like operator also for the above queries. SELECT How do I skip the first row of data in an SQL query? What is the best SQL query to check if any row exists in a table? SQL: How do I find all columns in a database with a particular name? Some of us might use COUNT operator to determine whether a particular data exist. SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0 Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field 'COLUMN'.

Check if one column value exists in another column, You can check if the values in column A exist in column B using VLOOKUP. To do that, you need to perform the following steps: Select cell C2 by clicking on it. Insert the formula in “=IF(ISERROR(VLOOKUP(A2,$B$2:$B$1001,1,FALSE)),FALSE,TRUE)” the formula bar. How To Check If a Column Exists in DataRow. Friends, This is a very small post to explain how can you check whether a column exists in a DataRow. This is a very handy tip when you are working with a set of data and you’re not sure everytime the query will return a specific column.

Comments
  • If A values are from countable set, consider an opposite mapping, where A value is a key and column and row pairs are multiple mapped values
  • @NaveenM . . . I am unclear what you want as a result set.
  • Does A mean a fixed certain data exactly 'A' as a capital letter, or any non-null data(even a word or a sentence) occupying that coordinate while the other positions are completely null?
  • @BarbarosÖzhan A is a NOT NULL value
  • well, it's just a fixed(constant) literal namely the capital letter 'A', what about the others ..? are they null ..?
  • Thank u so much @Littlefoot
  • Actually i need output like what @Littlefoot given above
  • @NaveenM . . . Then you should probably accept that answer.
  • Nice...! and Thanks