GoFuckYourself.com - Adult Webmaster Forum

GoFuckYourself.com - Adult Webmaster Forum (https://gfy.com/index.php)
-   Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)
-   -   Help with sql query and win a t3report (https://gfy.com/showthread.php?t=802399)

FightThisPatent 01-25-2008 09:34 AM
Help with sql query and win a t3report
 
My brain is fried right now on inner joins. I need an sql query to achieve the output results in the image below:

http://www.fightthepatent.com/t3sql.gif

at the end of your query would be this statement:

and url_domain = 'a.com'

using postgres as the db (shouldn't matter for the query if you are a mysql guru)


first to get the query correct wins a t3report of your choosing (up to a 3 credit report which is a $1200 value).

this is also a mini-interview as I have need for some sql/perl ppl

Fight the contest!

k0nr4d 01-25-2008 09:38 AM
erm...huh?

munki 01-25-2008 09:47 AM
I just realised trying to put that query together in my head... that since I'm thinking in smartys only right now, its time to sleep until my brain starts functioning properly again...

candyflip 01-25-2008 09:53 AM
I passed it along to someone.

FightThisPatent 01-25-2008 10:18 AM
Quote:
Originally Posted by k0nr4d (Post 13696330)
erm...huh?

data analysis from my big database of spidering of websites.


Fight the warehouse!

k0nr4d 01-25-2008 11:41 AM
Quote:
Originally Posted by FightThisPatent (Post 13696496)
data analysis from my big database of spidering of websites.


Fight the warehouse!
I meant more as in 'Huh? What are you trying to do?' and to post an sql dump of your tables :P

FightThisPatent 01-25-2008 11:44 AM
Quote:
Originally Posted by k0nr4d (Post 13696918)
I meant more as in 'Huh? What are you trying to do?' and to post an sql dump of your tables :P

LOL.. i don't think i could upload 1+ TB here :)

Doing some data mining that requires an inner join to the table to pull in linking relationship (its for what i call a T3flow in the t3report).

I have it done right now as separate queries in perl, but wanted an elegant 1 sql query line to do the equivalent.. much like people when people those 1-liner perl contests.


Fight the too many data!

mikesouth 01-25-2008 01:41 PM
give me the schema of the tables involved and the sql you're using for starters that may give me an idea of what you are trying to accomplish here

barring that give me the schemas and tell me what you are trying to do.

market4s 01-25-2008 01:47 PM
cake... ;-)))

Code:
select t1.url, t1.url_domain, t1.referer, t1.referer_domain, t2.referer, t2.referer_domain, t2.last_spidered
    from table t1, table t2
    where t1.referer_domain = t2.domain

FightThisPatent 01-25-2008 02:00 PM
Quote:
Originally Posted by mikesouth (Post 13697488)
give me the schema of the tables involved and the sql you're using for starters that may give me an idea of what you are trying to accomplish here

barring that give me the schemas and tell me what you are trying to do.

the schema is very simple, you can see it from the snapshot:

url text
url_domain text
referer text
referer_domain text
last_spidered date

i provided 2 dummy data rows from the table.

the task is to have an sql query that returns the columns/data listed in the output. the sql query would require an inner join


Fight the query!

market4s 01-25-2008 02:04 PM
look up ^^^ :winkwink:

what do i need for the T3report? it looks awesome. can't wait to try it.

send here please : [email protected]

FightThisPatent 01-25-2008 02:31 PM
Quote:
Originally Posted by market4s (Post 13697579)
look up ^^^ :winkwink:

what do i need for the T3report? it looks awesome. can't wait to try it.

send here please : [email protected]
you were close! just needed "and t1.referer = t2.url" at the end to tie the urls together.

without it, it was like a union

sweet.. going to plug this into my code and run it against the production database to do real test against the big database.

i'll hit you up in email.

Fight the solution!

FightThisPatent 01-25-2008 02:57 PM
If anyone came up with a different sql query, feel free to post, i'll do runner up prizes :)

Fight the you don't win second place, you lost first place!

mikesouth 01-25-2008 02:59 PM
Quote:
Originally Posted by FightThisPatent (Post 13697705)
you were close! just needed "and t1.referer = t2.url" at the end to tie the urls together.

without it, it was like a union

sweet.. going to plug this into my code and run it against the production database to do real test against the big database.

i'll hit you up in email.

Fight the solution!
yup that'd do it...oh well story of my life always a bridesmaid never a bride

LOL

FightThisPatent 01-25-2008 03:02 PM
Quote:
Originally Posted by mikesouth (Post 13697834)
yup that'd do it...oh well story of my life always a bridesmaid never a bride

LOL

and here i thought you just filmed nudie ppl and posted rants against FSC.

:1orglaugh

Fight the multi-talented!

JayS 01-25-2008 03:06 PM
Code:
SELECT a.url, a.url_domain, a.referer, a.referer_domain, b.referer_domain AS referer2, b.referer AS referer_domain2, b.last_spidered
FROM t1 AS a  INNER JOIN t1 AS b
ON b.url_domain = a.referer_domain
WHERE 1 AND a.url_domain = 'a.com'
Too late but.... :1orglaugh

FightThisPatent 01-25-2008 03:40 PM
Quote:
Originally Posted by JayS (Post 13697872)
Code:
SELECT a.url, a.url_domain, a.referer, a.referer_domain, b.referer_domain AS referer2, b.referer AS referer_domain2, b.last_spidered
FROM t1 AS a  INNER JOIN t1 AS b
ON b.url_domain = a.referer_domain
WHERE 1 AND a.url_domain = 'a.com'
Too late but.... :1orglaugh
now this is what i was expecting to see as a query (still needs one more where clause to link ur/referer but on the right track)... send me email to brandon -at- t3report.com to send me a few domains to lookup for a t3report. congrats on 2nd place.

Fight the runner up!


All times are GMT -7. The time now is 02:48 PM.

Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2025, vBulletin Solutions, Inc.
©2000-, AI Media Network Inc123