Help with sql query and win a t3report - GoFuckYourself.com

FightThisPatent · 01-25-2008, 09:34 AM

My brain is fried right now on inner joins. I need an sql query to achieve the output results in the image below:

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

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

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

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

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

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

look up ^^^

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

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

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.

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

FightThisPatent · 01-25-2008, 03:40 PM

Quote:

Originally Posted by JayS

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

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!

01-25-2008, 09:34 AM	#1
FightThisPatent Confirmed User Join Date: Aug 2003 Location: Austin, TX Posts: 4,090	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: 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! __________________ http://www.t3report.com (where's the traffic?) v5.0 is out! \| http://www.FightThePatent.com \| ICQ 52741957

01-25-2008, 09:38 AM	#2
k0nr4d Confirmed User Industry Role: Join Date: Aug 2006 Location: Poland Posts: 9,229	erm...huh? __________________ Mechanical Bunny Media Mechbunny Tube Script \| Mechbunny Webcam Aggregator Script \| Custom Web Development

01-25-2008, 09:47 AM	#3
munki Do Fun Shit. Industry Role: Join Date: Dec 2004 Location: OC Posts: 13,393	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... __________________ “I have the simplest tastes. I am always satisfied with the best.” -Oscar Wilde

01-25-2008, 09:53 AM	#4
candyflip Carpe Visio Industry Role: Join Date: Jul 2002 Location: New York Posts: 43,064	I passed it along to someone. __________________ Spend you some brain. Email Me

01-25-2008, 01:41 PM	#8
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	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. __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

01-25-2008, 01:47 PM	#9
market4s Confirmed User Join Date: May 2003 Location: Earth.... I think? Posts: 243	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 __________________ DreamerCafe.com

01-25-2008, 02:04 PM	#11
market4s Confirmed User Join Date: May 2003 Location: Earth.... I think? Posts: 243	look up ^^^ what do i need for the T3report? it looks awesome. can't wait to try it. send here please : [email protected] __________________ DreamerCafe.com

01-25-2008, 02:57 PM	#13
FightThisPatent Confirmed User Join Date: Aug 2003 Location: Austin, TX Posts: 4,090	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! __________________ http://www.t3report.com (where's the traffic?) v5.0 is out! \| http://www.FightThePatent.com \| ICQ 52741957