The following is a query from SQL Server:
SELECT * FROM ITEM WHERE item.name = hank
If @@ROWCOUNT < 20 THEN
UNION
SELECT * FROM ITEM WHERE item.name = jim
ENDIF
Is this possible in MySQL?
2 Answers
If I'm understanding correctly, you don't need union for that. One option would be to use order by with limit:
select *
from item
where name in ('hank','jim')
order by name
limit 20
This will select 20 rows from the item table, starting with those rows where name = 'hank'. You could also consider using:
order by case when name = 'hank' then 0 else 1 end
limit 20
- Condensed Fiddle Demo (change the limit to see)
2 Comments
DrivingInsanee
This is not really what I'm looking for. My actual query is unbelievably complicated, so this will not work for me. What is really need, is just a simple solution for seeing if the row count is more than 10, and then union select a second query
sgeddes
@DrivingInsanee -- if you want to include if logic in your sql, you're going to need to create a stored procedure. Here's a slightly different take on the posted solution which might help (based on same concept though): sqlfiddle.com/#!9/b6c49/4
I'm not sure if this behaviour is documented/guaranteed but in the past I found the following worked.
SELECT SQL_CALC_FOUND_ROWS *
FROM Item
WHERE Name ='Hank'
UNION ALL
SELECT *
FROM Item
WHERE FOUND_ROWS() < 20 AND Name = 'Jim';
Comments
IFstatement you are using (which is not theIFfunction) only works in stored procedures.@@ROWCOUNTis SQL Server-specific so I've edited your question with what I'm guessing is accurate. Feel free to clarify things further.