Second SELECT query in another table if first SELECT returns 0 rows

Question

I already read this question. But this is on same table. How can I archie that in two or three table like :

if (SELECT ViewCode FROM M_VIEW
          WHERE ViewCode=?) //if found return
else (SELECT ViewCode FROM M_Customer
          WHERE CustomerCode=?)

simply consider handling this from the application code instead.. — Raymond Nijland, Nov 20 '19 at 10:01
@RaymondNijland is this possible for write this in one query? — Akashii, Nov 20 '19 at 10:05
*"is this possible for write this in one query?"* Possibly ... See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) ... Still how you describe how it should work handling this in the application seams to be much more easy implemented with likely better performance and you don't have to keep track which selects are made.... — Raymond Nijland, Nov 20 '19 at 10:07

Sebastian Brosch · Answer 1 · 2019-11-21T10:02:28.483

3

You can use a solution like the following:

SELECT DISTINCT ViewCode FROM (
    SELECT ViewCode, 'M_VIEW' AS tName FROM M_VIEW WHERE ViewCode = 1
    UNION ALL
    SELECT ViewCode, 'M_Customer' FROM M_Customer WHERE ViewCode = 1
) t GROUP BY tName, ViewCode
HAVING tName = CASE 
    WHEN SUM(tName = 'M_VIEW') > 0 THEN 'M_VIEW' 
    WHEN SUM(tName = 'M_Customer') > 0 THEN 'M_Customer'
    ELSE '' 
END

demo on dbfiddle.uk

edited Nov 21 '19 at 10:02

answered Nov 20 '19 at 10:19

Sebastian Brosch

42,106
15
72
87

This is not working, see this case https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=708c88d86699a13ab053a430e1634d1e. I just changed view_code for m_view row (id 1 & 2). It should return view code 1 because it is in table m_customer instead of nothing. Maybe you should use SUM instead of COUNT ? – Gosfly Nov 21 '19 at 09:35
Also if you use SUM instead of COUNT, it should work but you would have to add DISTINCT ViewCode to prevent multiple identical ViewCode value. – Gosfly Nov 21 '19 at 09:39

Salman A · Answer 2 · 2019-11-20T11:00:34.653

3

Here is another way of doing it, it'll return rows from exactly one table:

SELECT ViewCode FROM M_VIEW WHERE ViewCode = @ViewCode
UNION ALL
SELECT ViewCode FROM M_Customer WHERE CustomerCode = @CustomerCode AND NOT EXISTS (
    SELECT 1 FROM M_VIEW WHERE ViewCode = @ViewCode
)

Note that I've replaced ? with variable names to show which ? means what.

Demo on db<>fiddle

edited Nov 20 '19 at 11:00

answered Nov 20 '19 at 10:38

Salman A

262,204
82
430
521

You should add DISTINCT to prevent "duplicate" ViewCode – Gosfly Nov 21 '19 at 09:38
No, distinct is not required, only one branch will return row (the branch itself could return duplicates). – Salman A Nov 21 '19 at 09:47
Yes that's what I was meaning ! – Gosfly Nov 21 '19 at 09:54

GMB · Answer 3 · 2019-11-20T10:11:24.733

2

Assuming that each query should return 0 or 1 row, you could use union all, then order the records and limit, as follows:

SELECT ViewCode
FROM (
    SELECT ViewCode, 1 seq FROM M_View WHERE ViewCode = ?
    UNION ALL SELECT ViewCode, 2 FROM M_Customer WHERE CustomerCode = ?
) t
ORDER BY seq
LIMIT 1

If the first query (from M_View) returns a record, the ordering clause puts it first, and limit 1eliminates the potential other record. Else, the (only) matching record (from M_Customer) will be selected.

edited Nov 20 '19 at 10:11

answered Nov 20 '19 at 10:07

GMB

216,147
25
84
135

2

anyhow going to remove those comments, anyhow as we are wait for the information from the topicstarter before making more assumptions how to possibly read this question ... – Raymond Nijland Nov 20 '19 at 10:25
this comment //if found return I mean, if this query `SELECT ViewCode FROM M_VIEW WHERE ViewCode=? ` matching record in table M_VIEW it return this record, if not run this second query – Akashii Nov 20 '19 at 10:31

Second SELECT query in another table if first SELECT returns 0 rows

3 Answers3