mysql gurus - GoFuckYourself.com

Cyborg69 · 01-25-2004, 11:17 AM

i have table with site url, url, and text where are not unique
and i need to select from the table something like this
site url
url1- tex1
url2- tex2
url3- tex3
site2 url
url1- tex1
....
and so on well i can do this with 2 querries first selecting site url then with a limit selecting url and text but is it possible to do everything in one querry ?

icedemon · 01-25-2004, 11:28 AM

I'm not sure what your asking. You wanna show the 2 select statements your using and maybe explain what your trying to find? Are your trying to find text fields that match or what?

Cyborg69 · 01-25-2004, 11:31 AM

here is what i am doing right now
SELECT DISTINCT surl FROM table WHERE text like '%something%'
then i do
SELECT text,url FROM table WHERE 1 AND `surl`= '$surl' AND text like '%something%' LIMIT 5

i was wondering if its possible to join those 2 together

TDJ · 01-25-2004, 11:40 AM

You could if you were using postgres or a brand new beta mysql version, then you can use subselects:

then i do
SELECT text,url FROM table WHERE 1 AND surl IN (SELECT DISTINCT surl FROM table WHERE text like '%something%') AND text like '%something%' LIMIT 5

Sounds like your table structure is poorly setup, else it should just be a matter of:

SELECT a.whatever FROM tableA a, tableB b WHERE b.value='$blah' and a.id=b.id

That might be too technical an explanation for you, sorry if so... Otherwise, using a 'stable' mysql release.. no.. no sub-selects.

Cyborg69 · 01-25-2004, 11:43 AM

storing things in 2 different tables in not tha answer
i need maximum 5 results from the same domain....

icedemon · 01-25-2004, 12:19 PM

Have you tried using the GROUP BY function? I think you could do something like this

SELECT text,url FROM table WHERE text LIKE '%something%' GROUP BY surl LIMIT 5

You might have to group both the surl and text
GROUP BY surl, text

Cyborg69 · 01-25-2004, 12:39 PM

limit will give only 5 overall results

Cyborg69 · 01-25-2004, 12:44 PM

SELECT text,url FROM table WHERE text LIKE '%something%' GROUP BY surl LIMIT 5

group by sulr make it same as
SELECT distinc surl, text,url FROM table WHERE text LIKE '%something%' LIMIT 5

icedemon · 01-25-2004, 12:53 PM

Quote:

Originally posted by Cyborg69
limit will give only 5 overall results

OK, I see what your doing now. I think your stuck with the way you've been doing it. If you were using Postgres, you could use Views.

Cyborg69 · 01-25-2004, 02:33 PM

can u give me an example how could i do this using Postgres

icedemon · 01-25-2004, 03:34 PM

Quote:

Originally posted by Cyborg69
can u give me an example how could i do this using Postgres

You would basically create one VIEW (view1) with the first select that you have. Then create another VIEW (view2) to query view1. Then in your program or in Postgres, you just do a select on (view2).

Or you could just create the first VIEW (view1) and do a select on that. You can find more info at
http://www.postgresql.org/docs/curre...reateview.html

Basically, a VIEW is command (like SELECT) that is kinda made into a seperate table.

fris · 01-25-2004, 03:38 PM

are you doing things from command line or using phpMyAdmin?

command line gave me headaches phpMyAdmin makes things a lot easier.

Cyborg69 · 01-25-2004, 06:29 PM

testing it in phpMyAdmin ,the script is in php

KobyBoy · 01-25-2004, 11:39 PM

I understand what you are talking about. What you want is called a Sub Query. Yes, you can do it with MySQL. Here's a little tutorial I've found: http://www.onlamp.com/pub/a/onlamp/2.../aboutSQL.html

KobyBoy · 01-25-2004, 11:40 PM

Here are two more links for you:

http://www.devshed.com/c/a/MySQL/Usi...-MySQL-part-1/

and

http://www.devshed.com/c/a/MySQL/Usi...-MySQL-part-2/

Cyborg69 · 01-26-2004, 12:56 PM

i could get what i was trying to with subquerries

Cyborg69 · 01-26-2004, 01:44 PM

I have another example of what i need
lets say a company has a list or all sales people and state their working in and i want to get the top 5 salesman from each state

DrGuile · 01-26-2004, 01:48 PM

two tables would be the right way to do it.

Seems like you have duplicating info in that table.

Cyborg69 · 01-26-2004, 02:03 PM

one column has dublicated info and if i bring it out to a separete table it still won't make the job easier
if u think u can do it with 2 tables write a querry...

Cyborg69 · 01-27-2004, 08:11 AM

cube³ · 01-27-2004, 04:59 PM

Pencils down, kids.

select t1.url, t1.txt, t2.txt from tbl t1 inner join txt t2 on t1.url=t2.url where t1.txt like '%bl%' group by t1.url, t2.txt having t2.txt like '%bl%' limit 5;

Don't you big players use Oracle? Come on... for the cost of just one of your Bentley's.

-s

01-25-2004, 11:17 AM	#1
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	mysql gurus i have table with site url, url, and text where are not unique and i need to select from the table something like this site url url1- tex1 url2- tex2 url3- tex3 site2 url url1- tex1 .... and so on well i can do this with 2 querries first selecting site url then with a limit selecting url and text but is it possible to do everything in one querry ?

01-25-2004, 11:28 AM	#2
icedemon Confirmed User Join Date: Jun 2003 Location: Lutz, FL Posts: 1,022	I'm not sure what your asking. You wanna show the 2 select statements your using and maybe explain what your trying to find? Are your trying to find text fields that match or what? __________________ Clips4Sale.com

01-25-2004, 12:19 PM	#6
icedemon Confirmed User Join Date: Jun 2003 Location: Lutz, FL Posts: 1,022	Have you tried using the GROUP BY function? I think you could do something like this SELECT text,url FROM table WHERE text LIKE '%something%' GROUP BY surl LIMIT 5 You might have to group both the surl and text GROUP BY surl, text __________________ Clips4Sale.com

01-25-2004, 03:38 PM	#12
fris Too lazy to set a custom title Industry Role: Join Date: Aug 2002 Posts: 55,372	are you doing things from command line or using phpMyAdmin? command line gave me headaches phpMyAdmin makes things a lot easier. __________________ Since 1999: 69 Adult Industry awards for Best Hosting Company and professional excellence. WP Stuff

01-26-2004, 01:48 PM	#18
DrGuile Confirmed User Join Date: Jan 2002 Posts: 2,025	two tables would be the right way to do it. Seems like you have duplicating info in that table. __________________ LiveBucks / Privatefeeds - Giving you money since 1999 Up to 50% Commission! 25% Webmaster Referal Powered by Gamma

01-25-2004, 11:31 AM	#3
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	here is what i am doing right now SELECT DISTINCT surl FROM table WHERE text like '%something%' then i do SELECT text,url FROM table WHERE 1 AND `surl`= '$surl' AND text like '%something%' LIMIT 5 i was wondering if its possible to join those 2 together

01-25-2004, 11:40 AM	#4
TDJ Confirmed User Join Date: Nov 2003 Location: Ontario, CA Posts: 146	You could if you were using postgres or a brand new beta mysql version, then you can use subselects: then i do SELECT text,url FROM table WHERE 1 AND surl IN (SELECT DISTINCT surl FROM table WHERE text like '%something%') AND text like '%something%' LIMIT 5 Sounds like your table structure is poorly setup, else it should just be a matter of: SELECT a.whatever FROM tableA a, tableB b WHERE b.value='$blah' and a.id=b.id That might be too technical an explanation for you, sorry if so... Otherwise, using a 'stable' mysql release.. no.. no sub-selects.

01-25-2004, 11:43 AM	#5
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	storing things in 2 different tables in not tha answer i need maximum 5 results from the same domain....

01-25-2004, 12:39 PM	#7
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	limit will give only 5 overall results

01-25-2004, 12:44 PM	#8
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	SELECT text,url FROM table WHERE text LIKE '%something%' GROUP BY surl LIMIT 5 group by sulr make it same as SELECT distinc surl, text,url FROM table WHERE text LIKE '%something%' LIMIT 5

01-25-2004, 02:33 PM	#10
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	can u give me an example how could i do this using Postgres

01-25-2004, 06:29 PM	#13
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	testing it in phpMyAdmin ,the script is in php

01-25-2004, 11:39 PM	#14
KobyBoy Confirmed User Join Date: Jul 2003 Location: Purgatory Posts: 201	I understand what you are talking about. What you want is called a Sub Query. Yes, you can do it with MySQL. Here's a little tutorial I've found: http://www.onlamp.com/pub/a/onlamp/2.../aboutSQL.html

01-25-2004, 11:40 PM	#15
KobyBoy Confirmed User Join Date: Jul 2003 Location: Purgatory Posts: 201	Here are two more links for you: http://www.devshed.com/c/a/MySQL/Usi...-MySQL-part-1/ and http://www.devshed.com/c/a/MySQL/Usi...-MySQL-part-2/

01-26-2004, 12:56 PM	#16
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	i could get what i was trying to with subquerries

01-26-2004, 01:44 PM	#17
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	I have another example of what i need lets say a company has a list or all sales people and state their working in and i want to get the top 5 salesman from each state

01-26-2004, 02:03 PM	#19
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	one column has dublicated info and if i bring it out to a separete table it still won't make the job easier if u think u can do it with 2 tables write a querry...

01-27-2004, 08:11 AM	#20
Cyborg69 So Fucking Banned Join Date: Apr 2002 Location: Canada T.O. Posts: 932	bump

01-27-2004, 04:59 PM	#21
cube³ Registered User Join Date: Dec 2003 Posts: 14	. Pencils down, kids. select t1.url, t1.txt, t2.txt from tbl t1 inner join txt t2 on t1.url=t2.url where t1.txt like '%bl%' group by t1.url, t2.txt having t2.txt like '%bl%' limit 5; Don't you big players use Oracle? Come on... for the cost of just one of your Bentley's. -s __________________ ±ß³ - (5327381)² Last edited by cube³; 01-27-2004 at 05:08 PM..