-2

I have read numerous article and different blogs of SQL Server . I have an unique problem in this case. Let me explain you my problem statement.

I have many tables and i cannot union it all.

So, i am trying to get the one column value (It can be string or null value)if the value doesn't exist in table 1, then it will go the another table and scan the whole table to find the value. If it doesn't exist in 2nd table, it will scan the table 3 and it goes on.. Untill it find that value.

Please help me writing either case statement or if else statement in SQL

  • 2
    Define "many" . – ypercubeᵀᴹ Jun 7 '17 at 20:03
  • 1
    And correct the tags. Do you use SQL Server or MySQL? – ypercubeᵀᴹ Jun 7 '17 at 20:04
  • I have 20 table and i need to search a value in different table . – Avinash Kumar Jun 7 '17 at 20:05
  • 1
    Is the column the same in each table? Or you have to search all columns in the tables? Or do you need the column to be searched to be dynamic/variable? – Jacob H Jun 7 '17 at 20:10
  • 2
    You'll need to edit your question to provide more info. What exactly are you trying to do? Locate any occurrences of a specific string in text-type columns in your DB? Search multiple tables to find a row with your value in a specific column? Is there a known set of tables to search, or will there be a dynamic list of tables? exact match or contains? Are you saying the search string can be NULL; if so, should it match all NULL values, or nothing? Do you just need to know if it exists, or do your do something with matching rows? Are the seach columns indexed? – RDFozz Jun 7 '17 at 20:13
1

Assumptions:

  • you need to search one column in every table, not many.
  • every column has the same type.

Then you can use a CASE expression with EXISTS subqueries to build something like this. If the tables are a lot (say 100+), you could use dynamic SQL or construct the query in another programming language:

SELECT 
  CASE 
    WHEN EXISTS (SELECT @prm INTERSECT SELECT colA FROM tabA) THEN 'tableA'
    WHEN EXISTS (SELECT @prm INTERSECT SELECT colB FROM tabB) THEN 'tableB'
    WHEN EXISTS (SELECT @prm INTERSECT SELECT colC FROM tabC) THEN 'tableC'
    ---
    WHEN EXISTS (SELECT @prm INTERSECT SELECT colZ FROM tabZ) THEN 'tableZ'
    ELSE 'Not Found'
  END AS result ;

Test at dbfiddle.uk.

  • Thanks for replying I am stilling getting the below error message All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. – Avinash Kumar Jun 7 '17 at 20:52
  • 2
    Then you have changed the query. – ypercubeᵀᴹ Jun 7 '17 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.