Select from one table matching criteria in another?
The simplest solution would be a correlated sub select:
select
A.*
from
table_A A
where
A.id in (
select B.id from table_B B where B.tag = 'chair'
)
Alternatively you could join the tables and filter the rows you want:
select
A.*
from
table_A A
inner join table_B B
on A.id = B.id
where
B.tag = 'chair'
You should profile both and see which is faster on your dataset.
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 <
.
SQL: Find entries with matching criteria in different table
The best way to solve this problem is to not use the EAV database model (Entity-Attribute-Value). You're running into just the first of many problems with this anti-pattern. A quick Google search on "EAV model" should reveal some of the other problems in store for you if you choose not to redesign. Normally your Event
table should have a column for foo
and a column for qux
.
One possible solution that you can use, if you insist (or are forced) to go down this path:
SELECT id, content
FROM Event
WHERE id IN
(
SELECT
E.id
FROM
Event E
INNER JOIN Event_Tag T ON
T.event_id = E.id AND
(
(T.type = 'foo' AND T.value = 'bar') OR
(T.type = 'qux' AND T.value = 'quux')
)
GROUP BY
E.id
HAVING
COUNT(*) = 2
)
If you put your various type/value pairs into a temporary table or as a CTE then you can JOIN
to that instead of listing out all of the pairs that you want. That syntax will be dependent on your RDBMS though.
Select records in on table based on conditions from another table?
I will do it in this way (it should be valid in most database systems:
select *
from b
where type_id in (
select type_id
from a
where status = true
)
To your question about if yours is a good way, my answer is no, it is not a good way because it likely forces a big intermediate record set (by the joining) then a time consuming distinct on the intermediate record set.
UPDATE
After some thought I realized there is no absolute good or bad solution. It all depends on the data your have in each table (total records, value distribution, etc...). So go ahead with the one that is clearly communicate the intention and be prepared to try different ones when you hit a performance issue in production.
select the records only if ALL related records match
If you want the rows from m
where all statuses are 3
in I
, then use not exists
:
select m.*
from table1 m
where not exists (select 1
from table2 I
where I.Id = m.Id and I.status <> 3
);
EDIT:
Note that this matches rows where there are no matches in table2
. That technically meets the requirement that all rows have a status of 3
. But if you want to require a row, you can add an exists
condition:
select m.*
from table1 m
where not exists (select 1
from table2 I
where I.Id = m.Id and I.status <> 3
) and
exists (select 1
from table2 I
where I.Id = m.Id and I.status = 3
);
Both of these can take advantage of an index on table2(Id, status)
. Methods that use some form of aggregation require additional work and should be a little less performant on large data sets (particularly when there are many matches in table2
).
Select all from one table where some columns match another select
After lots of comments I think this is what you're after...
SELECT T1.*
FROM table1 t1
LEFT JOIN table2 t2
on T1.ID = T2.ID
and T1.Name = T2.Name
AND E2.event_Time_UTC between convert(datetime,'2016-02-09 00:00:20',101) and convert(datetime '2016-02-09 23:59:52',101)
WHERE T2.Name is null
AND E1.Event_Time_UTC between convert(datetime,'2016-02-09 00:00:20',101) and convert(datetime,'2016-02-09 23:59:52',101)
You may allow implicit casting to work but above is the explicit approach.
If not then you would need to cast the string dates to a date time, assuming Event_Time_UTC is a date/time datatype.
A left join lets us return all records from the 1st table and only those that match from the 2nd.
The t1.* returns only the columns from table1. The join criteria (on) allows us to identify those records which match so they can then be eliminated in the where clause by 'where t2.name is null' they will always be null when no record match in t2.
Thus you get a result set that is: all records from t1 without a matching record on name and id in table2.
Old version
The below content is no longer relevant, based on comments.
I redacted previous answer a lot because you're using SQL Server not MySQL and I know you want multiple records not table1 and table2 joined.
In the below I create two tables: table1 and table2. I then populate table1 and table2 with some sample data
I then show how to get only those records which exist in one table but not the other; returning a separate ROW for each. I then go into detail as to why I choose this approach vs others. I'll finally review what you've tried and try to explain why I don't think it will work.
create table table1 (
ID int,
name varchar(20),
col1 varchar(20),
col2 varchar(20),
col3 varchar(20));
Create table table2 (
id int,
name varchar(20));
Insert into table1 values (1,'John','col1','col2','col3');
Insert into table1 values (2,'Paul','col1','col2','col3');
Insert into table1 values (3,'George','col1','col2','col3');
Insert into table2 values (1,'John');
Insert into table2 values (4,'Ringo');
Option 1
SELECT T1.name, T1.ID, T1.Col1, T1.Col2, T1.Col3
FROM Table1 T1
LEFT JOIN Table2 T2
on T1.Name = T2.Name
and T1.ID = T2.ID
WHERE T2.ID is null
UNION ALL
SELECT T2.name, T2.ID, NULL, NULL, NULL
FROM Table1 T1
RIGHT JOIN Table2 T2
on T1.Name = T2.Name
and T1.ID = T2.ID
WHERE T1.ID is null ;
which results in...
Notice John isn't there as it's in both tables. We have the other 2 records from table1, and the ID, name from table2 you're after.
Normally I would do this as a full outer join but since I think you want to reuse the name and id fields to relate to BOTH tables in the same column we had to use this approach and spell out all the column names in table 1 and put NULL for each column in table1 when displaying records from table2 in order to make the output of the second query union to the first. We simply can't use *
Option 2: Using a full outer join... with all columns from T1
SELECT T1.*
FROM Table1 T1
FULL OUTER JOIN Table2 T2
on T1.ID = T2.ID
and T1.Name = T2.Name
WHERE (T1.ID is null or T2.ID is null)
you get this... which doesn't show Ringo...
But then I would ask why you need anything from Table 2 at all so I think you're wanting to still show the ID, Name from table2 when it doesn't exist in table1.
Which is why What I think you're after is the results from the 1st query using the union all.
Option 3 I suppose we could avoid the second query in option 1 by doing...
SELECT coalesce(T1.Name, T2.name) as name, coalesce(T1.Id,T2.ID) as ID, T1.col1, T1.Col2, T1.Col3
FROM Table1 T1
FULL OUTER JOIN Table2 T2
on T1.ID = T2.ID
and T1.Name = T2.Name
WHERE (T1.ID is null or T2.ID is null)
which gives us what I believe to be the desired results as well.
This works because we know we only want the name,id from table2 and all the column values in table1 will be blank.
Notice however in all cases we simply can't use Tablename.* to select all records from table1.
This is what you tried:
( SELECT name,id FROM table1
EXCEPT
SELECT name,id FROM table2)
UNION ALL
( SELECT name,id FROM table2
EXCEPT
SELECT name,id FROM table1)
- Assuming you want to reuse the ID, Name fields; you can't select *. Why? because the records in Table2 not in table1 aren't in table1. In my example if you want Ringo to show up you have to reference table2! Additionally, * gives you no ability to "Coalesce" the ID and name fields together as I did in option 3 above.
- If you ONLY want the columns from table1, that means you will NEVER see data from table2. If you don't need the data from table2, (such as ringo in my example) then why do we need to do the union at all?) I'm assuming you want ringo, thus you HAVE to somewhere reference name, id from table2.
MYSQL - sum values of rows from one table based on criteria from another
it is the same, you need to join the tables
SELECT SUM(cost) FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id WHERE colour='blue';
| SUM(cost) |
| --------: |
| 80 |
db<>fiddle here
How to select rows with no matching entry in another table?
Here's a simple query:
SELECT t1.ID
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
The key points are:
LEFT JOIN
is used; this will return ALL rows fromTable1
, regardless of whether or not there is a matching row inTable2
.The
WHERE t2.ID IS NULL
clause; this will restrict the results returned to only those rows where the ID returned fromTable2
is null - in other words there is NO record inTable2
for that particular ID fromTable1
.Table2.ID
will be returned as NULL for all records fromTable1
where the ID is not matched inTable2
.
Related Topics
Row-Level Trigger VS Statement-Level Trigger
Using Bind Variables with Dynamic Select into Clause in Pl/Sql
Get Values from First and Last Row Per Group
Select Distinct from Multiple Fields Using SQL
Splitting Comma Separated Values in Columns to Multiple Rows in SQL Server
Access Substitute for Except Clause
With Check Add Constraint Followed by Check Constraint VS. Add Constraint
Postgresql How to Create a Copy of a Database or Schema
Max VS Top 1 - Which Is Better
How to Find Out What Is Locking My Tables
Performance of Like '%Query%' VS Full Text Search Contains Query
T-SQL Skip Take Stored Procedure
Eliminate and Reduce Overlapping Date Ranges
SQL Server - Query Short-Circuiting
SQL . the Sp or Function Should Calculate the Next Date for Friday
Export Table Data from One SQL Server to Another
T-Sql: Using a Case in an Update Statement to Update Certain Columns Depending on a Condition