Using Same Column Multiple Times in Where Clause

How to use same columns multiple times in where condition

If you want to get products that have both keys,

SELECT p.*
FROM products p join
     (SELECT product_id 
      FROM cs_inventory 
      WHERE ((meta_key = 'product_image' AND meta_value <> '') OR
             (meta_key = 'product_type' AND meta_value= 'Soap' )
            ) AND
            user_id = 6
      GROUP BY product_id
      HAVING COUNT(*) = 2
      LIMIT 10
     ) i
     ON p.product_id = i.product_id;

One row cannot have multiple values for a single column, so your subquery will never return any value.

Instead, this uses OR and checks that both rows are available for the product.

SQL WHERE clause same column multiple times?

select dp.value as department,
       n.value as name,
       dt.value as date
from
    (select formID, value from table1 where id = 25) as dp
    inner join (select formID, value from table1 where id = 16) as n
               on dp.formID = n.formID
    inner join (select formID, value from table1 where id = 5) as dt
               on dp.formID = dt.formID
    inner join (select formID, value from table1 where id = 30) as f
               on dp.formID = f.formID
where f.value = 'FormXYZ';

OR

select
    case when id = 25 then value end as department,
    case when id = 5 then value end as date,
    case when id = 16 then value end as name
from table1
where formId in (select formID from table1
                 where id = 30 and value = 'FormXYZ')
      and id in (5,16,25);

SQL select same column multiple times with different conditions

The below query should work,

SELECT 
    b.brc_id AS [Branch ID]
    ,s.stf_first_name AS [Manager First Name]
    ,s.stf_last_name AS [Manager Last Name]
    ,COUNT(CASE WHEN s.stf_position = 'Supervisor' THEN 1 ELSE 0 END) AS [Number of Supervisor]
    ,COUNT(CASE WHEN s.stf_position = 'Staff' AND s.stf_gender = 'Male' THEN 1 ELSE 0 END) AS [Male Staff]
    ,COUNT(CASE WHEN s.stf_position = 'Staff' AND s.stf_gender = 'Female' THEN 1 ELSE 0 END) AS [Female Staff]
FROM BRANCH_T b
INNER JOIN STAFF_T s 
    ON b.brc_id = s.stf_brc_id AND b.brc_manager = s.stf_id
GROUP BY b.brc_id,s.stf_first_name,s.stf_last_name

Using Same Column multiple time in SQL WHERE clause

Presumably, you want employees that have these conditions. If so, you can use aggregation:

SELECT EMPLOYEE
FROM EMPLOYMENT
WHERE (CATEGORY = 1 AND STATUS LIKE '%A%') OR
      (CATEGORY = 2 AND STATUS = 'B')
GROUP BY EMPLOYEE
HAVING COUNT(DISTINCT CATEGORY) = 2;  -- matches both

basic sql : selecting the same column multiple times in one query, when each occurrence is dependent on different where clause

You should use 3 queries. It will be a lot faster with proper indexing when self joins. Additionally it will be more readable.

If you would like one query call, it might be this :)

SELECT
(SELECT x FROM table WHERE y=1) AS x1,
(SELECT x FROM table WHERE y=2) AS x2,
(SELECT x FROM table WHERE y=3) AS x3

SAS Same column selection multiple times using multiple where clause as different output columns

I think you just want conditional aggregation. The CASE expression is the argument to the MIN():

SELECT t1.userid,
       MIN(CASE WHEN t1.flag1 = 'N' THEN t1.YearMonth END) end as YearMonth1,
       MIN(CASE WHEN t1.flag2 = 'N' THEN t1.YearMonth END) end as YearMonth2
FROM t1
GROUP BY t1.userid

SQL - Multiple conditions where clause the same column

select  Subject_ID from (
     Select Distinct Subject_ID, Diagnosis_ID
       From
       Table_1
       Where Diagnosis_ID=299 or Diagnosis_ID=288
)
Group By Subject_ID
Having count(Subject_ID)>=2

MYSQL Update same column with multiple where clause

You could use a case expression:

UPDATE mytable
SET    recent = CASE WHEN recent = 1 THEN 0
                     WHEN id = 555   THEN 1
                END
WHERE  recent = 1 OR id = 555

---

Related Topics