I just learned how to use JOINS (so please be gentle ;) ), and wrote this query:
SELECT 1
FROM `T1`
INNER JOIN `T2` ON `T1`.`T1_ID` = `T2`.`REQUIREMENT`
WHERE `T2`.`T1_ID` = XXX
AND `T1`.`STATEMENT` = YYY
AND `T2`.`REQUIREMENT` != 0 //last row does not work as intended!
It works perfectly without the last condition: if T1_ID from T1 does match REQUIREMENT from T2 for given T1_ID (XXX) - it does work. I wanted additional STATEMENT from T1 to be match (YYY) - and it still does work.
But then I realized, that I need to exclude one cause: when T2.REQUIREMENT is equal to 0, I want this query to return 1 regardless of the JOIN formula. The problem is, that if T2.REQUIREMENT = 0, I know for sure that there will not be any T1.T1_ID entry that will match the JOIN requirements. So I understand, that this last condition has no right to work like I'd wish it was.
What I need is some kind of IF statement. Something that would work like:
SELECT 1
IF (`T2`.`REQUIREMENT`!=0) //if true, don't even go to join, and return 1
OR (my previous join query)
The thing is, that I have no idea how to implement such IF statement into mysql.
Any ideas? Thanks.
Sample data:
T1:
id STATEMENT T1_ID
1 irrelevant 1
2 irrelevant 5
T2:
id T1_ID REQUIREMENT
1 1 0
2 2 0
3 3 1
4 4 3
5 5 4
6 6 5
7 7 6
Such setup should return 1 for T1_ID equal 1, 2, 3, 6.
In addition, if it's even possible in single query, I'd like it to return 1 as well even if T1 was empty, for all T2.REQUIREMENT=0 - in this case T1_ID equal 1, 2.
Just FYI, good start on your post and example... tableName.columnName references (or alias.columnName) should always be provided to prevent ambiguity that others don't know your table structure. Also, you only really need the tick marks for things like reserved words or column names that have spaces (never like these anyhow).
From my reading your question and sample tables T1 and T2... T1 appears to be some Lookup table and has IDs and descriptions associated to said IDs. Your T2 table appears to be your detail/transaction based table and it may or not have an actual requirement hence your desire to always include those records without a specific requirement.
If this is the case, it sounds like you want "all detail records that have some condition REGARDLESS of a matched requirement ID as found in the lookup table." If this is accurate, you would be looking for
Select
T2.T1_ID,
coalesce( T1.Statement, '' ) StatementFromT1Table
from
SomeMainTable T2
LEFT JOIN SomeLookupTable T1
on T2.T1_ID = T1.T1_ID
where
T2.T1_ID = SomeIdParameterValue
AND ( T1.T1_ID is NULL
OR T1.Statement = SomeOtherParameterValue )
The join between tables I always try to list the left-side table first, then indent to the right-side table and have my ON condition show the left.column = right.column so you always see the relationship and how table A gets to table B (and nested more as other joins come into play).
The different between (INNER) JOIN and LEFT JOIN is that (INNER) JOIN REQUIRES a record to always match on both tables. LEFT JOIN means I want everything from the table on the left side REGARDLESS of an actual match in the right table.
So at this point, I get all from T2 alias table first regardless of the answer in T1 alias. Now, how to deal with the zero remarks id value. If zero indicates you KNOW there wont be a match in T1, then you can just say I want all records on the T2 side if the T1 side IS NULL... But you also care for a specific statement, hence the OR within the parenthesis test.
The first part of the where is if you were specifically looking for all things of a T1_ID = some value, so that is a primary parameter and only applicable to the T2 side... you are qualifying the T1 side via the AND ( null or other equality test ) condition.
If you have some confidential data, it is ok to randomize / provide sample data, but having real table name reference / context will help us better understand what you are trying to accomplish. Ambiguity in table names and columns does not help us mentally understand and might have better query solutions having a better understanding.
If I am close and you need additional clarification, please advise and/or edit your original post with additional sample data and final output... such as parameters being filtered for too.
POST CLARIFICATION.
Per your comments, here are the clarifications...
The "SomeMainTable T2" is actually a breakdown of the actual table name within your database and "T2" is the ALIAS reference. Imagine your table name is "SomethingReallyLongDetail". Would you prefer to write your query something like
select
SomethingReallyLongDetail.Column1,
SomethingReallyLongDetail.Column7,
SomethingReallyLongDetail.Column20
from
SomethingReallyLongDetail
OR...
select
SRL.Column1,
SRL.Column7,
SRL.Column20
from
SomethingReallyLongDetail SRL
In this case, I used an alias "SRL" to more easily correlate to the table name as an acronym / abbreviation vs having to type the long value over and over, then have more chance of typing mistakes. Simply for readability providing the "alias" reference within the query. So, I did not know your ACTUAL table name, so I made it up but using the "T1" and "T2" references to stay in-line with your original post.
Next, COALESCE(). Since this query does a LEFT-JOIN, The right-side table may (or not) actually have a record match on the ID as you know might not always exist. Since I was trying to pull the "Statement" column from that second table (alias T1), that description could be NULL which you probably would not want to show in any sort of output. To prevent that, COALESCE() says, give me the value from the first parameter in the list... If that value is null, give me the second value. In this case the second value is just an empty string.
Parameters in the query. Your original query had reference to XXX and YYY such as you knew of a specific T1.ID value you wanted to narrow down to pulling out, but a different value YYY as being part of the statement. So the place where you had an "XXX", I just put a place-holder here for you to apply/put any value you were specifically looking for. Similarly for your "YYY" value, another place-holder for that. Just substitute whatever criteria you were looking for.
Finally that AND part of the where clause. This is for the condition of the LEFT-JOIN. Since you KNOW that not all records will have a match in the "T1" secondary table, with the LEFT JOIN, the ID will be found and HAVE a value, or there will not be a value and thus NULL.
If there is no matching record, you would never be able to compare some string, int, date, whatever to a column as it would be null. So I am doing
(T1.T1_ID IS NULL -- as in there was no match
OR T1.Statement = SomeOtherParameterValue ) -- there WAS a match, and I only want where the statement equals a given value.
Per your comments and example results, your query SHOULD be simplified to...
Select
T2.ID,
T2.T1_ID,
T2.Requirement,
coalesce( T1.Statement, '' ) StatementFromT1Table
from
T2
LEFT JOIN T1
on T2.Requirement = T1.T1_ID
where
T2.Requirement = 0
OR T1.T1_ID IS NOT NULL
In your case, the final answer is... I want all records where there is no requirement (thus = 0) OR the record DOES have a match in the T1 table (thus T1.T1_ID IS NOT NULL)