SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error: select code from 2different join tables

    Hi
    i have the code below to select the count of two differnt tables.

    however the results give me the same value.
    What is wrong where ?

    Code:
    SELECT  COUNT(dbo.Accounting_Financials_PO_invoice.usr) AS past, COUNT(dbo.e_invoice.email) AS present
    FROM         dbo.Accounting_Financials_PO_invoice INNER JOIN
                          dbo.e_invoice ON dbo.Accounting_Financials_PO_invoice.usr = dbo.e_invoice.usr
    WHERE     (dbo.Accounting_Financials_PO_invoice.usr = 'afrika')
    thanks
    Afrika

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it's easy to see why, if you look at what happens in a join

    first, the FROM clause is evaluated --
    Code:
      FROM dbo.Accounting_Financials_PO_invoice 
    INNER 
      JOIN dbo.e_invoice 
        ON dbo.Accounting_Financials_PO_invoice.usr 
         = dbo.e_invoice.usr
    this produces a result set with matching rows from both tables --

    freddy fred@example.com
    antony tony@example.com
    afrika afrk@example.com
    andrew drew@example.com
    afrika afka@example.com
    willem bill@example.com

    then, the WHERE clause is executed --
    Code:
    WHERE (dbo.Accounting_Financials_PO_invoice.usr = 'afrika')
    this leaves a shorter result set, but each row in the result set still has columns from both tables --

    afrika afrk@example.com
    afrika afka@example.com

    finally, the GROUP BY is executed, and the COUNT() aggregate functions evaluate the number of non-null values in each column --

    2 2

    they are always going to be the same, as long as they don't have NULLs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy
    So i guess i have to create multiple recordsets for each table ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, not at all

    what exactly is it that you want to count? usrs? emails? usrs that match emails? users and the count of emails that each usr has, even if none?

    it all depends on what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Am trying to count the total records of users who have paid and unpaid invoices.

    I just thought it might be wrong to use multiple recordsets to count them.

    I just picked a column ie users and email to count the number available.

    rgds

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the total users who have paid and unpaid invoices would be all of them, no?

    so just count the users

    oh, did you mean one count of users who have paid invoices, and another count of users who have unpaid invoices?

    what about a user who has both paid and unpaid invoices?

    what about a user who has no invoices at all?

    i'm sorry to be so picky, but you must realize that there is a wide range of possible sql solutions, and the correct one will depend precisely on what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry i have been out of town, just came in.

    well basically want i we have are two tables
    1. with users with paid invoices dbo.Accounting_Financials_PO_invoice
    2. with users with unpaid invoices dbo.e_invoice

    Now we have an ASP page where they could view their invoices paid and unpaid. Instead of creating two recordsets, i thought it might be simpler to create one query with the count of both paid and unpaid invoices.

    which is better ?

    Am using this code now and it works well.

    select a.past, b.present
    from (
    select COUNT(usr) past
    from dbo.Accounting_Financials_PO_invoice
    where usr = 'afrika'
    ) a

    cross join

    (
    select COUNT(email) AS present
    from dbo.e_invoice
    where usr = 'afrika'
    ) b

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's very ingenious!! definitely beyond "sql 101" level

    your query is fine, and efficient, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot, Rudy
    As i said we learn everyday

    rgds
    Afrika


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
  •