Member Avatar for arcticM

I have a table like this:

tableA: id, a, b, c
sample data:

1, x, y, sample1-get-by-y
2, x, y, sample2-get-by-y
3, x, '', sample1-get-by-x
4, x, '', sample2-get-by-x
5, '', '', saple1-general
6, '', '', saple2-general

I want to get the data like this:
if I have data where field b is 'y' - get me those rows (row 1 and 2)
if I don't have rows where field b is 'y', look for rows where field a is 'x' - (rows 3 and 4)
if I don't have rows where field a is 'x', then get me the rows where fields a and b are empty (rows 5 and 6)
(if I'd run this on the data from above I should only get rows 1 and 2, because once i do get result for b=y I should stop looking for other options)

so basicly those are the 3 queries-
SELECT * FROM tableA WHERE b= 'y';

SELECT * FROM tableA WHERE a= 'x';

SELECT * FROM tableA WHERE a = '' AND b = ''

what I need help with, is how do I combine all of this into 1 query? so that I won't have to run a query and then check the result and run the next one if needed and etc..

thanks in advance!

Edited by arcticM
  • 3 Contributors
  • 5 Replies
  • 138 Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for urtrivedi

SELECT * FROM tableA WHERE (b= 'y' or (b<>'y' and a='x') or (a<>'x' and b<>'y' and a = '' AND b = '') )

Edited by urtrivedi
Member Avatar for arcticM

but in this case I get results for more than 1 case.. I want to get results only for 1 case.. first to check the b culumn, and if there are rows that match then get only those and not continue with the rest of the cases (if a='x'.. or a='' and b='')
I tried using XOR but I still get the union of all the ORs..

Member Avatar for smantscheff

Along those lines:

select * from tableA where b = 'y' or
((not exists (select * from tableA where b = 'y')) and a = 'x') or
((not exists (select * from tableA where a = 'x' or b = 'y')))
Member Avatar for arcticM

it gives me mysql errors..and again all the ORs make me doubt that this is the solution..

so far I have this-

SELECT SQL_CALC_FOUND_ROWS *
FROM table
WHERE b = 'y'

UNION ALL

SELECT *
FROM table
WHERE
FOUND_ROWS() = 0 AND a = 'x'

this works great for the first 2 queries. now if someone could just help me figure out how to add the 3rd part (in case there's no b=y and no a=x) I tried few things but I get mysql errors :S
SO CLOSE!

Member Avatar for smantscheff

Maybe that my query gives sql errors - it's not tested, and some brackets may be unbalanced. But it reflects the logic of your demand most accurately.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.