Possible to join in a count(*)
Hi! Using mysql version 4.0.x, and would like to know if it is possible to join in a count(*) from a related table which count the number of corresponding posts in this per post in the first table? A little like: table1: +-+--+ | id | name | +-+--+ | 04002 | victor | +-+--+ | 04003 | pierre | +-+--+ table2 +-+--+ | id | relid | +-+--+ | 1| 04002 | +-+--+ | 2| 04002 | +-+--+ | 3| 04002 | +-+--+ | 4| 04003 | +-+--+ The result I want is: +-+--+ | id | count(*) | +-+--+ | 04002 | 3 | +-+--+ | 04003 | 1 | +-+--+ Is this possible? Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count all rows if limit by?
Thanks a lot, Jigal and Egor - just what i searched for! Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count all rows if limit by?
Hi all! Is there a simple way to get the total number of rows a result _should_ have had if no where-clause where present? Without doing a second query? Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query: count(distinct field1 max(fieldn)) where?
I need some help with a complex query of mine. The query in question looks like this: SELECT DISTINCT film_film.filmid, titel, pdf, termin, aar, film_serier.serieid, serienamn, screener, har_affisch, har_bilder, max( datum ) FROM ( ( ( `film_film` LEFT JOIN film_rel_regissoerer ON film_film.filmid = film_rel_regissoerer.filmid_relid ) LEFT JOIN film_regissoerer ON film_rel_regissoerer.regissoerid_relid = film_regissoerer.regissoerid ) LEFT JOIN film_visningar ON film_film.filmid = film_visningar.filmid ) LEFT JOIN film_serier ON film_visningar.serieid = film_serier.serieid GROUP BY titel Earlier, before I added the max(datum) to the query, i could get the number of rows by doing: --- SQL --- SELECT count( DISTINCT film_film.filmid, titel, IF ( aar IS NULL , '', aar ), IF ( termin IS NULL , '', termin ) ) AS antal FROM ( ( ( `film_film` LEFT JOIN film_rel_regissoerer ON film_film.filmid = film_rel_regissoerer.filmid_relid ) LEFT JOIN film_regissoerer ON film_rel_regissoerer.regissoerid_relid = film_regissoerer.regissoerid ) LEFT JOIN film_visningar ON film_film.filmid = film_visningar.filmid ) LEFT JOIN film_serier ON film_visningar.serieid = film_serier.serieidLIMIT 0 , 30 The if-clauses where added because count returned wrong number of rows when there where NULL-values in the result What I need to do is to count the number of rows in the first query. But I can't figure how. Adding a max(datum) to the count-list simply wont do it; reports sql-errors Extremely thankful for some feedback. Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group_concat() alternative for mysql 4.1?
Hi all! I'm wondering - are there any alternative to the function group_concat() for mysql servers prior to 4.1? The reason for asking is that I'm building a library database, where each book can have n numbers of authors, where n = 0. In listings I want to get all the authors of one book as a comma delimited (or something) string in one field... Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alternate syntax for UNION?
2003-10-26 kl. 16.56 skrev Victor Spng Arthursson: Desperately need some way to get around the fact that UNION is not possible to use on mysql prior to version 4 Was just about to launch a site when it showed up that the hosting provider was running version 3. Help! Come on now - don't make me change to postgreSQL ;) /.v -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alternate syntax for UNION?
Desperately need some way to get around the fact that UNION is not possible to use on mysql prior to version 4 Was just about to launch a site when it showed up that the hosting provider was running version 3. Help! Regards, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union the rows from select 1 not in select 2?
Need to find out which rows from select 1 are not present in select 2, but is it possible to make som kind of union that only returns the overlapping rows from the two selects? Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
get rows not present using join _AND_ where clause
I need to find out, using a join, which corresponding rows in a union that is missing. But it is among a certain subselect I want to do this, that is, among the rows where the column lang = 'uk' for example. The two tables looks like following: +---++---+ | id | betegnelse | enhed | +---++---+ | 00046 | 838718001064311911 | 5 | | 00120 | 641725001064311948 | 5 | | 01310 | 532898001064317190 | 5 | | 01320 | 535436001064317190 | 5 | | 01330 | 537895001064317190 | 5 | +---++---+ den andra ser ut som +--++--+--+ | id | relid | lang | text | +--++--+--+ | 5513 | 838718001064311911 | dk | m | | 5514 | 838718001064311911 | de | m | | 5515 | 838718001064311911 | uk | m | | 5517 | 641725001064311948 | dk | Stk. | | 5518 | 641725001064311948 | de | Stk. | +--++--+--+ As you can see, the second one holds the language-strings for the first table. But as you also can see I'm missing the last row in table two which should read: +--++--+--+ | id | relid | lang | text | +--++--+--+ | 5519 | 641725001064311948 | uk | Stk. | +--++--+--+ What I would like to do is to list all rows, with null values, where lang != 'dk' or 'de', for example, or rather where lang = 'uk' to find out which that are missing. (What I'm gonna do then is to list the missing texts for translation under a translation page in the admin-area. But thats at a later point - now I just have to select them) Best regards and lots of thanks in advance! Sincerely Victor // Malmoe and Copenhagen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't import sql using databasewhatever.sql
Hi! I'm having a strange problem - I can't read data using the mysql -u root -p databasepath/to/whatever.sql Not getting any error message, but some kind of introduction text flashes by reading: [powerbook:~] victor% /usr/local/mysql/bin/mysql -u root -p *** databas /path/to/tabell.sql /usr/local/mysql/bin/mysql Ver 12.21 Distrib 4.0.15, for apple-darwin6.4 (powerpc) Copyright (C) 2002 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: /usr/local/mysql/bin/mysql [OPTIONS] [database] -?, --help Display this help and exit. --auto-rehash Enable automatic rehashing. One doesn't need to use [... clip ...] max_allowed_packet 16777216 net_buffer_length 16384 select_limit 1000 max_join_size 100 Anyone knowing what to do? It's pretty urgent cause I'm having this big database for a project that is to big to import using phpmyadmin - causes the browser to time out... Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to specify --local-infile[=1]?
The manual tells that the option LOAD LOCAL INFIL can be specified at the command line client by setting the --local-infile[=1] -flag. How do I do that? Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to join from a union-result
Hi! I would like to perform a join on two tables, where one table is a UNION. This is because I have two different tables with products which looks the same and can be UNION:ed without problems So I'ld like to perform something like Select * from table left join [the union] on table.id = [the union].relid Thanks in advance, regards Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random result and split over several pages
Hi! I was wondering: is there any way to make mysql remember the last random or to return the limit in exactly the same order? I want to achieve a random on a result on which I use Limit in the sql. Greetings Victor -- I wish Bill Gates the best, I really do. I just think he and Microsoft are a bit narrow. He´d be a broader guy if he had dropped acid once or gone off to an ashram when he was younger. - Steve Jobs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this possible? Group related results to one field
Hi! I'm having this main table with references to files in it: files +-+-+ | id | filename | +-+-+ |168 |v008-12.jpg| +-+-+ Then I have this table to relate one file to one or several categories: relatedtable +-++--+ |id | fromid | toid | +-++--+ |4| 1 | 2 | | 257 | 2 | 2 | +-++--+ Which lies in this table for categories: categories ++--+ | id | categoryname | stickword ++--+ | 5 | demonstrations | | 6 |people | ++--+ The following sql: SELECT DISTINCT files.id, files.filename, categories.categoryname FROM files LEFT JOIN relatedtable ON files.id = relateratabell.fromid LEFT JOIN kategorier ON relatedtable.toid = categories.id WHERE (relatedtable.fromid IS NULL OR relatedtable.fromid IS NOT NULL) AND files.stickword LIKE '%basta%' //for example ORDER BY filename; Gives the following result: +---+---+--+ | id | filename | categoryname| +---+---+--+ | 166| v007-86.jpg | demonstrations | | 166| v007-86.jpg | people | | 167| v008-03.jpg | demonstrations | | 167| v008-03.jpg | people | +---+---+--+ This is what I expected it to, but I'ld rather get a result as this one: +---+---+--+ | id | filename | categoryname| +---+---+--+ | 166| v007-86.jpg | demonstrations, people| | 167| v008-03.jpg | demonstrations, people| +---+---+--+ Where the categories have been collected together into the same field, so that I don't get multiple rows for the same file Is this possible to achieve? Many many thanks to the one who can give me some input! Sincerely Victor -- Att kriga fr fred r som att knulla fr att bli oskuld - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
related tables result collected in one field
Hi! I'm having this main table with references to files in it: kod: +-+-+ | id | filnamn | +-+-+ |168 |v008-12.jpg| +-+-+ Then I have this table to relate one file to one or several categories: +-++--+ |id | fromid | toid | +-++--+ |4| 1 | 2 | | 257 | 2 | 2 | +-++--+ Which lies in this table for categories: ++--+ | id | kategorinamn | ++--+ | 5 | demonstrations | | 6 |people | ++--+ The following sql: SELECT DISTINCT bilder.id, bilder.filnamn, kategorier.kategorinamn FROM bilder LEFT JOIN relateratabell ON bilder.id = relateratabell.fromid LEFT JOIN kategorier ON relateratabell.toid = kategorier.id WHERE (relateratabell.fromid IS NULL OR relateratabell.fromid IS NOT NULL) AND bilder.stickord LIKE '%basta%' //for example ORDER BY filnamn; Gives the following result: +---+---+--+ | id | filnamn | kategorinamn| +---+---+--+ | 166| v007-86.jpg | demonstrations | | 166| v007-86.jpg | people | | 167| v008-03.jpg | demonstrations | | 167| v008-03.jpg | people | +---+---+--+ This is what I expected it to, but I'ld rather get a result as this one: +---+---+--+ | id | filnamn | kategorinamn| +---+---+--+ | 166| v007-86.jpg | demonstrations, people| | 167| v008-03.jpg | demonstrations, people| +---+---+--+ Where the categories have been collected together into the same field, so that I don't get multiple rows for the same file Is this possible to achieve? Many many thanks to the one who can give me some input! Sincerely Victor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select data that has no related categories...
Tells a great deal about how confused I am... I've a database with three tables, one with image data, one with categories and one table which helps me to have several categories per image... The following SQL gives me _all_ the images that has at least one category associated: SELECT images.filename, images.path, images.desc, categories.name FROM images, relate, categories WHERE public = 1 AND relate.fromid = images.id AND relate.toid = categories.id But what I need help with is getting all the images that has no categorie associated with them in the table relate... Someone, please... Sincerely, Victor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Implement a menu structure using an relative database
Hi! I'm currently working on a menu system for web pages, but I've run into some problems... What I'ld like to create is a menu which can host an infinite number of submenus. Further more, every menu should also be able to link to a specific file, though not always used. I would like to be able to get the entire menu in one sql-line, that is, I would like to be able to get the following in one line: - menu1 - menu2 - menu1 - menu2 - menu1 - menu2 * some text * some more text - menu3 - menu3 - menu3 I'm doing my scripting in PHP, but I want as much as possible of the menu structure to be only mySQL-specific. But having to use some loops to be able to create the sql-line is no problem. I desperately need help on this one, I've put down much energy and time to solve it but... Sincerely Victor Spång Arthursson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Easiest way to create a duplicate of a db?
For example if I want a copy of products named products1? Sincerely Victor query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Easiest way to create a duplicate of a db?
For example if I want a copy of products named products1? Sincerely Victor query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mac OS X Server 10.0.4 and startup
Hi! I build mine successfully using the instructions at http://www.devshed.com/Server_Side/Administration/BuildingOnOSX/. That one including the startup item wich is edscribed there... Sincerely, Victor -- database,sql,query,table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do I restore a database backed up using mysqldump?
Hi! Mysqldump creates the files brand.txt brand.sql etc. How do i restore the database in a simple way..? I've looked in the manual but couldn't find out... Sincerely Victor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP! Uninstall mySQL (Mac OS X (UNIX))
How do I do that? My installation of mySQL doesn't work, so I have to do it... Sincerely Victor Spång Arthursson [EMAIL PROTECTED] | V i c t o r | +45 35358799 / +46(0)733 204002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
My mySQL doesn't start...
Hi! I installed mySQL on my Mac OS X last week, but I never really tried it, though I know it was possible to start. But after having reinstalled Apache and PHP, it's not possible to start anymore... What happens when I type mysql in the terminal is: [localhost:~] victor% mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) [localhost:~] victor% Is it anyone who can tell me how to fix this..? Thankful for answers, Victor Spång Arthursson, sweden - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
