Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Question Unanswered: Using Between Condition With Multiple Tables?

    Is it possible to use the between command to return data from multple tables? I have tables named :

    England_Summer
    England_Winter
    France_Summer
    France_Winter

    Code:
    SELECT*
    FROM England_Summer
    WHERE Fee BETWEEN '0.00' AND '20.00';
    Returns values from the England_Summer table but how do I return the values from all the tables in the database?

    Code:
    SELECT*
    FROM England_Summer, England_Winter
    WHERE Fee BETWEEN '0.00' AND '20.00';
    Gives this error :

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'England_Summer'.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Palermo View Post
    ... how do I return the values from all the tables in the database?
    by not having separate tables, just have one

    then the SQL is simple, right?

    you will need to add two columns: country and season

    there is no need for separate tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    36
    There are 10 tables in total, covering five countries and ultimately there will be 10 years of data so it is necessary to have separate tables!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Palermo View Post
    ... so it is necessary to have separate tables!
    no, it isn't -- not based on those reasons

    seriously, you should consider using just one table

    you will thank me later

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    Code:
    go
    create table England_Summer (country varchar(20), fee int)
    go
    create table England_Winter (country varchar(20), fee int)
    go
    create table France_Summer (country varchar(20), fee int)
    go
    create table France_Winter (country varchar(20), fee int)
    go
    
    insert into England_Summer values ( 'England1',10)
    go
    insert into England_Summer values ( 'England2',12)
    go
    insert into England_Summer values ( 'England3',14)
    go
    insert into England_Summer values ( 'England4',20)
    go
    insert into England_Summer values ( 'England5',30)
    go
    go
    insert into England_Winter values ( 'England1',10)
    go
    insert into England_Winter values ( 'England2',12)
    go
    insert into England_Winter values ( 'England3',14)
    go
    insert into England_Winter values ( 'England4',20)
    go
    insert into England_Winter values ( 'England5',30)
    go
    go
    insert into France_Summer values ( 'Frqance1',10)
    go
    insert into France_Summer values ( 'Frqance2',12)
    go
    insert into France_Summer values ( 'Frqance3',14)
    go
    insert into France_Summer values ( 'Frqance4',20)
    go
    insert into France_Summer values ( 'Frqance5',30)
    go
    go
    insert into France_Winter values ( 'Frqance1',10)
    go
    insert into France_Winter values ( 'Frqance2',12)
    go
    insert into France_Winter values ( 'Frqance3',14)
    go
    insert into France_Winter values ( 'Frqance4',20)
    go
    insert into France_Winter values ( 'Frqance5',30)
    go

    Code:
    select country,fee 
    from england_summer
    where fee between 10 and 20
    union all
    select country,fee 
    from france_summer
    where fee between 10 and 20
    country fee
    -------------------- -----------
    England1 10
    England2 12
    England3 14
    England4 20
    Frqance1 10
    Frqance2 12
    Frqance3 14
    Frqance4 20

    (8 row(s) affected)

    Code:
    update england_summer 
    set fee= fee+6
    6 Rows Updated

    Code:
    select * from england_summer
    country fee
    -------------------- -----------
    England1 16
    England2 18
    England3 20
    England4 26
    England5 36

    (5 row(s) affected)

    Code:
    select country,fee 
    from england_summer
    where fee between 10 and 20
    union all
    select country,fee 
    from france_summer
    where fee between 10 and 20
    country fee
    -------------------- -----------
    England1 16
    England2 18
    England3 20
    Frqance1 10
    Frqance2 12
    Frqance3 14
    Frqance4 20

    (7 row(s) affected)

    Code:
    update france_summer 
    set fee= fee+9
    Code:
    select country,fee 
    from england_summer
    where fee between 10 and 20
    union all
    select country,fee 
    from england_winter
    where fee between 10 and 20
    union all
    select country,fee 
    from france_summer
    where fee between 10 and 20
    union all
    select country,fee 
    from france_winter
    where fee between 10 and 20
    country fee
    -------------------- -----------
    England1 16
    England2 18
    England3 20
    England1 10
    England2 12
    England3 14
    England4 20
    Frqance1 19
    Frqance1 10
    Frqance2 12
    Frqance3 14
    Frqance4 20


    NOTE : I am passing my time ... thus playing with sql
    Last edited by mishaalsy; 12-30-10 at 09:30.

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    what .. i am bored

  7. #7
    Join Date
    Dec 2010
    Posts
    36

    sucaphy for

    Thanks mishaalsy though most of the code wasn't necessary as I have the the tables and data.

    I'll explain what I'm doing so it makes sense. The database is a list of transfer fees paid by soccer clubs for players in five different championships i.e. England, France, Germany, Italy and Spain. The original data is in Excel format so I imported it into access than saved it in mdb format before importing into SQL as Importing from Excel into SQL results in non numerical data being replaced with null values.

    I played around with your union code :

    Code:
    SELECT*
    FROM England_Summer
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM England_Winter
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM France_Summer
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM France_Winter
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM Germany_Summer
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM Germany_Winter
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM Italy_Summer
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM Italy_Winter
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM Spain_Summer
    WHERE Fee BETWEEN '0.00' AND '80.00'
    UNION ALL
    SELECT*
    FROM Spain_Winter
    WHERE Fee BETWEEN '0.00' AND '80.00'
    Order by Fee;
    Which displays all the values between 0.00 million and 80.00 grouped by each table in ascending order. I'm guessing if I want to show ascending fees from all tables I will have to merge the tables into a single table as r937 suggested?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Palermo View Post
    Which displays all the values between €0.00 million and €80.00 grouped by each table in ascending order.
    um, no it doesn't

    your ORDER BY clause ensures that all rows from all tables are sorted by fee

    however, a single table is still the best way forward for you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2010
    Posts
    36
    http://img.villagephotos.com/p/2010-...der_by_Fee.jpg

    You'll notice the fees are displayed in ascending order across all tables before suddenly dropping from 19.00 to 2.00.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Palermo View Post
    ... before suddenly dropping from 19.00 to 2.00.
    it's not sudden, it is inherent in the collating sequence of strings

    you're sorting strings, not numbers

    '19.00' comes before '2.00' when you sort them character by character from the left position

    if you want numeric sorting, you'll have to convert your fees column to a numeric datatype

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and i still say you should have just one table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by r937 View Post
    it's not sudden, it is inherent in the collating sequence of strings

    you're sorting strings, not numbers

    '19.00' comes before '2.00' when you sort them character by character from the left position

    if you want numeric sorting, you'll have to convert your fees column to a numeric datatype

    As that field also contains non numerical data (loan moves, undisclosed fees) that's not an option and converting to a single table isn't going to resolve that problem! Although if I could save the results of Where Between query as a new table I could then change the data to a numerical datatype.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Palermo View Post
    As that field also contains non numerical data (loan moves, undisclosed fees) ...
    i think perhaps you might want to consider redesigning this so that numeric data is kept in numeric columns

    not to mention keeping different data in different columns

    and while you're at it, combine your dozen tables into one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2010
    Posts
    36
    As the data in Excel that's easy to do though I'd rather find a solution using SQL code. I have saved the results of the Where Between query as a CSV file, imported into SQL and tried to convert the fees column to a numerical data type but get this error :

    Error converting data type varchar to numeric.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Palermo View Post
    Error converting data type varchar to numeric.
    some of the values are not numeric

    you'll have to expend a bit more energy to separate the numeric from the non-numeric

    perhaps two columns instead of one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •