Professional Web Applications Themes

Select quantity from 2 tables - Microsoft SQL / MS SQL Server

I have 2 tables: table1: country1 quantity1 idProduct1 table2: country2 quantity2 idProduct2 Now I have to get the quantity:(quantity1-quantity2) for each productId for each country. (where idProduct1=idProduct2 AND country1=country2) If quantity1 doesn't exist then quantity=0-quantity2 (sometimes there is only record in table2) or if quantity2 doesn't exist then quantity=quantity1 (sometimes there is only record in table1) Does anybody have the answer? EXAMPLE: table1: country1 quantity1 idProduct1 1 5 1 2 4 2 table2: country2 quantity2 idProduct2 1 2 1 3 1 3 The result should be: country quantity productId 1 3 1 2 4 2 3 -1 3 SCRIPT: CREATE ...

  1. #1

    Default Select quantity from 2 tables

    I have 2 tables:

    table1:
    country1 quantity1 idProduct1

    table2:
    country2 quantity2 idProduct2

    Now I have to get the quantity:(quantity1-quantity2)
    for each productId for each country.
    (where idProduct1=idProduct2 AND country1=country2)

    If quantity1 doesn't exist then quantity=0-quantity2
    (sometimes there is only record in table2)
    or if quantity2 doesn't exist then quantity=quantity1
    (sometimes there is only record in table1)

    Does anybody have the answer?

    EXAMPLE:

    table1:
    country1 quantity1 idProduct1
    1 5 1
    2 4 2

    table2:
    country2 quantity2 idProduct2
    1 2 1
    3 1 3

    The result should be:

    country quantity productId
    1 3 1
    2 4 2
    3 -1 3

    SCRIPT:

    CREATE TABLE [dbo].[Table1] (
    [country1] [int] NOT NULL ,
    [quantity1] [int] NULL ,
    [idProduct1] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Table2] (
    [country2] [int] NOT NULL ,
    [quantity2] [int] NULL ,
    [idProduct2] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    (
    [country1],
    [idProduct1]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    (
    [country2],
    [idProduct2]
    ) ON [PRIMARY]
    GO
    INSERT Table1 VALUES(1,5,1)
    INSERT Table1 VALUES(2,4,2)
    INSERT Table2 VALUES(1,2,1)
    INSERT Table2 VALUES(3,1,3)

    Thank you,
    Simon


    Simon Guest

  2. #2

    Default Re: Select quantity from 2 tables

    Try this,

    select t1.country1, (t1.quantity1-isnull(t2.quantity2,0)) quantity,
    t1.idproduct1 from table1 t1 left join table2 t2 on t2.country2=t1.country1
    and t2.idproduct2=t1.idproduct1
    union
    select t2.country2, (isnull(t1.quantity1,0)-isnull(t2.quantity2,0))
    quantity, t2.idproduct2 from table1 t1 right join table2 t2 on
    t2.country2=t1.country1 and t2.idproduct2=t1.idproduct1

    OR

    select t1.country1, (t1.quantity1-isnull(t2.quantity2,0)) quantity,
    t1.idproduct1 from table1 t1 left join table2 t2 on t2.country2=t1.country1
    and t2.idproduct2=t1.idproduct1
    union all
    select t2.country2, (isnull(t1.quantity1,0)-isnull(t2.quantity2,0))
    quantity, t2.idproduct2 from table1 t1 right join table2 t2 on
    t2.country2=t1.country1 and t2.idproduct2=t1.idproduct1
    where t1.country1 is null

    HTH
    Falik


    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f0a66cd$1news.s5.net...
    > I have 2 tables:
    >
    > table1:
    > country1 quantity1 idProduct1
    >
    > table2:
    > country2 quantity2 idProduct2
    >
    > Now I have to get the quantity:(quantity1-quantity2)
    > for each productId for each country.
    > (where idProduct1=idProduct2 AND country1=country2)
    >
    > If quantity1 doesn't exist then quantity=0-quantity2
    > (sometimes there is only record in table2)
    > or if quantity2 doesn't exist then quantity=quantity1
    > (sometimes there is only record in table1)
    >
    > Does anybody have the answer?
    >
    > EXAMPLE:
    >
    > table1:
    > country1 quantity1 idProduct1
    > 1 5 1
    > 2 4 2
    >
    > table2:
    > country2 quantity2 idProduct2
    > 1 2 1
    > 3 1 3
    >
    > The result should be:
    >
    > country quantity productId
    > 1 3 1
    > 2 4 2
    > 3 -1 3
    >
    > SCRIPT:
    >
    > CREATE TABLE [dbo].[Table1] (
    > [country1] [int] NOT NULL ,
    > [quantity1] [int] NULL ,
    > [idProduct1] [int] NOT NULL
    > ) ON [PRIMARY]
    > GO
    >
    > CREATE TABLE [dbo].[Table2] (
    > [country2] [int] NOT NULL ,
    > [quantity2] [int] NULL ,
    > [idProduct2] [int] NOT NULL
    > ) ON [PRIMARY]
    > GO
    >
    > ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
    > CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    > (
    > [country1],
    > [idProduct1]
    > ) ON [PRIMARY]
    > GO
    >
    > ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
    > CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    > (
    > [country2],
    > [idProduct2]
    > ) ON [PRIMARY]
    > GO
    > INSERT Table1 VALUES(1,5,1)
    > INSERT Table1 VALUES(2,4,2)
    > INSERT Table2 VALUES(1,2,1)
    > INSERT Table2 VALUES(3,1,3)
    >
    > Thank you,
    > Simon
    >
    >

    Falik Sher Guest

  3. #3

    Default Re: Select quantity from 2 tables

    SELECT COALESCE(country1, country2) AS country,
    COALESCE(quantity1,0)-COALESCE(quantity2,0) AS quantity,
    COALESCE(idproduct1, idproduct2) AS productid
    FROM Table1 AS T1
    FULL JOIN Table2 AS T2
    ON T1.country1=T2.country2 AND T1.idproduct1=T2.idproduct2

    Thanks for posting the DDL and sample data.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default Re: Select quantity from 2 tables

    FULL JOIN? :-)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  5. #5

    Default Re: Select quantity from 2 tables

    Thank you very much for your answers,

    Simon

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:O8SLM3RRDHA.2852tk2msftngp13.phx.gbl...
    > SELECT COALESCE(country1, country2) AS country,
    > COALESCE(quantity1,0)-COALESCE(quantity2,0) AS quantity,
    > COALESCE(idproduct1, idproduct2) AS productid
    > FROM Table1 AS T1
    > FULL JOIN Table2 AS T2
    > ON T1.country1=T2.country2 AND T1.idproduct1=T2.idproduct2
    >
    > Thanks for posting the DDL and sample data.
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Simon Guest

  6. #6

    Default Re: Select quantity from 2 tables

    Hello, David,

    your soluton is ok, but you can have more then one record of the same
    product for the same country, so I have to include somehow:
    COALESCE(sum(quantity1),0)-COALESCE(sum(quantity2),0) AS quantity

    and therefore also group by country and productId - but there you can't use
    group by country, only country1 or country2 - how to solve that?

    I also have date1 and date2 column and I would like to include the
    condition:

    WHERE COALESCE(date1,date2) AS date='20030629' or something like that.

    Do you now the solution?

    Thank you
    Simon


    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:O8SLM3RRDHA.2852tk2msftngp13.phx.gbl...
    > SELECT COALESCE(country1, country2) AS country,
    > COALESCE(quantity1,0)-COALESCE(quantity2,0) AS quantity,
    > COALESCE(idproduct1, idproduct2) AS productid
    > FROM Table1 AS T1
    > FULL JOIN Table2 AS T2
    > ON T1.country1=T2.country2 AND T1.idproduct1=T2.idproduct2
    >
    > Thanks for posting the DDL and sample data.
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Simon Guest

  7. #7

    Default Re: Select quantity from 2 tables

    > your soluton is ok, but you can have more then one record of the same
    > product for the same country
    In the DDL you posted (country,product) was the primary key so it isn't
    possible to have the same product more than once for the same country. If
    (country,product) is not unique then putting a SUM into the query I posted
    will NOT give the correct answer because rows will be doubled-up.

    Without knowing the PK I can't give you a full answer. Please post the
    correct DDL with the primary key.

    > WHERE COALESCE(date1,date2) AS date='20030629' or something like that.
    >
    Use:

    WHERE date1='20030629' OR date2='20030629'

    COALESCE won't help in this instance because presumably the joined rows
    could have different dates.


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  8. #8

    Default Re: Select quantity from 2 tables

    sorry, the fact is that I have table with more than 30 columns and I try to
    extract some for a propriate example.
    So, the new script is:

    CREATE TABLE [dbo].[Table1] (
    [country1] [int] NOT NULL ,
    [quantity1] [int] NULL ,
    [idIzdelka1] [int] NOT NULL ,
    [datum1] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
    CONSTRAINT [DF_Table1_datum1] DEFAULT (getdate()) FOR [datum1],
    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    (
    [country1],
    [idIzdelka1],
    [datum1]
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Table2] (
    [country2] [int] NOT NULL ,
    [quantity2] [int] NULL ,
    [idIzdelka2] [int] NOT NULL ,
    [datum2] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
    CONSTRAINT [DF_Table2_datum2] DEFAULT (getdate()) FOR [datum2],
    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    (
    [country2],
    [idIzdelka2],
    [datum2]
    ) ON [PRIMARY]
    GO

    INSERT Table1 VALUES(1,5,1,getdate())
    go
    INSERT Table1 VALUES(2,4,2,getdate())
    go
    INSERT Table1 VALUES(2,3,2,getdate())
    go
    INSERT Table1 VALUES(4,3,4,getdate())
    go
    INSERT Table1 VALUES(4,4,4,getdate())

    INSERT Table1 VALUES(1,2,1'20030705')
    INSERT Table1 VALUES(2,1,2,'20030704')

    INSERT Table2 VALUES(1,2,1,getdate())
    go
    INSERT Table2 VALUES(3,1,3,getdate())
    go
    INSERT Table2 VALUES(1,1,1,getdate())
    go
    INSERT Table2 VALUES(3,5,3,getdate())
    go
    INSERT Table2 VALUES(4,2,4,getdate())
    go
    INSERT Table2 VALUES(4,1,4,getdate())
    INSERT Table2 VALUES(3,2,3'20030705')
    INSERT Table2 VALUES(4,1,4,'20030704')

    The result I need is:

    country quantity productId
    1 2 1
    2 7 2
    3 -6 3
    4 4 4

    AND I need this results for one day, for example where date is 08.07.2003
    (when you insert this script the date will be today by default).
    So, the last 2 rows from table1 AND table2 should't affect the result If you
    include the condition: date=08.07.2003.
    By the way, where you include the ISO (YYYYMMDD) format?
    When you create the database?

    Thank you for everything,
    Simon

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:eEr23OTRDHA.2228tk2msftngp13.phx.gbl...
    > > your soluton is ok, but you can have more then one record of the same
    > > product for the same country
    >
    > In the DDL you posted (country,product) was the primary key so it isn't
    > possible to have the same product more than once for the same country. If
    > (country,product) is not unique then putting a SUM into the query I posted
    > will NOT give the correct answer because rows will be doubled-up.
    >
    > Without knowing the PK I can't give you a full answer. Please post the
    > correct DDL with the primary key.
    >
    >
    > > WHERE COALESCE(date1,date2) AS date='20030629' or something like that.
    > >
    > Use:
    >
    > WHERE date1='20030629' OR date2='20030629'
    >
    > COALESCE won't help in this instance because presumably the joined rows
    > could have different dates.
    >
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Simon Guest

  9. #9

    Default Re: Select quantity from 2 tables

    DECLARE dt DATETIME
    SET dt = '20030708'

    SELECT COALESCE(country1, country2) AS country,
    COALESCE(quantity1,0)-COALESCE(quantity2,0) AS idizdelka,
    COALESCE(idizdelka1, idizdelka2) AS productid
    FROM
    (SELECT country1, idizdelka1, SUM(quantity1) AS quantity1
    FROM Table1
    WHERE datum1 >= dt AND datum1 < DATEADD(DAY,1,dt)
    GROUP BY country1, idizdelka1) AS T1
    FULL JOIN
    (SELECT country2, idizdelka2, SUM(quantity2) AS quantity2
    FROM Table2
    WHERE datum2 >= dt AND datum2 < DATEADD(DAY,1,dt)
    GROUP BY country2, idizdelka2) AS T2
    ON T1.country1 = T2.country2 AND T1.idizdelka1 = T2.idizdelka2
    > By the way, where you include the ISO (YYYYMMDD) format?
    > When you create the database?
    You don't have to change any settings. The format:

    '20030708'

    or:

    '2003-07-08T00:00:00.000'

    is valid no matter what your regional or DATEFORMAT settings. That's the
    advantage of these formats over the local formats such as 'dd-mm-yyyy' or
    'mm-dd-yyyy' which only work under particular regional settings.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  10. #10

    Default Re: Select quantity from 2 tables

    Hi,

    thank you for your example, it works now.

    Is it possible to have instead of SET dt = '20030707'
    SET dt=getdate(), I need always for the current day?

    Does it use index on the date column in this example?

    Best regards from Slovenia,
    Simon


    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:edEOGzTRDHA.2676TK2MSFTNGP10.phx.gbl...
    > DECLARE dt DATETIME
    > SET dt = '20030708'
    >
    > SELECT COALESCE(country1, country2) AS country,
    > COALESCE(quantity1,0)-COALESCE(quantity2,0) AS idizdelka,
    > COALESCE(idizdelka1, idizdelka2) AS productid
    > FROM
    > (SELECT country1, idizdelka1, SUM(quantity1) AS quantity1
    > FROM Table1
    > WHERE datum1 >= dt AND datum1 < DATEADD(DAY,1,dt)
    > GROUP BY country1, idizdelka1) AS T1
    > FULL JOIN
    > (SELECT country2, idizdelka2, SUM(quantity2) AS quantity2
    > FROM Table2
    > WHERE datum2 >= dt AND datum2 < DATEADD(DAY,1,dt)
    > GROUP BY country2, idizdelka2) AS T2
    > ON T1.country1 = T2.country2 AND T1.idizdelka1 = T2.idizdelka2
    >
    > > By the way, where you include the ISO (YYYYMMDD) format?
    > > When you create the database?
    >
    > You don't have to change any settings. The format:
    >
    > '20030708'
    >
    > or:
    >
    > '2003-07-08T00:00:00.000'
    >
    > is valid no matter what your regional or DATEFORMAT settings. That's the
    > advantage of these formats over the local formats such as 'dd-mm-yyyy' or
    > 'mm-dd-yyyy' which only work under particular regional settings.
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Simon Guest

  11. #11

    Default Re: Select quantity from 2 tables

    > Is it possible to have instead of SET dt = '20030707'
    > SET dt=getdate(), I need always for the current day?
    Use:

    SET dt = CONVERT(VARCHAR,GETDATE(),112)

    to get only the today's date without the time.

    > Does it use index on the date column in this example?
    Yes, this query is optimizable using an index on the date column. I would
    expect SQL to use the index but whether it actually does or not depends
    largely on your data. If the expected result set is large enough then SQL
    may bypass the index altogether.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

Similar Threads

  1. Select Sum acrross tables
    By Jared in forum MySQL
    Replies: 1
    Last Post: May 25th, 04:15 AM
  2. Select accoss 2 tables
    By ShowOffs.ca in forum PHP Development
    Replies: 0
    Last Post: June 11th, 07:32 PM
  3. Select from two tables
    By Taper Litwater in forum ASP
    Replies: 4
    Last Post: October 27th, 08:41 PM
  4. MySQL Q - SELECT with 2 tables
    By Geoff Berrow in forum PHP Development
    Replies: 4
    Last Post: September 5th, 07:17 AM
  5. SELECT DISTINCT from two tables
    By Marco Alting in forum ASP
    Replies: 4
    Last Post: July 31st, 10:26 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139