Help with sql query and win a t3report

[FightThisPatent]

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]

erm...huh?

[munki]

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]

I passed it along to someone.

[FightThisPatent]

erm...huh?

data analysis from my big database of spidering of websites.


Fight the warehouse!

[k0nr4d]

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]

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]

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]

cake... ;-)))

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]

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]

look up ^^^

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

send here please : [email protected]

[FightThisPatent]

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]

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]

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]

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]

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]

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!