SQL select object which matches multiple values from column and row


I need a little assistance how to fix this. This image will help explain:

The object id = 2635. This picture is already the result of a join on oId.
Now how do i select an object when it must be all these values:
ftId = 149 and its value = 1529
ftId = 201 and its value = 1

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Just to check - is it ftld or tfld?  That part has to be right.

You can use parentheses in SQL WHERE clauses to express OR and AND conditions.  Something like this, maybe:

SELECT ... WHERE (tfld = 149 AND value = 1529) OR (tfld = 201 AND value = 1)
peps03Author Commented:
It is tf, i made a typo in my example.

Maybe i should ask it differently, tfId is the form field, value is the value.

So i want to select everything where tfId 149 = 1529 AND tfId 201 = 1.
Ray PaseurCommented:
The way I understand the image with the question goes like this:

1. There are columns named ofld, old, tfld, value
2. There are rows running horizontally under the names of the columns
3. Each of the rows has four integers corresponding to the positions of the columns

If that's not how we should interpret the image, please post the CREATE TABLE statements for these tables and the query that created the output we see above, thanks.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

peps03Author Commented:
Yes correct.
ofId = yes the identifier
oId = the id of the object the values of tfId and value belong to
tfId = the description / the form field identifier
value = the value entered

So we can't just check is object 2635 has value 1. We must check if object 2635 has tfId 201 which is value 1.

And same for tfId 149 that must be 1529 and it won't matter if of tfId 150 has value 1529.

Hope this explains better.
Ray PaseurCommented:
Sounds like this might be closer?

SELECT ... WHERE (old = 2635 AND tfld = 149 AND value = 1529) OR (old = 2635 AND tfld = 201 AND value = 1)

The order of the elements in the WHERE clause might matter to performance.  You can use EXPLAIN SELECT to find out.
peps03Author Commented:
All conditions must be present, so:
tfld = 149 MUST have value = 1529 and also  tfld = 201 Must have value = 1
OR would mean there is a choice right?
Julian HansenCommented:
If I understand the problem you are wanting to isolate an object based on values that exist in multiple rows. So if an object has a row with  tfld=149 and a value = 1529 and also a row with tfld=201 and a value = 1 then you want to select that.

You will need to join two queries to do that. The following query shows how to do this. I have assumed your source table is called tablename.
SELECT * FROM tablename t1, tablename t2 
WHERE t1.tfld=149 AND t1.value=1529 AND t2.tfld=201 AND t2.value=1 AND t2.old = t1.old;

Open in new window

The above query joins two tables together and looks for rows where the tfld and value fields are 149 and 1529 respectively for the first instance of the table and 201 and 1 for the second instance of the table and where the old values are the same.

The result will be a single row per old of all old that match those criteria.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
OR is disjunctive, meaning there is a choice among mutually exclusive conditions.  These are the conditions aggregated inside the parentheses by the conjunctive AND.  Going back to the original question, we have this:

ftId = 149 and its value = 1529
ftId = 201 and its value = 1

Obviously that is impossible if it is considered an atomic statement, because tfld cannot simulatneously be both 149 and 201.  If all you want is the data associated with tfld = 149, then the query might look like this:

SELECT ... WHERE old = 2635 AND tfld = 149 AND value = 1529

The way to read the query is to analyze the parenthetical questions first and if any of them are true, the row satisfies the WHERE clause.  So the logic for this query goes something like this:

SELECT ... WHERE (old = 2635 AND tfld = 149 AND value = 1529) OR (old = 2635 AND tfld = 201 AND value = 1)

Take the row and ask these three WHERE questions.  Only if all of them are "yes" will the row pass the first of the two OR conditions:
1. Does this row contain old = 2635?
2. Does this row contain tfld = 149?
3. Does this row contain value = 1529?

If all three of the questions have been answered "yes" the row passes the test and becomes part of the results set.  If any of the questions in the first WHERE group are "no," then we go on to the next three questions:
1. Does this row contain old = 2635?
2. Does this row contain tfld = 201?
3. Does this row contain value = 1?

If all three of these new questions have been answered "yes" the row passes the test and becomes part of the results set.  If any of the questions are "no," then we have exhausted the possible conditions that can allow us to SELECT this row, and the row does not become part of the results set.
peps03Author Commented:
Many thanks Julian Hansen!! This works as a charm! Very nice! Much appreciated!

@ray also thanks for your help!
Ray PaseurCommented:
So the part about "old = 2635" was always a red herring?  Amazing!
Julian HansenCommented:
You are welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.