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)

- - any mysql gurus here? (https://gfy.com/showthread.php?t=720679)

martinsc

04-03-2007 02:23 AM

any mysql gurus here?

ok, this is weird... at least for me...
i have three tables
sites - id (autoinc), name, url
dumps - id (autoinc), name, url
posted - id (autoinc), dumpid, linkid, date
i want to show in a query all combinations of sites+dumps that do not exist in posted. (which means links that have not been submitted to dumps).
i am using the following query and it works great in MS sql, but in My sql i always get records back although all combos exist in posted...

Code:

SELECT site, dumpi, sitename, dumpname, dumpurl, siteurl

FROM

(SELECT sites.id AS site, 

     dumps.id AS dumpi, 

     sites.name AS sitename, 

     dumps.name AS dumpname, 

     dumps.url AS dumpurl, 

     sites.url AS siteurl 

FROM sites, dumps) Q

LEFT JOIN posted on

Q.site = posted.siteid

and Q.dumpi = posted.dumpid

WHERE posted.stieid IS NULL

could anybody please take a look at this and tell me, where i am wrong?

thanks a lot!!!

mikeyddddd

04-03-2007 03:12 AM

Shouldn't WHERE posted.stieid IS NULL
be WHERE posted.siteid IS NULL
???

martinsc

04-03-2007 03:18 AM

Quote:

Originally Posted by mikeyddddd (Post 12186897)

Shouldn't WHERE posted.stieid IS NULL
be WHERE posted.siteid IS NULL
???

? i don't see any difference????

mikeyddddd

04-03-2007 03:21 AM

Look closely. posted.sTIeid has never been set. posted.sITeid = Q.site.

quantum-x

04-03-2007 03:24 AM

I'm too tired to work it all through at the moment, but you probably want to use the EXISTS keyword, or use the ANY comparision of subqueries.

martinsc

04-03-2007 03:25 AM

Quote:

Originally Posted by mikeyddddd (Post 12186928)

Look closely. posted.sTIeid has never been set. posted.sITeid = Q.site.

damn, you are right...
but the typo is only here on the text... in my code it's site... and still doesn't work...
damn mysql....

martinsc

04-03-2007 03:27 AM

Quote:

Originally Posted by quantum-x (Post 12186939)

I'm too tired to work it all through at the moment, but you probably want to use the EXISTS keyword, or use the ANY comparision of subqueries.

can you tell me a bit more about that after you wake up? i have no clue about what you just said...

quantum-x

04-03-2007 04:13 AM

Quote:

Originally Posted by martinsc (Post 12186954)

can you tell me a bit more about that after you wake up? i have no clue about what you just said...

These pages do it much more succintly than I could-
http://dev.mysql.com/tech-resources/...ubqueries.html
http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

martinsc

04-03-2007 07:05 AM

Quote:

Originally Posted by quantum-x (Post 12187058)

These pages do it much more succintly than I could-
http://dev.mysql.com/tech-resources/...ubqueries.html
http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

THanks.
i will take a look at it.
but why doesn't the query above doesn't work 100%?
it works in MSSQL fine...

All times are GMT -7. The time now is 04:35 PM.