Results 1 to 4 of 4
  1. #1

    SQL command to get data from 2 different tables

    table 1 = visitors
    date, time, ip, (same ip can be multiple times if same visitors visits site many time)

    table 2 = ips
    ip, country (ip is unique in this table)


    Visitors table contains record of each visitor, if one visitors visits site 10 times then 10 records will be added in visitors table. I want to show that how many times a visitor came from single ip with country name like

    105.24.141.100 - Canada - 10 times
    145.32.147.87 - US - 15 times

    What will be SQL command of this output?

    GCS

  2. #2
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Code:
    SELECT `ip`,`country`, COUNT(*) AS `visits`
    FROM `visitors` JOIN `ips` USING (`ip`)
    GROUP BY `ip`;
    You'll probably want indexes on ip in both tables - use EXPLAIN to check because it could get seriously inefficient with a lot of data to sort through.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  3. #3
    Quote Originally Posted by foobic View Post
    Code:
    SELECT `ip`,`country`, COUNT(*) AS `visits`
    FROM `visitors` JOIN `ips` USING (`ip`)
    GROUP BY `ip`;
    You'll probably want indexes on ip in both tables - use EXPLAIN to check because it could get seriously inefficient with a lot of data to sort through.
    Although this is simple command for programmers but difficult for me because I am not full time programmer.

    Thank you man, it worked great.

    GCS

  4. #4
    Hi man

    I need another strong and complicated sql command with 5 different conditions.

    campaign table
    camp_id,order_visits,daily_visits,last_visit_time,status

    status: Active or Completed

    visitors table
    date,time,ip,camp_id

    I need sql statement that can select one record i.e. I need campaign id with following condition

    1. Campaign status is active
    2. Campaign's total visits are less than order_visits (count from visitors table)
    3. Campaign's today's visits are less than or equal to daily_visits
    4. If there are more than 2 campaigns then select campaign with old last_visit_time (I mean which is visited earlier)
    5. Same ip should not have today's visit i.e. daily unique ip. If this ip has visited by one camp, then don't visit again.

    I want to use less commands because one visitor will arrive to this script it should take less time in calculating and selecting the url where to send this visitor.

    Please help me.

    GlobalCashSite
    Last edited by globalcashsite; 05-11-2011 at 06:22 AM. Reason: Add condition 5

Similar Threads

  1. How to alter all tables in MYSQL with only one command
    By HelpHelp in forum Programming Discussion
    Replies: 11
    Last Post: 11-19-2015, 11:23 AM
  2. compressing sql tables ?
    By jjk2 in forum Programming Discussion
    Replies: 3
    Last Post: 04-08-2009, 03:11 PM
  3. SQL Count from 2 tables?
    By ChrisF79 in forum Programming Discussion
    Replies: 4
    Last Post: 11-01-2005, 06:46 AM
  4. Command line syntax - dumping individual tables in Mysql
    By techmonkey in forum Programming Discussion
    Replies: 6
    Last Post: 06-05-2004, 10:55 AM
  5. Replies: 2
    Last Post: 01-06-2004, 10:12 PM

Posting Permissions

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