CODEWITHSUNDEEP
mysqlsql
Caweren
Caweren

Reputation: 244

MySQL if rows exist JOIN else SELECT

i'm having trouble getting this thing solved.

How would i go about doing this in MySQL?

I have 2 tables, one with products, and another with lists of products linked to users.

If the user has products linked to them, select ONLY those products from the products table. If the user does NOT have any products linked to them, then select ALL products.

I have tried various options, but i can't seem to get it to work :(

EDIT
Table structure:

Products:
|  Supplier  |  partnumber  |  price  |
|------------|--------------|---------|
|      1     |      1b      |   4.00  |
|      4     |      13-f    |  12.00  |
|____________|______________|_________|



Lists
|   userid   |  partnumber  |
|------------|--------------|
|      37    |      1b      |
|____________|______________|

The query should only select product with id 1b.

Users with another id should select both 1b and 13-f

EDIT 2

The userid is stored in a PHP session, so the users table should not be relevant!

Upvotes: 0

Views: 150

Answers (2)

GolezTrol
GolezTrol

Reputation: 116100

You can union the part where rows exist to the part where a user has no products:

select
  u.UserId,
  p.ProductId,
  p.ProductName
from
  Users u
  inner join UserProducts up on up.UserId = u.UserId
  inner join Products p on p.ProductId = up.ProductId
union all
select
  u.UserId,
  p.ProductId,
  p.ProductName
from
  Users u
  cross join Products p
where
  not exists (select 'x' from UserProducts up where up.UserId = u.UserId)

Instead of using not exists, you can use a left join for this too. Some say this is faster in MySQL, although it will depend on the circumstances:

select
  u.UserId,
  p.ProductId,
  p.ProductName
from
  Users u
  inner join UserProducts up on up.UserId = u.UserId
  inner join Products p on p.ProductId = up.ProductId
union all
select
  u.UserId,
  p.ProductId,
  p.ProductName
from
  Users u
  cross join Products p
  left join UserProducts up on up.UserId = u.UserId
where
  up.UserId is null

Either way, the query looks big, because they are actually two queries joined together. But therefor it is also readable and reasonable fast. Union all causes hardly any overhead at all.

Since your additions suggest that you have a single userid already, your query could look like this:

select
  p.ProductId,
  p.ProductName
from
  UserProducts up
  inner join Products p on p.ProductId = up.ProductId
where
  up.UserId = <YOURUSERID>
union all
select
  p.ProductId,
  p.ProductName
from
  Products
where
  not exists (select 'x' from UserProducts up where up.UserId = <YOURUSERID>)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Here is one method that uses union all:

select u.*, up.productid
from users u join
     userproducts p
     on u.userid = up.userid
union all
select u.*, p.productid
from users u cross join
     products p
where not exists (select 1 from userproducts up2 where up2.userid = u.userid);

EDIT:

If you are looking for just one user id, I would suggest:

select p.*
from userproducts up join
     products p
     on up.productid = p.productid and
        u.userid = $userid
union all
select p.*
from products p
where not exists (select 1 from userproducts up2 where up2.userid = $userid);

Upvotes: 1

Related Questions