SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    count from 2 tables

    Hi
    i have 2 tables in my database cardA, and cardb, i have a field called ticket in both tables, what i want to do is if field name ticket has value of 1 do a count then output the total, so if ticket in both tables have the value of 1 the total would be 2 how do i do this, this is the code i have but outputs the total 0 not 2

    PHP Code:
    $query sprintf("SELECT cardA.ticket,cardb.ticket, COUNT(cardA.ticket) and COUNT(cardb.ticket) AS total FROM cardA, cardb GROUP BY cardA.ticket,cardb.ticket
    HAVING ( COUNT(cardA.ticket) > 0 )AND( COUNT(cardb.ticket) > 0 ) "


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    are you trying to count the values? or just the values that are 1? what's a ticket? does ticket have any other values besides 1? does the same ticket number have to exist in both tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    the tables in my database are cardA, cardb, this goes all the way to cardl but at the moment just trying to get two tables to work,
    i have some bingo cards writen with flash and php, when a user chooses a ticket it writes 15 numbers into the table lets say cardA and changes the table field ticket to 1 from the default 0
    so im just trying to add all the 1's up and ouput the result so if the field ticket is still 0 it wont count.
    so if they have clicked cardA and cardb the output would be 2 if they just clicked cardA and not cardb the output would be 1

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    whoa, i'm lost, that explanation is really confusing, plus the fact that you have 10 tables where probably you need only one

    anyhow, are you perhaps trying to UNION the results maybe?
    Code:
    SELECT 'A' AS card
         , ticket
         , COUNT(*) AS total 
      FROM cardA
    GROUP 
        BY ticket
    UNION ALL
    SELECT 'B' AS card
         , ticket
         , COUNT(*) AS total 
      FROM cardB
    GROUP 
        BY ticket
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    i tried your code but still gives me a result of 0
    all im trying to do is add all the 1 in the database together so if table carda field ticket has a value of 1 and table 2 cardb has a 1 in the field ticket it will add the two togeter giving me a result of 2.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    still not making a lot of sense to me, but here's another attempt based on your latest explanation...
    Code:
    SELECT COUNT(*) AS total 
      FROM ( SELECT ticket
               FROM cardA
              WHERE ticket = 1 
             UNION ALL
             SELECT ticket
               FROM cardB
              WHERE ticket = 1 
           ) AS d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    i will try to explain what i want to do

    i have 12 bingo cards with numbers on the card, a user clicks a card and it writes to the database posting all numbers, and also changing the field ticket from 0 to 1,
    i have 12 tables in my database named cardA cardB cardC, and so on to 12.
    all tables have a field called ticket, so if a user clicks 3 cards the output would be 3,
    so i have to do some sort of count ,
    count all ticket fields with 1
    hope this explains.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't think you need 12 tables for this, but that would be a separate thread

    did you try my latest query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi yes i tried your query, i got no output not even 0 just blank.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you must've done something wrong in translating my query to your "real" table and column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    i get this when i use your query ticket=
    doesn't bring any result from the database.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please show the exact query that you ran

    you tested it outside of php, of course?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    I have to say I think r937's suggestion would be better - put all the cards in a single table and call count across a single table. Otherwise your query string is going to get SERIOUSLY large when you try and union 12 tables.

  14. #14
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    im not sure how to put all cards into one table, it works fine the way i have it.
    i thought it would be easy just to a count of all the fields named ticket.

    here is the query r937 gave me
    if i run the query outside php i get total 1 it should be 2

    PHP Code:
    SELECT COUNT(*) AS total 
      FROM 
    SELECT ticket
               FROM cardA
              WHERE ticket 

             UNION ALL
             SELECT ticket
               FROM cardb
              WHERE ticket 

           
    ) AS total", 

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pwesthead View Post
    if i run the query outside php i get total 1 it should be 2
    in that case, you have a data problem

    could you dump the table structures, please, as well as a few rows of data for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi i was a little brain dead yesterday late night,
    i have decided to take your advise and use 1 table for all cards, if they click on a card it will just keep adding 1 to the total and then run 1 query to get the result,i will try it this way

    thankyou


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
  •