Can you combine two SELECTs which join on the same field (non trivial)


I have a table (choices) which includes two fields (first_choice,second_choise) which contain ID numbers of items, and an identifier for the row (choiceid)

I have another table (items) which contains fields including the item ID (itemid), name of the item (name) and others.

1) Is it possible to create a MYSQL statement that would enable me to create a table containing the names of the choices

If I was doing this in PHP I would code two seperate SELECTs...

SELECT choiceid,name FROM choices,items WHERE choices.firstchoice=items.itemid ORDER BY choiceid

SELECT choiceid,name FROM choices,items WHERE choices.secondchoice=items.itemid ORDER BY choiceid

... and then write a function to combine the two results by matching IDs.

2) Is it possible for the statement to work when the second choice has a value of 0 (and so will not have a value in the items table)

My guess is that this may be possible with some form of JOIN statement.

3) I could do with the results being ordered in alphabetical order by first choice, and then second choice.

This may be impossible, but I can hope.
Posted On: Thursday 11th of October 2012 11:57:51 PM Total Views:  153
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Multiple results when I only want one - can NOT figure this out

I'm having trouble at work with this and I just can not figure it out. I hope I can explain it correctly. Two tables table1 and table2 I need to trim up column C from table one with LEFT( (TRIM(LEADING '00' FROM C) ) , 7) to get 7 digits without the leading 00 I need to use this to match it to table2.D Here is where I'm stuck If there is a match it obviously returns a match BUT if there isn't a match I need ot try something else OR concat( LEFT( (TRIM(LEADING '00' FROM C) ) , 6),0) = table2.D cut off the last of the 7 digits and replace it with Zero Then return a result. My problem..... I get two results back not one if the 7 digits work. I get one result back if the seven digits didn't work. I have tried Select distinct and that didn't work either. I have no experience with stored procedures so that is out. I just can't figure this out. Smart people can you help to get my boss off my back My code: use DB; select table1.A,table1.B,table1.C, LEFT( (TRIM(LEADING '00' FROM C) ) , 7) AS Cresults, table2.A, table2.B, table2.C, table2.D from table1,table2 where LEFT( (TRIM(LEADING '00' FROM C) ) , 7) = table2.D OR concat( LEFT( (TRIM(LEADING '00' FROM C) ) , 6),0) = table2.D
VIEWS ON THIS POST

102

Posted on:

Friday 12th October 2012
View Replies!

MySQL syntax error but can't see how

getting an annoying mySQL syntax error when I run a query to a db, but the syntax looks fine as far as I can tell: Error is: ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index = '2'' at line 1 The PHP code is:
VIEWS ON THIS POST

274

Posted on:

Friday 12th October 2012
View Replies!

How can I combine my two queries?

I am trying to SELECT all the scheduled rules of all the suupliers but cant figure out how to join my two queries: 1.) THIS QUERY SELECTS ALL THE SCHEDULE OF ALL THE SUPPLIERS. THE FIELD condition_value is the suppliers unique code number: "select rules.rule_id, name, start_date, end_date, condition_value from rules, rules_schedule where rules.rule_id = rules_schedule.rule_id" sample output: rule_id| name | start_date | end_date | condition_value | 1 | first rule | 2009-01-01 | 2009-02-02 | 1234 | 2.)THIS QUERY SELECTS THE SUPPLIERS PP-ID- JUST SOME ID AND "NAME" AND "ASI" - WHICH IS THE SUPPLIERS UNIQUE CODE NUMBER OR SAME AS THE condition_value in query#1: "SELECT pp_id, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_varchar USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=215 AND approved=1) as supplier_name, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_int USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=127 AND approved=1) as asi FROM suppliers" sample output: pp_id | supplier_name | asi | 00-01 | ABC CO. | 1234 |
VIEWS ON THIS POST

149

Posted on:

Friday 12th October 2012
View Replies!

How can I have an empty date field instead of 0000:00:00?

Sorry if this has been asked before, I have searched first. How can I have an empty default date field and not have it appear as 0000-00-00 In Mysql I have a date field with default set to null. As a result on my web form it appears as 0000-00-00. If I have the date field as a text type, using default null allows me to have a blank entry. I'm hoping it's a setting in mysql. If not, should I: 1. use text type for the field. 2. use date type field, but edit appearance using javascript/php Thank you.
VIEWS ON THIS POST

105

Posted on:

Friday 12th October 2012
View Replies!

How can I do this

I have a database where people add their own entries... each entry is esentially a link to a page, the problem is the first people to add them all get viewed heaps of times then all the others don't get viewed at all... I was thinking of changing it to stat every month they have to go to an "update" page and re-enter their link. then it can be added to a new table so that the sooner they "update" their link the higher in the list it will be in the next month, I also want to copy all the entries that were not "updated" to the end of the new table. Is there a better way to do it than my way (a new table every month) I have a spare column in my database, I was thinking of just placing a number in there to mark which month they updated in, then sort that that column followed by the "ID" column, that way during the month the order can change, but then there's a chance of people seeing the same link twice (slim chance I guess but it could happen) BTW if you're wondering what my database looks like... here are the columns ID: auto increasing integer Link Unused column set as integer reports (number of times the link has been reported as a deal/invalid link) I type too much... don't I =[ and I probably make no sense either... well it is 4:30am and i have work in about 7 hours... Oh, and I don't mind if the list doesn't change until the next month. that was my original plan anyway.
VIEWS ON THIS POST

111

Posted on:

Friday 12th October 2012
View Replies!

Where i can start learn making crosstab Query

. where i can start learn making crosstab query (beginner to expert level)
VIEWS ON THIS POST

103

Posted on:

Friday 12th October 2012
View Replies!

How can i dynamically archive tables to tables with year/month postfixes, by timestamp

, I have several tables in my MySQL database which i want to archive. Now i want to archive the tables to other tables with a different name. For instance, a table `messages` should be archived to `messages_2008_01` and `messages_2008_02` etc... In the `messages` table there is a `timestamp` column, from this column i want to determine into which table the record should be placed. For example, i have 10 records, where 6 records are from month 01 and the other 4 records are from month 02. No i want those 6 records to be placed in `messages_2008_01` and the other 4 into `messages_2008_02`. And if possible i want to do this within one query/sql-procedure. This way i don't have to parse the result within PHP, and insert them manually with x amount of inserts. thx in advance.
VIEWS ON THIS POST

112

Posted on:

Friday 12th October 2012
View Replies!

How can I merge a number or Rows from the same Table?

HI Guys, I am setting up a daily report of Automatic updates on our client Database. The problem is that there are 5 separate updates, and 35 companies. The status of each update is queried with a variant of the query below, where #####=stock, contracts, categories, debtors, inventory. PHP Code: USE\t\t\t\t\t\t\t\tsitedb INSERTINTO\t\t\t\tupdate_log(project_pkey,project_dbname,Last_#####_Update,test_date,#####_Update_Status) SELECT\t\t\t\t\t\tproject_pkey,project_dbname,last_#####,date(now()),if(date(last_#####)
VIEWS ON THIS POST

97

Posted on:

Friday 12th October 2012
View Replies!

Page 2 - How to store american DATE formate( like 27-02-2007) in mysql table???????????

stefan, i will have to respectfully disagree mysql also supports YYYYMMDD and YYYY$MM$DD, so there goes the accuracy of your statement it also supports Tuesday February 27th, 2007 (but only on output)
VIEWS ON THIS POST

125

Posted on:

Friday 12th October 2012
View Replies!

How can i change default charset for mysql database?

Hi all, before I begin here are some info about current environment... +++++++++++++++++++++++ Linux VPS with root/shell access MySQL4 version 4.1.9-standard Mod Php 4.4.2 phpMyAdmin 2.7.0 pl2 +++++++++++++++++++++++ I'm very new to VPS(Virtual Private Server) for start, Just install above applications, it took me while but managed to change php.ini, now i would like to change the mysql DEFAULT CHARSET of your databases from utf8 to ujis. I have seen something like this below but i dont know to do it Can anyone help me # ./configure --with-charset=ujis -with-extra-charsets=all --with-mysqld-user=mysql mysql --default-character-set=ujis, Example | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results | ujis | | character_set_server | ujis |
VIEWS ON THIS POST

115

Posted on:

Friday 12th October 2012
View Replies!

How can i create dsn on linux machine

hello, i am raghvendra. i am working on linux. i am using a php4 with apache 2 on linux machine. i want to create a dsn on linux machine. but i donot know how can i do this. my database is on remote windows machine. could any body help me thanx & regards raghvendra khandal
VIEWS ON THIS POST

102

Posted on:

Friday 12th October 2012
View Replies!

Mysql scanning all rows even with index

when I do something like this. Code: select field from table order by field desc limit 5 mysql scans all the rows in the table even though the field i'm ordering by is indexed...Is this right Here is the actual query. http://pastebin.com/506898
VIEWS ON THIS POST

105

Posted on:

Friday 12th October 2012
View Replies!

How can run queries in batch mode?

all, I want to execute the queries wriiten in a .sql file in a single attempt using the batch process of mysql.Since I don't have a good grip over the database systems, I am confused how should I go for it. Please anybody suggest something. Thanx for all of you in advance.
VIEWS ON THIS POST

122

Posted on:

Friday 12th October 2012
View Replies!

Mac & CocoaMySQL: How can I create InnoDB Tables?

...it seems to me that I can only create MyISAM tables... is there any way to tell CocoaMySQL to create InnoDB tables
VIEWS ON THIS POST

107

Posted on:

Friday 12th October 2012
View Replies!

using left join - can't retrieve data from second table

I am trying to retrieve details for an audio file from two different tables. The UPLOADS tables contains most of the information about the file, including whether it is linked to a release (CD, DVD etc). From the RELEASES table I need to retrieve the appropriate cover art for the release. Some audio files are not linked to a release, therefore I will sometimes need to retrieve data from the UPLOADS table when there is no matching release coverart in the RELEASES table. I have tried using a left join to achieve this and am able to retrieve all the relevant info from the UPLOADS table, but the path for the cover art is not being retrieved. Is my syntax faulty, am I using the right method of joining tables to achieve what I need Can anyone help me (I've included a bit of my PHP code, in case someone happens to spot an error outside of my query. Hope that's okay.) PHP Code: $id=$_GET['id']; if($id) \t\t{ \t\t \t\t$result=mysql_query("SELECT \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tuploads.release_id,uploads.upload_type,uploads.upload_timestamp,uploads.upload_name,uploads.upload_format,uploads.upload_path,uploads.upload_comment,release.release_id,release.release_coverart \t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tuploadsLEFTJOINrelease \t\t\t\t\t\t\t\t\t\t\t\t\t\tON \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tuploads.release_id=release.release_id \t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tuploads.upload_id='$id'") \t\t\t\t\t\t\t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\t\t\t\t\t\t\torprint("Cannotfindfile."); \t\t \t\twhile($row=mysql_fetch_array($result)) \t\t\t\t{ \t\t\t\t\t\t\t\t\t\t \t\t\t\t\t\t \t\t\t\t\t\t$id=$row["upload_id"]; \t\t\t\t\t\t$type=$row["upload_type"]; \t\t\t\t\t\t$name=$row["upload_name"]; \t\t\t\t\t\t$format=$row["upload_format"]; \t\t\t\t\t\t$path=$row["upload_path"]; \t\t\t\t\t\t$comment=$row["upload_comment"]; \t\t\t\t\t\t$art_path=$row["release_coverart"]; \t\t\t\t\t\t$date\t=date("d.m.y",$row["upload_timestamp"]);
VIEWS ON THIS POST

125

Posted on:

Friday 12th October 2012
View Replies!

How to Insert if I can't Update

Now, I check for the data to exist. If it does, I UPDATE, otherwise I INSERT. Is there a way to do it better, more efficiently
VIEWS ON THIS POST

102

Posted on:

Friday 12th October 2012
View Replies!

Php mysql query - can you spot the error

Mysql Query via PHP: Im having a dull moment, can anyone please help and try spot the error, thanks: Code: UPDATE TOELEVERANCIERS SET HANDELSNAAM = 'TestDan', WEB_NAME = '', ADRES = 'TestD', POSTCODE = '6167', PLAATS = '', TELEFOON = '', FAX = '', GRATIS_EN_BETAALD = '', WEBSITE = '', LINK = '', AANTAL_MEDEWERKERS_VOOR_DATABASE = '0', AANTAL_MEDEWERKERS_IN_BEELD = '', ACTIVITEIT = 'hey heyh hey', BRANCHE = '', EIGEN_VESTIGINGEN = '', CONTACT = '', EMAIL = '', FUNCTIE = '', HOOFDGROEP = '', HOOFDGROEP_2 = '', KLANTEN = '', REFERENTIES_VAN = '', STARTJAAR_ONDERNEMING = '0', STEPSTONES = '', TOTALE_OMZET = '', VESTIGINGEN_BUITENLAND = '', ZUSTERBEDRIJVEN = '', IS_CLIENT = '2', CLIENT_INFO = '', FORMELE_BEDRIJFSNAAM = '', MOEDERMAADSCHAPIJ = '' WHERE id = 319 MySQL said: Code: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
VIEWS ON THIS POST

117

Posted on:

Friday 12th October 2012
View Replies!

AES_DECRYPT cant seem to show stored values

Ok I have sucessfuly created a page that insterest data using the AES_ENCRYPT. Now I am working on another page that will show the data. I dont through any erros but I dont see the information either. I am using Mysql 5.0 and I use dreamweaver cs4. Here is the code a am using. [code] $query_Recordset1 = "SELECT AES_DECRYPT(password, 'text_key') AS decrypt FROM content"; $Recordset1 = mysql_query($query_Recordset1, $EevokePages) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); > Untitled Document [code] Can anyone help me out here I am stuck
VIEWS ON THIS POST

222

Posted on:

Friday 12th October 2012
View Replies!

Problems with creating stored procedures, can't figure out the syntax

so im trying to make some stored procedures but im still stuck at the first procedure still. SQL Code: Original - SQL Code use fromScratch; DROP PROCEDURE IF EXISTS personsGet; CREATE PROCEDURE personsGet () SELECT [id], [name], [lastname] FROM persons;
VIEWS ON THIS POST

152

Posted on:

Friday 12th October 2012
View Replies!

How many connection a mysql can handle and how can I tell it's overloaded?

Hi. I wonder how many connections a mysql server can normally handle And how can I tell (tools/technique) tell will tell me it's about to get overloaded that I need to do something
VIEWS ON THIS POST

149

Posted on:

Friday 12th October 2012
View Replies!