[SQLITE]select from a table and use its data to select from another one

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[SQLITE]select from a table and use its data to select from another one

Maziar Parsijani
Hi,
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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLITE]select from a table and use its data to select from another one

Simon Slavin-3
On 14 Sep 2018, at 6:50pm, Maziar Parsijani <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLITE]select from a table and use its data to select from another one

David Raymond
Small typo:

SELECT * FROM table2 JOIN table1
    ON table1.rowid = table2.rowid
    WHERE table1.name LIKE '%smth%'


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Friday, September 14, 2018 1:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

On 14 Sep 2018, at 6:50pm, Maziar Parsijani <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLITE]select from a table and use its data to select from another one

CedricCicada
The use of single quotes instead of double quotes in database queries is
not limited to SQLite.  That's part of the SQL standard.

RobR

On Fri, Sep 14, 2018 at 2:05 PM David Raymond <[hidden email]>
wrote:

> Small typo:
>
> SELECT * FROM table2 JOIN table1
>     ON table1.rowid = table2.rowid
>     WHERE table1.name LIKE '%smth%'
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Simon Slavin
> Sent: Friday, September 14, 2018 1:59 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] [SQLITE]select from a table and use its data to
> select from another one
>
> On 14 Sep 2018, at 6:50pm, Maziar Parsijani <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLITE]select from a table and use its data to select from another one

CedricCicada
Double quotes can be used to specify that you mean a database object when
the name of the object might be confused with a keyword.  For example, my
company's database models a production system with various recipes.  We
call them "cycles".  But the word "cycle" appears to have some specific
meaning inside SQL (or at least, inside PostgreSQL).  So, although the
query
   SELECT * FROM cycle
works, we should probably use
    SELECT * FROM "cycle"
to avoid any possible ambiguity.

RobR

On Fri, Sep 14, 2018 at 2:12 PM Rob Richardson <[hidden email]>
wrote:

> The use of single quotes instead of double quotes in database queries is
> not limited to SQLite.  That's part of the SQL standard.
>
> RobR
>
> On Fri, Sep 14, 2018 at 2:05 PM David Raymond <[hidden email]>
> wrote:
>
>> Small typo:
>>
>> SELECT * FROM table2 JOIN table1
>>     ON table1.rowid = table2.rowid
>>     WHERE table1.name LIKE '%smth%'
>>
>>
>> -----Original Message-----
>> From: sqlite-users [mailto:[hidden email]]
>> On Behalf Of Simon Slavin
>> Sent: Friday, September 14, 2018 1:59 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] [SQLITE]select from a table and use its data to
>> select from another one
>>
>> On 14 Sep 2018, at 6:50pm, Maziar Parsijani <[hidden email]>
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLITE]select from a table and use its data to select from another one

Maziar Parsijani
In reply to this post by Simon Slavin-3
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 <[hidden email]> wrote:

> On 14 Sep 2018, at 6:50pm, Maziar Parsijani <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLITE]select from a table and use its data to select from another one

Keith Medcalf

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-
>[hidden email]] On Behalf Of Maziar Parsijani
>Sent: Friday, 14 September, 2018 12:32
>To: [hidden email]
>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 <[hidden email]>
>wrote:
>
>> On 14 Sep 2018, at 6:50pm, Maziar Parsijani
><[hidden email]>
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLITE]select from a table and use its data to select from another one

Maziar Parsijani
In reply to this post by CedricCicada
Hi,
Really thanks to all with your complete answers.
The best answer is from Keith Medcalf.

On Sat, Sep 15, 2018 at 1:45 AM Rob Richardson <[hidden email]>
wrote:

> Double quotes can be used to specify that you mean a database object when
> the name of the object might be confused with a keyword.  For example, my
> company's database models a production system with various recipes.  We
> call them "cycles".  But the word "cycle" appears to have some specific
> meaning inside SQL (or at least, inside PostgreSQL).  So, although the
> query
>    SELECT * FROM cycle
> works, we should probably use
>     SELECT * FROM "cycle"
> to avoid any possible ambiguity.
>
> RobR
>
> On Fri, Sep 14, 2018 at 2:12 PM Rob Richardson <[hidden email]>
> wrote:
>
> > The use of single quotes instead of double quotes in database queries is
> > not limited to SQLite.  That's part of the SQL standard.
> >
> > RobR
> >
> > On Fri, Sep 14, 2018 at 2:05 PM David Raymond <[hidden email]>
> > wrote:
> >
> >> Small typo:
> >>
> >> SELECT * FROM table2 JOIN table1
> >>     ON table1.rowid = table2.rowid
> >>     WHERE table1.name LIKE '%smth%'
> >>
> >>
> >> -----Original Message-----
> >> From: sqlite-users [mailto:[hidden email]
> ]
> >> On Behalf Of Simon Slavin
> >> Sent: Friday, September 14, 2018 1:59 PM
> >> To: SQLite mailing list
> >> Subject: Re: [sqlite] [SQLITE]select from a table and use its data to
> >> select from another one
> >>
> >> On 14 Sep 2018, at 6:50pm, Maziar Parsijani <[hidden email]
> >
> >> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users