SQL, where attribute in subquery if it return anything or else return everything

Question

I am trying to add a condition(subquery) that basically would filter the output but incase the subquery does not return anything, I want this condition to be ignored somehow.

my main query is very long and complicated so I will try to give a simple example here.

set @userid := 55;

select project, userid 
from users_projects
where userid IN 
(select userid from activeusers)
AND
project IN (select userid from paidprojects where userid = @userid)

So if select userid from paidprojects where userid = @userid returns an empty output, I want this last condition to be ignored.

I tried this

set @userid := 55;

select project, userid 
from users_projects
where userid IN 
(select userid from activeusers)
AND
project IN (IF count(select userid from paidprojects where userid = @userid)>0, (select userid from paidprojects where userid = @userid), (select userid from activeusers))

but it's not working and I am getting syntax error:(

Can you add some example data for that and the expected output — juergen d
– juergen d, Commented Jun 26, 2021 at 4:48
my main query is very long and complicated so I just gave this as a simple example. I guess it's a bad example. — Data_sniffer
– Data_sniffer, Commented Jun 26, 2021 at 5:51
No need to post the main query, just a sample data and your expected output to give us the general idea. Here, you can use this fiddle dbfiddle.uk/… — FanoFN
– FanoFN, Commented Jun 26, 2021 at 5:58
By the way, you probably don't need all those tables. Instead you can just have two tables of users and projects. Both of this table can each have an additional column like status and you can populate that with values like active/inactive (users) and paid/unpaid(projects). — FanoFN
– FanoFN, Commented Jun 26, 2021 at 6:06

Gordon Linoff · Accepted Answer · 2021-06-26 11:28:01Z

1

So if select userid from paidprojects where userid = @userid returns an empty output, I want this last condition to be ignored.

That is, you want only paid projects if there are any. Otherwise, you want all (unpaid) projects.

First, I assume that you want to compare projects in the last query not a project to a user. That just seems broken.

An explicit way to write this logic is:

select up.project, up.userid
from users_projects up
where up.userid in (select au.userid from activeusers au) and
      (up.project in (select pp.project from paidproject pp where pp.userid = @userid) or
       not exists (select pp.project from paidproject pp where pp.userid = @userid)
      );

answered Jun 26, 2021 at 11:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Data_sniffer Over a year ago

This is it. Thank you so much. I wish I could answer all people's questions but I could not because I am a dev and given these 2 queries without having full understanding of the DB.

Ernesto · Accepted Answer · 2021-06-26 04:50:47Z

1

Why would you do that ? Let me see if I understood you have

user

You’re users table

project

Where yow projects are

user_projects

I is guessing that’s an N to M

Question

Why do you have a

paidprojects table

That table can easily and should be a column on user_projects table as well as active users table that table can also be a column on users table is user active then true else false

Your problem can be solve by a simple left join

SELECT s.user_tbl_col, p.product _tbl_col
FROM users AS s
LEFT JOIN users_projects AS up ON u.id = up.userId
LEFT JOIN products AS p ON up.productID = p.id

If The user hadn’t buy none the users info would be the only data

answered Jun 26, 2021 at 4:50

Ernesto

4,3461 gold badge17 silver badges30 bronze badges

Collectives™ on Stack Overflow

SQL, where attribute in subquery if it return anything or else return everything

2 Answers 2

1 Comment

user

project

user_projects

Question

paidprojects table

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

user

project

user_projects

Question

paidprojects table

Comments

Your Answer

Sign up or log in

Post as a guest

Related