Re: [sqlite] Slim down join results (all fields returned)

[flakpit]

On 18/04/2009 2:33 PM, flakpit wrote:
> If anyone else has a moment and a concrete example instead of sending me
> to
> tutorials, feel free to step in here.

>If you would take a moment to read the fraction of a screen of a 
>tutorial that I pointed you at (and which contains a concrete example), 
>instead of repeating what you have already explained you want and is 
>screamingly obvious anyway, you might actually *learn* something, and be 
>able to find answers for yourself quickly.

Do me a favour and stick your condescending arrogance where the light
doesn't shine. As I get older and slower, I have trouble working things out
for myself which is why I asked for help here and I don't need to be told to
go read tutorials as I have repeatedly told you that I don't necessarily
understand but it seems you have trouble reading English as well as basic
courtesy and manners.

Since you have trouble using the few brain cells you posess for basic
courtesy and understanding that not all of us older folks can work things
out as fast as you do and it isn't "screamingly obvious anyway" to some of
us, don't feel that you have to demean yourself any more by helping.

I certainly don't need your help with that blinding arrogance.
-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23110073.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[flakpit]

> You did. You should still do some SQL tutorials.
>In the meantime, I hope the above helps.

Adjusted the fieldnames accordingly and your example worked perfectly. Thank
you.

However, repeating that I should do some SQL tutorials is annoying in
extremis. I had been to many SQL tutorials and always failed to understand
more complex examples until I had a concrete, real world example to work
from. 

That is why I came here for help as the examples you people provide are far
better a base for me to comprehend than the relativity of a tutorial.

I remember several examples from other people here that helped me get along
far better than a 'relative' tutorial.

Thanks for the help, the query below with slightly adjusted field names
works perfectly.

Now I am going to go and get tortured by my wife, beaten up by my daughter
and whinged at by the cat. Looks like no more struggling till nightfall!

SELECT pubs.*,
  notes.note_note,
  publishers.publisher_name
FROM pubs JOIN notes ON pubs.note_id = notes.note_id
  JOIN publishers ON pubs.publisher_id = publishers.publisher_id
WHERE pubs.pub_title LIKE '%salem%' 


-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23110028.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[John Machin]

On 18/04/2009 2:33 PM, flakpit wrote:
> If anyone else has a moment and a concrete example instead of sending me to
> tutorials, feel free to step in here.

If you would take a moment to read the fraction of a screen of a 
tutorial that I pointed you at (and which contains a concrete example), 
instead of repeating what you have already explained you want and is 
screamingly obvious anyway, you might actually *learn* something, and be 
able to find answers for yourself quickly.

> SELECT * FROM pubs; I want ALL columns from the 'pubs'
> table
> 
> WHERE pub_title LIKE '%salem%'  ; Where the title sounds like 'salem'

*sounds* like? The LIKE operator does simple pattern matching ... LIKE 
'%salem%' will match a string containing 'salem' (or 'Salem' or 'SaLem' 
or ...) anywhere, e.g. 'History of Salem, Mass.' or 'Jerusalem'. If you 
want any sort of phonetic matching, you'd have to write a user-defined 
function and use the MATCH operator instead of LIKE.

> The query below retrieves the correct note and publisher for each retrieved
> record in 'pubs' that sounds like 'salem' in the 'pub_title' but also every
> other column in 'notes' and 'publishers' which I don't want.

> I just want the matching notes.note_note for each pubs record and not
> notes.id or notes.idx etc.
> I just want the matching publishers.publisher_name for each pubs record and
> not publishers.id or publishers.idx etc.

> SELECT * FROM pubs

What do you want from the pubs table? All columns
What do you want from the publishers table? The publisher_name column
What do you want from the notes table? the note_note column [are you 
sure? It was note_notes in your previous message]

So just tell it what you want:

SELECT pubs.*, publishers.publisher_name, notes.note_note FROM ...

> INNER JOIN notes
> ON pubs.note_id=notes.note_id
> INNER JOIN publishers
> ON pubs.publisher_id=publishers.publisher_id
> WHERE pub_title LIKE '%salem%'


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[P Kishor]

On Fri, Apr 17, 2009 at 11:33 PM, flakpit  wrote:
>
> If anyone else has a moment and a concrete example instead of sending me to
> tutorials, feel free to step in here.
>
> SELECT * FROM pubs                    ; I want ALL columns from the 'pubs'
> table
>
> WHERE pub_title LIKE '%salem%'  ; Where the title sounds like 'salem'
>
> In the pubs table, there is an ID field that points to the note in the
> 'notes' table. I want to retrieve that note for the currently retrieved
> record from the 'pubs' table and no other column in the 'notes table'
>
> In the pubs table, there is an ID field that points to the publisher in the
> 'publisher' table. I want to retrieve that publisher for the currently
> retrieved record from the 'publishers' table and no other column in the
> 'publishers' table
>
> The query below retrieves the correct note and publisher for each retrieved
> record in 'pubs' that sounds like 'salem' in the 'pub_title' but also every
> other column in 'notes' and 'publishers' which I don't want.
>
> I just want the matching notes.note_note for each pubs record and not
> notes.id or notes.idx etc.
> I just want the matching publishers.publisher_name for each pubs record and
> not publishers.id or publishers.idx etc.
>
> SELECT * FROM pubs
> INNER JOIN notes
> ON pubs.note_id=notes.note_id
> INNER JOIN publishers
> ON pubs.publisher_id=publishers.publisher_id
> WHERE pub_title LIKE '%salem%'
>

SELECT pubs.*,
  notes.note,
  publishers.publisher
FROM pubs JOIN notes ON pubs.note_id = notes.note_id
  JOIN publishers ON pubs.publisher_id = publishers.publisher_id
WHERE pubs.pub_title LIKE '%salem%'


> I believe I explained myself well enough this time.

You did. You should still do some SQL tutorials.

In the meantime, I hope the above helps.


> --
> View this message in context: 
> http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23109313.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[flakpit]

If anyone else has a moment and a concrete example instead of sending me to
tutorials, feel free to step in here.

SELECT * FROM pubs; I want ALL columns from the 'pubs'
table

WHERE pub_title LIKE '%salem%'  ; Where the title sounds like 'salem'

In the pubs table, there is an ID field that points to the note in the
'notes' table. I want to retrieve that note for the currently retrieved
record from the 'pubs' table and no other column in the 'notes table'

In the pubs table, there is an ID field that points to the publisher in the
'publisher' table. I want to retrieve that publisher for the currently
retrieved record from the 'publishers' table and no other column in the
'publishers' table

The query below retrieves the correct note and publisher for each retrieved
record in 'pubs' that sounds like 'salem' in the 'pub_title' but also every
other column in 'notes' and 'publishers' which I don't want.

I just want the matching notes.note_note for each pubs record and not
notes.id or notes.idx etc.
I just want the matching publishers.publisher_name for each pubs record and
not publishers.id or publishers.idx etc.

SELECT * FROM pubs
INNER JOIN notes
ON pubs.note_id=notes.note_id
INNER JOIN publishers
ON pubs.publisher_id=publishers.publisher_id
WHERE pub_title LIKE '%salem%'

I believe I explained myself well enough this time.
-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23109313.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[John Machin]

On 18/04/2009 8:24 AM, flakpit wrote:
>> SELECT pubs.* from pubs, notes, publishers WHERE ...
> 
> Thanks, but it didn't work the way I expected it to:)
> 
> Now I have my query laid out  a lot better and can 'slim' it down from here
> 
> SELECT * FROM pubs
> INNER JOIN notes
> ON pubs.note_id=notes.note_id  ; notes.

I presume the "; notes." on the end of the above line is not meant to be 
there.

> INNER JOIN publishers
> ON pubs.publisher_id=publishers.publisher_id
> WHERE pub_title LIKE '%salem%'
> 
> How do I return only the matching notes.note_notes field in that join and no
> the rest of the fields in notes?
> 
> How do I return only the matching publishers.publisher_name field in that
> join and not the rest of the fields in publishers?
> 
> Probably still explaining myself very badly here. Oh well.

Either that or you need to work through an elementary SQL tutorial ... 
e.g. see http://www.w3schools.com/Sql/sql_join_inner.asp

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[flakpit]

>SELECT pubs.* from pubs, notes, publishers WHERE ...

Thanks, but it didn't work the way I expected it to:)

Now I have my query laid out  a lot better and can 'slim' it down from here

SELECT * FROM pubs
INNER JOIN notes
ON pubs.note_id=notes.note_id  ; notes.
INNER JOIN publishers
ON pubs.publisher_id=publishers.publisher_id
WHERE pub_title LIKE '%salem%'

How do I return only the matching notes.note_notes field in that join and no
the rest of the fields in notes?

How do I return only the matching publishers.publisher_name field in that
join and not the rest of the fields in publishers?

Probably still explaining myself very badly here. Oh well.
-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23107065.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[cmartin]

On Fri, 17 Apr 2009, flakpit wrote:

> Currently, I return any needed data like this.
>
>  select * from pubs,notes,publishers where pub_title like '%salem%'
>  and pubs.note_id=notes.note_id
>  and pubs.publisher_id=publishers.publisher_id
>
> And it works except for all fields in the matching tables being returned.

'*' means 'return all fields'. This has nothing to do with the joins, even 
if you are selecting from a single table '*' means return all fields:

select * from tableA

returns all columns from tableA.

If you only want some fields, just name them in the SELECT:

select pubs.note_id, pubs.publisher_id from ...

Chris

> Is there any way using joins (been playing but can't get them working) so
> that a statement like
>
> pubs.note_id=notes.note_id
>
> Would return only the contents of the notes.note_note field and not all the
> fields in the notes table?
> -- 
> View this message in context: 
> http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23098746.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[Eric Minbiole]

> Currently, I return any needed data like this.
> 
>   select * from pubs,notes,publishers where pub_title like '%salem%' 
>   and pubs.note_id=notes.note_id
>   and pubs.publisher_id=publishers.publisher_id
> 
> And it works except for all fields in the matching tables being returned.
> 
> Is there any way using joins (been playing but can't get them working) so
> that a statement like 
> 
> pubs.note_id=notes.note_id
> 
> Would return only the contents of the notes.note_note field and not all the
> fields in the notes table?

Is this what you are looking for:

SELECT pubs.* from pubs, notes, publishers WHERE ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slim down join results (all fields returned)

[flakpit]

Currently, I return any needed data like this.

  select * from pubs,notes,publishers where pub_title like '%salem%' 
  and pubs.note_id=notes.note_id
  and pubs.publisher_id=publishers.publisher_id

And it works except for all fields in the matching tables being returned.

Is there any way using joins (been playing but can't get them working) so
that a statement like 

pubs.note_id=notes.note_id

Would return only the contents of the notes.note_note field and not all the
fields in the notes table?
-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23098746.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users