If you ONLY want columns returned from table2 then:
select table2.*
from table2
join table1
on table2.rowid = table1.rowid
where table1.name like '%smth%';
which is really the same thing as:
select table2.*
from table2, table1
where table2.rowid = table1.rowid
and table1.name like '%smth%';
The difference between select * and select table2.* is that the latter will
only return columns from table2 whereas the former will return all columns both
from table1 and from table2 in the order in which the tables were joined (which
in this case will always be table1 then table2, but for many queries you cannot
so easily determine this, so if you have duplicated column names in table1 and
table2 you will not know which is which without asking for the source table and
column name metadata for each column to find out what column and table it came
from).
This is a general issue with all SQL and is why many texts will tell you to not
duplicate column names in different tables unless they contain the same data
-- leading to silly standards like "columns should always be named as
table_column" (though, back in the 1950's or so database systems used to
require that each column no matter what the source had a unique column_name
(actually, item_name), but this is no longer the case and has not been for
quite a while).
To avoid this issue entirely (and the embed the table name in the column name
sillyness) you should almost never use a * in the select list ... unless you
want to handle the various vagaries that will arise on the client side -- plus
of course it is better to only ask for what you need rather than culling the
returned haystack for the needle you want after the fact -- the SQL Query
Planner may devise quite different ways of executing your query with quite
different performance based on data you have asked to be returned (for example,
you might have "extra" tables that are not required for either returned data or
join constraints and will be removed entirely, or join constraints that can be
satisfied entirely by an index without going back to the source table -- but if
you ask for * then this optimization cannot be performed because the underlying
table may need to be accessed to create the haystack of data you are requesting.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[email protected]] On Behalf Of Maziar Parsijani
>Sent: Friday, 14 September, 2018 12:32
>To: [email protected]
>Subject: Re: [sqlite] [SQLITE]select from a table and use its data to
>select from another one
>
>Hi,
>Thanks for your answer.I used your answer like this :
> SELECT * FROM table2
> JOIN table1 on table1.rowid = table2.rowid
> WHERE table1.name LIKE '%smth%'
>Because without the "table1 on" statement it didn't work .
>
>On Fri, Sep 14, 2018 at 10:29 PM Simon Slavin <[email protected]>
>wrote:
>
>> On 14 Sep 2018, at 6:50pm, Maziar Parsijani
><[email protected]>
>> wrote:
>>
>> > I have 2 tables with the same rowid now I want to :
>> > select rowid from table1 where table1 like "%smth%"
>> > select * from table2 where rowid =(selected rows before)
>> >
>> > I mean if I could do it in a same query.
>>
>> This is what JOIN is for.
>>
>> SELECT * FROM table2
>> JOIN table1.rowid = table2.rowid
>> WHERE table1.name LIKE '%smth%'
>>
>> Note that SQLite uses single quotes ' for text strings, not double
>quotes
>> ".
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
