visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: SELECT from multiple tables
By: Squibi U.S.A.  Date: 17/02/2003 00:00:00  English  Points: 100 Status: Answered
Quality : Excellent
Hey.

On my website I have been keeping track of statistical data in 3 tables for awhile now. 1 table tracks which files a person downloads, another which local links they click on, and the last which external links they click on.

Up until now Ive just been using the information to create reports about which downloads/links are most popular. But now Ive tried to also implement user tracking, to see what an individual user has been doing. Im not so proficient with SQL though, so Im not sure how the query should look.

The first thing I tried was SELECT * FROM table1, table2, table3 WHERE UserID=xyz. Of course I soon found out this wasnt what I wanted since it was returning the number of rows in the largest table to the power of 3 results.

Basically what I would like is to have the information in the 3 tables returned in 1 column, sorted by a date column. So if a user downloaded a file, clicked a local link, then an external link it might look like:

Row 1) download
Row 2) local link
Row 3) external link

instead of

Row 1) download | local link | external link

Like I said I dont know a lot about SQL so maybe this isnt possible. I guess if it isnt then the best solution is to keep track of all this data in 1 table?

Thanks
By: TheFalklands Date: 17/02/2003 05:55:00 English  Type : Comment
how bout:

SELECT * FROM table1, table2, table3 WHERE table1.UserID = xyz AND table2.UserID = table1.UserID AND table3.UserID = table2.UserID
By: TheFalklands Date: 17/02/2003 05:56:00 English  Type : Comment
how bout:

btw, this assumes all 3 tables have a field called UserID...
if this doesn't work for you, please post the table structures of the three tables so we can come up w/ a query that will do what you need.
By: VGR Date: 17/02/2003 19:48:00 English  Type : Answer
Row 1) download
Row 2) local link
Row 3) external link

in stead of Row 1) download | local link | external link


IMPOSSIBLE

sorry. Databases are composed of rows and columns, who are like records and fields. You ask for an output that will not have the same number of lines as the number of records/lines in the DB. That's impossible using SQL.

You may achieve this by using PHP (for example), though.
By: Squibi Date: 17/02/2003 20:35:00 English  Type : Comment
That's not the answer I was hoping for, but it is what I was expecting. I just wanted to confirm it before abandoning the current database structure.

Thanks
By: VGR Date: 17/02/2003 20:48:00 English  Type : Comment
no, I find your structure right for your needs, but for THAT need of yours, you need a "program" because the request(s) are too complex (at least, for me)

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 889.683010 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page