Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

sql is quite tough

DarQDarQ Posts: 1,625Member
hi

i could have fixed the problem by using subqueries in pgsql. bah, im using mysql now and it doesnt support subqueries. i could have fixed my problem by using php with several queryresults, but i prefer to use only sql for this. i knew when i started that i would run into the following problem.

btw: im sorry that its such a long piece of text. but i really need to explain it as good as possible.

allright, i have 6 tables that are involved in the problem:
- bots
- browsers
- ipcountry
- os
- visitors
- visits[/red]

the 2 below are always involved in the queries. on top of those, either bots, browsers, os or countries.

there is only 1 variable given. that is userID. the table visits acts here as a linktable (hmm, we call it like that). see:
[code]
ID userID visitorID botID timestamp referrer
1 1 1 0 1093778117
2 1 1 0 1093778118 statiqz.space.servehttp.com
[/code]

i first need this:
SELECT visitorID from visits WHERE userID=''

the table visitors is next.

[code]
ID countryID ip xres yres bits java browserID osID
1 NETHERLANDS 62.194.168.26 1024 768 24 0 1 1
2 127.0.0.1 0 0 0 1 17
[/code]

(dont worry about NL there, ill change it later)

since the following procedure is there same for countryID(and table countries), osID (table os) and browserID (table browsers duh).

SELECT visitorID from visits WHERE userID=''
this returns all visitorID's i want. obviously, these point to ID in the table above.

ok, now for the real problem (ill take the table [red]os[/red] in mind because countries and browsers are no different, you should also keep osID in the returned array of visitors):

the table os contains only:
[code]
ID name parameter
1 Linux linux
2 Windows windows
[/code]

[red]ok, we have (an array, not DISTINCT) osID from all visitors that made visits on userID [/red]

now i would like to have (from the os table):
the name (visitors.osID points to os.ID) and the number of DISTINCT osID's we got.

in plain english. i would like to display the number of people that got linux or windows(list will be extended later).

but how to do it in MySQL?

i really hope someone knows a query. otherwise ill have to do it with php or pgsql.

big thnx in advance
[size=5][italic][blue]Dar[RED]Q[/RED][/blue][/italic][/size]
Jou my goddomme no rap dy lekkere dikke tsjap

Comments

  • lionblionb Posts: 1,688Member ✭✭
    I've never used MySQL and do not know does it support JOIN technique or not. I am also not sure how your is databse organized. Are tables visits and os related? Seems to me they are. If so and MySQL supports JOIN you can try it. On SQL Serever I would try following
    [code]
    SELECT visits.visitorID,os.name from visits IINER JOIN os ON visits.id = os.id
    WHERE visits.userID=''
    [/code]


  • DarQDarQ Posts: 1,625Member
    lionb :)

    thank you very much for replying.

    visits and os are not related, only via visitors.

    anyway, i didnt think someone would reply on this. i posted the same problem on a dutch board with no response.

    so i tried, and it works my way :D i retrieve an array of numberOfOs and osname.

    [code]
    $sql = 'SELECT
    COUNT( DISTINCT (`visitors`.`ID`) ) as `osSum`, `os`.`name`
    FROM `visits`, `visitors`, `os`
    WHERE `visits`.`userID` = ?
    AND `visitors`.`ID` = `visits`.`visitorID`
    AND `os`.`ID` = `visitors`.`osID`
    GROUP BY (`os`.`ID`)
    ORDER BY osSum DESC';
    [/code]

    im very sure that if i hadnt already fixed it, your reply would certainly point me into the right direction.

    greets!

    : I've never used MySQL and do not know does it support JOIN technique or not. I am also not sure how your is databse organized. Are tables visits and os related? Seems to me they are. If so and MySQL supports JOIN you can try it. On SQL Serever I would try following
    : [code]
    : SELECT visits.visitorID,os.name from visits IINER JOIN os ON visits.id = os.id
    : WHERE visits.userID=''
    : [/code]
    :
    :
    :

    [size=5][italic][blue]Dar[RED]Q[/RED][/blue][/italic][/size]
    Jou my no rap dy lekkere dikke tsjap

  • lionblionb Posts: 1,688Member ✭✭
    : lionb :)
    :
    : thank you very much for replying.
    :
    : visits and os are not related, only via visitors.
    :
    : anyway, i didnt think someone would reply on this. i posted the same problem on a dutch board with no response.
    :
    : so i tried, and it works my way :D i retrieve an array of numberOfOs and osname.
    :
    : [code]
    : $sql = 'SELECT
    : COUNT( DISTINCT (`visitors`.`ID`) ) as `osSum`, `os`.`name`
    : FROM `visits`, `visitors`, `os`
    : WHERE `visits`.`userID` = ?
    : AND `visitors`.`ID` = `visits`.`visitorID`
    : AND `os`.`ID` = `visitors`.`osID`
    : GROUP BY (`os`.`ID`)
    : ORDER BY osSum DESC';
    : [/code]
    :
    : im very sure that if i hadnt already fixed it, your reply would certainly point me into the right direction.
    :
    Anyway using JOIN, if it's possible on MySQL, more efficient. Try
    something like that
    [code]
    SELECT visits.visitorID,os.name from visits IINER JOIN os ON visitors.id = visits.id INNER JOIN
    os on visitors.id = os.id
    WHERE visits.userID=''
    [/code]
  • DarQDarQ Posts: 1,625Member
    : : lionb :)
    : :
    : : thank you very much for replying.
    : :
    : : visits and os are not related, only via visitors.
    : :
    : : anyway, i didnt think someone would reply on this. i posted the same problem on a dutch board with no response.
    : :
    : : so i tried, and it works my way :D i retrieve an array of numberOfOs and osname.
    : :
    : : [code]
    : : $sql = 'SELECT
    : : COUNT( DISTINCT (`visitors`.`ID`) ) as `osSum`, `os`.`name`
    : : FROM `visits`, `visitors`, `os`
    : : WHERE `visits`.`userID` = ?
    : : AND `visitors`.`ID` = `visits`.`visitorID`
    : : AND `os`.`ID` = `visitors`.`osID`
    : : GROUP BY (`os`.`ID`)
    : : ORDER BY osSum DESC';
    : : [/code]
    : :
    : : im very sure that if i hadnt already fixed it, your reply would certainly point me into the right direction.
    : :
    : Anyway using JOIN, if it's possible on MySQL, more efficient. Try
    : something like that
    : [code]
    : SELECT visits.visitorID,os.name from visits IINER JOIN os ON visitors.id = visits.id INNER JOIN
    : os on visitors.id = os.id
    : WHERE visits.userID=''
    : [/code]
    :

    ill give it a try
    [size=5][italic][blue]Dar[RED]Q[/RED][/blue][/italic][/size]
    Jou my no rap dy lekkere dikke tsjap

  • lionblionb Posts: 1,688Member ✭✭
    :
    : ill give it a try
    : [size=5][italic][blue]Dar[RED]Q[/RED][/blue][/italic][/size]
    : Jou my no rap dy lekkere dikke tsjap
    :
    Good luck! :-)

Sign In or Register to comment.