Select Items Like Records from a Column in Another Table

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

SQL Query in which the WHERE clause uses LIKE on all data from a column in another table

I suspect that this will be the more "perfomant" option (I use quotes, as using LIKE with a leading wildcard will make the query non-SARGable):

SELECT *
FROM dbo.articles a
WHERE EXISTS (SELECT 1
              FROM dbo.keywords k
              WHERE a.TextValue LIKE '%' + k.keyword + '%');

This will avoid duplicate rows, and a costly DISTINCT; as I suspect that TextValue could have some lengthy values.

Select rows in one table, based on column values in another table?

You seem to be looking for conditional aggregation. Using this technique, your query can be simplified as follows, to avoid the need for multiple inline subqueries:

select  Company_ID,  
        max(case when ds.Department='Sales' then ds.StatusCode end) as SalesStatus,
        max(case when ds.Department='Inventory' then ds.StatusCode end) as InvStatus,
        max(case when ds.Department='Retail' then ds.StatusCode end) as RetailStatus,
        max(case when ds.Department='Main' then ds.StatusCode end) as MaintStatus
from    
    Company cm
    inner join DepartmentStatus ds on ds.Company_ID = cm.Company_ID 
Where   cm.CompanyID=1
group by cm.CompanyID 

Select column from another table based on matching condition

I would just use a correlated subquery:

select t1.*,
       (select t2.limit
        from table2 t2
        where t2.date <= t1.date
        order by t2.date desc
        limit 1
       ) as Limit_At_Time
from table1 t1;

Usually in these types of problems, the comparison is <= rather than <, so I used that. Of course, the exact equivalent to your query is <.

Filtering selecting column with like clause from unrelated another table - Postgresql?

You can do this without like '%BC%' , using just plain string matching. (don't expect it to be fast; you'll need trigrams for performance)

---

CREATE TABLE aaa
        ( ii integer not null primary key
        , vv varchar
        );
INSERT INTO aaa ( ii , vv ) VALUES ( 1, 'ABCD' ) , ( 2, 'DBCA' ) , ( 3, 'ACBD' );

CREATE TABLE bbb
        ( ii integer not null primary key
        , vv varchar
        );
INSERT INTO bbb ( ii , vv ) VALUES ( 1, 'BC' ) ;

SELECT * FROM aaa a
WHERE EXISTS (
        SELECT * FROM bbb b
        -- WHERE POSITION (b.vv IN a.vv) > 0 
        WHERE NOT POSITION (b.vv IN a.vv) > 0
        );

---

Results:

---

CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 1
 ii |  vv  
----+------
  3 | ACBD
(1 row)

Get records from one table, based on related data from another table

I would add an EXISTS clause:

SELECT TOP 1000 [Id]
  ,[SiteId]
  ,[UserName]
  ,[Visited]
  ,[Created]
  ,[Status]
FROM [tblUserVisit] tuv
WHERE visited = 'lilje' AND siteid = '3' AND status = '0' AND
      EXISTS (SELECT 1 FROM [tblUserNotify] tun
              WHERE tun.UserName = tuv.Visited AND [Type] = '7');

How to select rows from a table based on a column value in another table that has multiple values?

Don't set this solution as a good practice because as all comments said above:

Never, ever store data as comma separated items.

Workaround:-

Manipulate the values that selected form user table for getting a good structure via using Replace function as next demo:-

Create table #user (username varchar(10), Areas varchar (100))
go
insert into #user values ('User1', 'KA,TN,AP,GJ')
insert into #user values ('User2', 'MH,UP,MP,GJ')
go
Create table #order ( OrderID int , ProductID varchar(10), Qty int , Area char (2))
go
insert into #order values (1, 'Prod1', 10, 'GJ')
insert into #order values (2, 'Prod1', 22, 'MH')
insert into #order values (3, 'Prod2', 3,  'AP')
insert into #order values (4, 'Prod2', 77, 'TN')
go 

declare @List nvarchar(250)

select @List = '('''+(select Areas from #user where username = 'User1') + ''')'
select @List = replace(@List,',',''',''')

exec ('select ProductID,Qty, area from #order
where Area in' +  @List )

drop table #user
drop table #order

Result:-

Note: I assumed the 3rd record is missing in your desired result set by mistake.

---

Related Topics