CODEWITHSUNDEEP
sql-servervb.net
Nalum
Nalum

Reputation: 4213

SQL Server query combination

I have a query that I have to run that requires me to get rows from a table based on the value of a field in another table (which I have gotten earlier).

I currently have it done like this (simplified).

cmd.commandtext = "SELECT * FROM TableB WHERE FieldC = '" & TableA.FieldF & "'"
cmdReader = cmd.executereader()
If cmdReader.HasRows Then
    Do something
Else
    cmdReader.close()
    cmd.commandtext = "SELECT * FROM TableB WHERE FieldC = 'Generic'"
    cmdReader = cmd.executereader()
    If cmdReader.HasRows Then
        Do something
    Else
        Do something
    End If
End If

Is there a way that I can combine those two queries so that I get one or the other?

Let me know if you need any more info, not sure I've explained this properly.

Upvotes: 0

Views: 448

Answers (1)

Andomar
Andomar

Reputation: 238076

Try:

WHERE FieldC = '" & TableA.FieldF & "' OR FieldC = 'Generic'

Or

WHERE FieldC IN ('" & TableA.FieldF & "', 'Generic')

Even better, use parameters:

WHERE FieldC IN (@FieldF, 'Generic')
...
cmd.Parameters.Add("@FieldF", TableA.FieldF);

EDIT: To select only one set of rows in one query, you could:

SELECT  * 
FROM    TableB 
WHERE   FieldC = @FieldF
UNION ALL
SELECT  * 
FROM    TableB 
WHERE   NOT EXISTS (SELECT * FROM TableB WHERE FieldC = @FieldF)
        AND FieldC = 'Generic'

Upvotes: 2

Related Questions