[MySQL SUBSELETCS or something equivalent...]

A while ago somebody posted a question about how to return values from one table, but with some row excluded based on the precense in another table.

 

Here\'s the original post:

 

I have two tables that I need to get information from.

 

The AllStores table has a field called Stores with data in it like:

-Store 1

-Store 2

-Store 3

-etc. (unique store names)

The AllDepartments table has two fields called Store and Department with rows like:

Store                  Department

Store 1                Bakery

Store 1                Produce

Store 2                Bakery

Store 2                Meat

Store 3                Bakery

Store 3                Meat

Notice that Store 1 has a Produce dept. and no other store does.

 

What I need the query to do is:

Find all stores that don\'t have a Produce department in them.

Which, in this case would be Store 2 and Store 3.

 

The solution in one mysql statement is:

 

The basic trick is to duplicate one of the tables in order to get rid of the record having the value you want to exclude...

 

SELECT DISTINCT Stores FROM AllStores A, AllDepartments D1, AllDepartments D2

WHERE A.Stores = D1.Store

AND

A.Stores <> D2.Store

AND D2.Deparment = \'Produce\'

 

This will return all stores from A where the store does NOT have a Department called \'Produce\' .

 

(If people think this was easy and logical, then ignore this post).

 

Greetings,

P., denmark

[Problem with mysql YEAR_MONTH]

It exists but your syntax is wrong.

 

mysql> select extract(year_month FROM curdate());

+------------------------------------+

| extract(year_month FROM curdate()) |

+------------------------------------+

|                             200301 |

+------------------------------------+

1 row in set (0.00 sec)



mysql> 

mysql> 

 

P., denmark

[mySQL JOIN query....I think]

SELECT DISTINCT Stores FROM AllStores 

WHERE Stores NOT IN

(SELECT Store FROM AllDepartments WHERE Department=\'Produce\')

 

Inner select gives you a list of stores with a produce department

Outer select gives you a list of all stores except the one present in the inner select...

 

P., denmark

[Help me query please]

A few typos, try:

 

SELECT luser.userid,luser.firstname,luser.lastname 

FROM luser

WHERE (luser.Pos = \'Teacher\') 

AND 

(

  luser.userid NOT IN

   (

   SELECT sectionuser.userid 

   FROM sectionuser 

   WHERE 

   (sectionuser.courseid = \'204105\') 

   AND

   (sectionuser.SectionID = \'1\') 

   AND 

   (sectionuser.Semester = \'2\') 

   )

)

ORDER BY luser.UserID 

 

Try it,

P., denmark

 

select luser.userid,luser.firstname,luser.lastname from luser  

where luser.Pos = \'Teacher\'and luser.userid not in  

(select sectionuser.userid from sectionuser  

where sectionuser.courseid = \'204105\'and sectionuser.SectionID = \'1\' and sectionuser.Semester = \'2\')  

ORDER BY luser.UserID  

 

this query i can use in other database server but i can\'t use in MySQL version 4.0.2-alpha-nt  

 

please!!! help me to convert my query can use in MySQL ....thank

[Search all fields?]

Also sent by mail to original questioner...

 

Ok, just came up with an idea...

 

You MAY be able to combine all fields in the search using

 

select * from table where

concat(field1, field2, field3,....field20) like %$searchterm%;

 

(I\'m not sure if I remeber correctly with concat but anyway there is a mysql order for combining fields. I\'m not sure about speed though - it might be quite slow...

 

CORRECTION: I just checked my own code... It works. (This is for a adress database and dive logging - danish field names, sorry about the confusion).

 

$sql->Query("SELECT 

p.Prs_PersonID, 

CONCAT(p.Prs_Fornavn,\' \',p.Prs_Efternavn), 

p.Prs_Status, 

p.Prs_Email, 

p.Prs_WWW,

COUNT(tbl_Noter.Note_NoteID),

tbl_Dykkererfaring.Dykef_LoggedeDyk



FROM 

(tbl_Person AS p LEFT JOIN tbl_Noter ON p.Prs_PersonID = tbl_Noter.Note_PersonID) 

LEFT JOIN tbl_Dykkererfaring ON p.Prs_PersonID = tbl_Dykkererfaring.Dykef_PersonID 



WHERE 

(// HERE IT COMES!!!

((concat(p.Prs_Fornavn,\' \',p.Prs_Efternavn)) like \'%$navn%\')

AND

(p.Prs_Status like \'$status\')

)

GROUP BY p.Prs_PersonID 

ORDER BY $sortby

");

 

P.

[Gaining access to a folder]

Use an alias!

 

My document root is a completely different place!

 

I hope this is enough,

P., denmark

 

#

# Aliases: Add here as many aliases as you need (with no limit). The format is  

# Alias fakename realname

#

<IfModule mod_alias.c>

 

   #

   # Note that if you include a trailing / on fakename then the server will

   # require it to be present in the URL.  So \\\"/icons\\\" isn\'t aliased in this

   # example, only \\\"/icons/\\\".  If the fakename is slash-terminated, then the  

   # realname must also be slash terminated, and if the fakename omits the  

   # trailing slash, the realname must also omit it.

   #

   Alias /icons/ \\\"/home/arginine/genchip/apacheServer/icons/\\\"

 

   <Directory \\\"/home/arginine/genchip/apacheServer/icons\\\">

       Options Indexes MultiViews

       AllowOverride None

       Order allow,deny

       Allow from all

   </Directory>

 

   # This Alias will project the on-line documentation tree under /manual/

   # even if you change the DocumentRoot. Comment it if you don\'t want to  

   # provide access to the on-line documentation.

   #

   Alias /manual/ \\\"/home/arginine/genchip/apacheServer/htdocs/manual/\\\"

 

   <Directory \\\"/home/arginine/genchip/apacheServer/htdocs/manual\\\">

       Options Indexes FollowSymlinks MultiViews

       AllowOverride None

       Order allow,deny

       Allow from all

   </Directory>

 

I have a website currently running in X:Apachehtdocs, and I\'m trying to have a link to my mp3 folder for my friends to be able to browse.  The folder is X:Music, and I\'ve tried a couple things but haven\'t gotten it to grant access to that folder, always getting \\\"Forbidden\\\".  This is what I\'ve put in my apache conf file:

 

<Directory "X:/Music">

   AllowOverride None

   Options None

   Order allow,deny

   Allow from all

</Directory>

 

and also

 

ScriptAlias /music/ "X:/Music"

 

I\'ve tried changing it from <Directory \\\"X:/Music\\\"> to \\\"/music\\\", and also a couple other things, but I have a feeling thats not the problem.  Any help?[/code]

[Random row in mySQL]

Just a quickie:

 

If using pure sql:

 

select * from quotes order by rand(time_to_sec(curtime())) limit 1

 

This would use the number of seconds as seed...

 

P., denmark

[Random row in mySQL]

Use a seed value that differs all the time:

 

select * from quotes order by rand($seed) limit 1

 

Use some reandom integer in $seed (time or something).

 

The reason for the problem - probably because you\'re creating a shiny new connection the database each time the page is loaded and the \"not really random\" function in sql returns the same values when a new connection has been initialized (?)....

 

I think the seed is the solution - BUT remeber that the same seed returns the same random value! It would actually be a lot easier to pick a random row using the following (assuming sql and php or something similar):

 

$seed = random(10);

select * from quotes limit by $seed, 1;// picks 1 row from $seed

 

P., denmark

 

ok, i got this code which is supposed to select a random row in mysql.

 

select * from quotes order by rand() limit 1

 

i have 10 rows in the table, and i seem to get the same row each time!  i don\'t understand.  maybe after reloading about 50 times i\'ll get one different row, then it will go back to the previous one for another 30 times.  i have no idea why it\'s really not randomizing these.

 

any ideas?

[Order query results by columns]

You code currently gives you

 

i | i+1

i+2 | i+3

...

...

n-1 | n

 

or something like that... Right ?

 

You want:

 

i | k + 1

i+1 | k +2

i+2 | k + 3

k

 

So if you have a total of 53 rows, k should be 27, resulting in 27 rows in the table, the last row consisting of 1 output only.

 

Calculate $n as you do. Make $k = ceiling($n/2)

Now you may use

 

Pseudocode:

[php:1:f262e15009]

$n = number of rows in query result;

$k = number of rows/2 rounded up //ceiling will be the last result in left coloumn.

$i = 0; // counter for output row

While $i < $k; $i++;{

//Left column:

$nom=mysql_result($result,$i,\"nom\");

//Right coloumn

$nom=mysql_result($result,($i+$k),\"nom\");

}

[/php:1:f262e15009]

This should work. You\'ll have to add some checking whether the number of rows is even or not (in the sql result) and adjust the output accordingly.

 

I hope this helps,

P., denmark

[Search all fields?]

The answer is NO. There is no smart way to search all coloumns for a given string. But why would you do that?

 

(If you really want to, then do as you suggest in your second line.)

 

You COULD combine all coloumns into one large string and put that into a new coloumn - but that is not nice at all.

 

The idea behind having a database disappears if you start collapsing information into one huge pile and then searches it like that.

 

I\'m curious - what kind of data would you like to search in such a manner ?

 

P., denmark

 

 

I\'m new and i\'m trying to write a query that searches all columns  for a certain variable without writing out the name of every column in the table. Is there a command like

SELECT * FROM mytable WHERE * LIKE\'%...%\'   ?  That would search the entire table for a value?

 

  I dont want to write SELECT * from mytable WHERE field1 like \'%somevar%\' OR field2  like \'%somevar%\' OR ......

 

Or even a way of indexing the whole database in one column then i could search that column in my query?

How do i go about this. Thanks[/i][/u]