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:

 ID   	  userID   	  visitorID   	  botID   	  timestamp   	  referrer
1 	1 	1 	0 	1093778117 	 
2 	1 	1 	0 	1093778118 	statiqz.space.servehttp.com

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

the table visitors is next.

 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

(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='<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 os 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:

 ID   	  name   	  parameter
1 	Linux 	        linux
2 	Windows 	windows

ok, we have (an array, not DISTINCT) osID from all visitors that made visits on userID <userID>

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
DarQ
Jou my goddomme no rap dy lekkere dikke tsjap

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

SELECT visits.visitorID,os.name from visits IINER JOIN os ON visits.id = os.id 
WHERE visits.userID='<userID>'

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.

    $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';

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
:

: SELECT visits.visitorID,os.name from visits IINER JOIN os ON visits.id = os.id 
: WHERE visits.userID='<userID>'
: 

:
:
:

DarQ
Jou my no rap dy lekkere dikke tsjap

: 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.
:
:

:     $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';
: 

:
: 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

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='<userID>'

: : 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.
: :
: :

: :     $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';
: : 

: :
: : 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
:

: 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='<userID>'
: 

:

ill give it a try
DarQ
Jou my no rap dy lekkere dikke tsjap

:
: ill give it a try
: DarQ
: Jou my no rap dy lekkere dikke tsjap
:
Good luck!