SQL Do Inner Join If Condition Met

SQL do inner join if condition met

This should (approxmately) do the same thing:

SELECT
     .......
  FROM myTable
  INNER JOIN table ON table.param1=myTable.param1
  LEFT JOIN systemTable on systemTable.param2=myTable.param2 and @SystemMerge = 1
  WHERE (@SystemMerge = 0 OR systemTable.NonNullableColumn IS NOT NULL)

Of course, this also means that any other references to columns within systemTable must be written to expect such columns to be NULL.

SQL JOIN only when a condition is true

You need to give that condition while joining itself.

ALTER PROCEDURE [dbo].[Search] 
@one NVARCHAR(50), @two NVARCHAR(50), @three NVARCHAR(50)

SELECT  cinfo.ID,
    cinfo.Nam,
    cinfo.INAM,
    cinfo.CA,
    cinfo.Form,
    cinfo.Std,
    cval.Prop,
    cval.Cons,
    sc.Accep

From dbo.Info AS cinfo
Inner JOIN dbo.values AS cval
    ON cinfo.ID = cval.ID
INNER JOIN dbo.Sources AS sc
    ON (cval.sID = sc.sID AND sc.Accept = 'A')
       LEFT JOIN dbo.Synonym AS synm
    ON (cinfo.ID = synm.ID and @three is not null) **<---------------------**

where (cinfo.NAM LIKE '%'+@one+'%' OR cinfo.CAS LIKE '%'+@two+'%' OR 
  synm.SynonymID LIKE '%'+@three+'%') AND
    (cval.PropID = '1' OR
    cval.PropID = '2' OR
    cval.PropID = '3' OR
    cval.PropID = '4' OR)

Inner Join with restriction if a criteria is met

Condition stating "if Foo then Bar" means that when foo is false, the condition is satisfied, and when foo is true (or false), the condition is satisfied when Bar is true, so:

if Foo is true then Bar must be true

means

WHERE ( NOT FOO  OR  BAR )

For the query in the question, that means:

WHERE c.ClientIsActive = 1
  AND ( dd.DataDefinitionValueTest <> "PA"
     OR dd.DataDefinitionValueTest IS NULL -- remove if column is NOT NULL
     OR cs.ClientSupporterRemarks = "B" )

For the query in the question, you can also add that extra set of conditions to the dd JOIN, instead of to the WHERE clause, it makes no difference.

INNER JOIN [dbo].[DB_DataDefinitions] dd
        ON cs.ClientSupporterRole = dd.DataDefinitionValueID 
       AND dd.DataDefinitionName = 'ClientSupporterRole'
       AND ( dd.DataDefinitionValueTest <> "PA"
          OR dd.DataDefinitionValueTest IS NULL -- remove if column is NOT NULL
          OR cs.ClientSupporterRemarks = "B" )

Inner join with if condition

An inner join will only return a row if matches are found in both sides of the join. If you're looking for something that will return all rows from Table1 but only records from Table2 when a match is found, you want a left outer join:

select * from Table1 as t1
left outer join Table2 as t2
    on t1.RepID = t2.RepID
where t1.Date = @Date
order by t1.Date desc

JOIN table if condition is satisfied, else perform no join

Your description a bit confusing, but the title says:

"else perform no join"

So I think you want something this:

SELECT ti.*
FROM   transaction t
LEFT   JOIN transaction_item ti ON ti.unique_id        = t.unique_id  
                               AND ti.location_id      = t.location_id 
                               AND ti.transaction_date = t.transaction_date
                               AND NOT EXISTS (
                                    SELECT FROM transaction_item tx
                                    WHERE  tx.unique_id        = t.unique_id  
                                    AND    tx.location_id      = t.location_id 
                                    AND    tx.transaction_date = t.transaction_date
                                    AND    tx.amount < 2)  -- !
WHERE  t.pos_transaction_id = 4220
AND    t.location_id = 1674
AND    t.transaction_date = '2020-05-08';

However, the LEFT JOIN in combination with only columns from the right table in the SELECT list is dubious at best. This way you get one row with all NULL values for every row in transaction that qualifies and has no matching rows in transaction_item or one or more matching rows with amount < 2. I doubt you want that.

Typically, you'll want to include columns from transaction in the SELECT list, or use an [INNER] JOIN instead.

So it could be:

SELECT ti.*
FROM   transaction t
JOIN   transaction_item ti USING (unique_id, location_id, transaction_date)
WHERE  t.pos_transaction_id = 4220
AND    t.location_id = 1674
AND    t.transaction_date = '2020-05-08';
AND    NOT EXISTS (
   SELECT FROM transaction_item tx
   WHERE  tx.unique_id        = t.unique_id  
   AND    tx.location_id      = t.location_id 
   AND    tx.transaction_date = t.transaction_date
   AND    tx.amount < 2);

Then again, a table transaction_item would typically have a FK column transaction_id referencing transaction - in which case we can simplify

SELECT ti.*
FROM   transaction t
JOIN   transaction_item ti ON ti.transaction_id = t.transaction_id -- ?
WHERE  t.pos_transaction_id = 4220
AND    t.location_id = 1674
AND    t.transaction_date = '2020-05-08';
AND    NOT EXISTS (
   SELECT FROM transaction_item tx
   WHERE  tx.transaction_id = t.transaction_id
   AND    tx.amount < 2);

You mentioned both pos_transaction_id and transaction_id, so I am guessing here ...

The major insight to take away from this: always show exact table definitions with any such question.

Conditional Inner Join

Try putting both tables in the query using LEFT JOIN's

LEFT JOIN TimeRegistration TR ON r.rid = TR.Id AND RegT.type =1 
LEFT JOIN DrivingRegistration DR ON r.rid = DR.Id AND RegT.type <>1 

Now, in you select clause, use

CASE RegType.Type WHEN 1 THEN TR.SomeField ELSE DR.someField END as SomeField

The other option is to use dynamic SQL

SQL only join if conditions are met

Sounds like you really want to do a Left Join. A Left Join will give you the data if it exists, but otherwise, will show null data for photos.

Maybe something like this:

Select *
From Posts
   Left Join Photos on Posts.MEDIA = Photos.MEDIA 
                    and Photos.Type = 'b'

If this was just text, it'd look like:

Type    Media    Photo
a       w/e
b       w/e      cats.jpg

---

Related Topics