How to write select Query in MySQL?

Question

I want to write a select query where i user have to pass two input ID and SourceID. But here is the twist, Input ID is mandetory and SourceID is Optional. I want to write a select query where if a user passes only Input ID then my select query will filter data based on ID but if user passes both ID and SourceID then user should Get Data based on Both Filters. I am pretty much new to database query so i am not able to figure out how to do this?

My request body:

<body>
 <p:GetEntryByID xmlns:p="http://abcFarm.org/">
  <!--Exactly 1 occurrence-->
  <xs:ExistingID xmlns:xs="http://abcFarm.org/">?</xs:ExistingID>
  <!--0 to 1 occurrence-->
  <xs:TargetSourceID xmlns:xs="http://abcFarm.org/">?</xs:TargetSourceID>
 </p:GetEntryByID >
</body>

I have written a sql query which is not working as:

select * from entry WHERE ID='ID1' AND (e.SourceID='SourceID1' Or ID='ID1');

But this is not fulfilling my above need. Please Help.Thanks in advance.

This doesn't really make a lot of sense. Could you provide an example of theexpected input and output? — symcbean, May 30 '13 at 14:50

score 4 · Answer 1 · answered May 30 '13 at 14:49

4

Try this,

SELECT * 
FROM   entry 
WHERE  ID = 'ID1' AND 
       SourceID = COALESCE('SourceID1', SourceID)

What it means is that when NULL is passed in SourceID1, the value that will be compared with column SourceID is the value of the column itself.

answered May 30 '13 at 14:49

John Woo

258,903
69
498
492

But this gives me data only if i pass both ID and SourceID,If I send Only ID Then This Query Fails:( – Roy May 30 '13 at 14:52
you need to pass `NULL` instead of `SourceID1` if you want to filter only by `ID`. – John Woo May 30 '13 at 14:54
do you use your own script or an application to make the requests? maybe NULL isn't send as a NULL value, but as a string. If you use your own script, it could be easier to just write two queries and switch which query is used based on which parameter are present – cypherabe May 30 '13 at 15:00
@Roy by the way, `NULL` is different from passing `''` empty string. `NULL` is an unknown value while `''` is a value but an empty string. – John Woo May 30 '13 at 15:02
actually i have to pass empty string instead of null – Roy May 30 '13 at 15:08
i thought of doing like this but if a user passes multiple SourceID then i have to invoke this query multiples times thus resulting in performance issue:(.My boss won't accept this approach – Roy May 30 '13 at 15:12
1

@Roy multiple SourceID is never mentionned in your question. For empty string and/or null, you might do `SourceID = CASE WHEN COALESCE('SourceID1', '') = '' THEN SourceID ELSE 'SourceID1' END` – Raphaël Althaus May 30 '13 at 15:22
I already gave you atleast a part of the correct answer. You haven't mention it in your question. Please do update it that exactly asked your needs. Anyway, try handling it in the application level. – John Woo May 30 '13 at 15:31
I just wanted to know what this line means:-SourceID = CASE WHEN COALESCE('SourceID1', '') = '' THEN SourceID ELSE 'SourceID1' END – Roy Jun 11 '13 at 04:19

score 0 · Answer 2 · answered May 30 '13 at 14:58

0

$id = "Your_ID1";
$SourceID1 = "Your_SourceID1";

if($id){
    $query = "select * from entry WHERE ID=$id";

    if($SourceID1){
        $query .= " AND SourceID = $SourceID1";
    }
    $query .= ";";
}

answered May 30 '13 at 14:58

Deepak Mallah

3,926
4
21
26

How to write select Query in MySQL?

2 Answers2