mysql gurus

[Cyborg69]

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]

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]

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]

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]

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

[icedemon]

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]

limit will give only 5 overall results

[Cyborg69]

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]

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]

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

[icedemon]

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]

are you doing things from command line or using phpMyAdmin?

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

[Cyborg69]

testing it in phpMyAdmin ,the script is in php

[KobyBoy]

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]

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]

i could get what i was trying to with subquerries

[Cyborg69]

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]

two tables would be the right way to do it.

Seems like you have duplicating info in that table.

[Cyborg69]

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]

bump

[cube³]

.

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