Menu
One SQL code can have one or more than one nested query. Syntax: SELECT * FROM table_name WHERE column_name=( SELECT column_name FROM table_name); Query written after the WHERE clause is the subquery in above syntax.
(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
You should use the WHERE clause to filter the records and fetching only the necessary records. The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.
The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
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.
You should make tags their own table with a linking table.
items:
id object
1 lamp
2 table
3 stool
4 bench
tags:
id tag
1 furniture
2 chair
items_tags:
item_id tag_id
1 1
2 1
3 1
4 1
3 2
4 2
select a.id, a.object
from table_A a
inner join table_B b on a.id=b.id
where b.tag = 'chair';
I have a similar problem (at least I think it is similar). In one of the replies here the solution is as follows:
select
A.*
from
table_A A
inner join table_B B
on A.id = B.id
where
B.tag = 'chair'
That WHERE clause I would like to be:
WHERE B.tag = A.<col_name>
or, in my specific case:
WHERE B.val BETWEEN A.val1 AND A.val2
More detailed:
Table A carries status information of a fleet of equipment. Each status record carries with it a start and stop time of that status. Table B carries regularly recorded, timestamped data about the equipment, which I want to extract for the duration of the period indicated in table A.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With