Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me:
Table 1 - portrait
field 1 - id
field 2 - type (will be the id from a row in business or personal)
Table 2 - business
field 1 - id
field 2 - bizname
Table 3 - personal
field 1 - id
field 2 - firstname
field 3 - lastname
His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account.
If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
View Replies !
Selecting Another Column Based On Max()
I've got a funky query I'm trying to set up. I've got this poll answers database, and each row has a qid that matches to a question. So I can group answers by the question they correspond to. Now I'm trying to select the total votes and the answer with the most votes for each question. The total votes part is working fine, but I'm having trouble with the winning answer part. I've got a MAX(votes) that works well, and finds the answer with the most votes for every question, and returns its votes. What I want to do is select the `answer` column (the one that houses the actual text answer) of the answer with the greatest votes (the winner). Code:
View Replies !
Selecting From One Table Based On Another?
I have a table for products and another table for the type of product it is like this: I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc. but how can I then select a product based on criteria from the types table say I wanted to select all products that are type = "Band" and type != "Fancy" ?
View Replies !
Selecting Based On Matched Column?
Lets say I have table with two columns zip_work and zip_home. I'd like to construct a query looking something like this: SELECT {here is the problem} FROM myTable WHERE zip_home=99999 OR zip_work=99999 GROUP BY zip_home, zip_work LIMIT 100; In response I'd like get two column table with zipcode as the first column and COUNT() of matched users. The idea is to know how many users are working or living in 99999 zipcode.
View Replies !
Selecting Values Based On Column
I couldn'y find solution to this on forum so here it is : sample table : id | name | value 1 | x | y 1 | z | v 2 | x | y i would like to select id where x=y and z=v. so in this case result should be : 1 (2 is not in the result coz it doesnt match x=y). How to create such a query?
View Replies !
Selecting Based On Time Range
I'm writing a series of queries on a transactions table. I'd like to find all transactions from: Today Yesterday Last Week Last Month This Year The table contains a datetime field called txn_timestamp. What is the appropriate code to filter the table for each time window? SELECT transaction_id FROM transactions WHERE <?>
View Replies !
Selecting Based On Relevancy Of Number Ranges
I'm trying to figure out the best (any) way to do a relevancy query based on how closely a group of four numbers in a row match a supplied set of numbers. Basically, in each player's row, I have four game rankings that make up 100 points total. They may, for example, be given 40 for score A, 25 for score B, 20 for C, and 15 for D And, I want to be able to return a result set thats weighted based on the someone who's got a certain set of scores In one scenario, a C of 45 and B of 20 In another scenario, a A of 50, a D of 20, and a B of 5 etc I want to weight the results based on who is closest to the first selection, then the second, then the third, etc... Is this even doable? :0
View Replies !
Selecting Values For This Record Based On Other Records
Is it possible in a select to reference the contents of a column in a different record to the one currently being assessed? Kind of like :- Select id,prev_id,title,flag FROM my_table WHERE flag != "S" AND flag(prev_id)="S"; What I'd like it to do is select records which have flag set "S", but the record referred to in prev_id do not have flag set "S". If it matter, I'm stuck on 4.0.
View Replies !
Selecting Events Based On Various Date Criterias
I'm a little stuck at how to tackle this one efficiently. I'll try to explain concisely... I have a form to search for events. Form fields include Postcode Event name Venue name When (radios) -> today -> tomorrow -> this weekend OR instead of 'When' they can choose a specific date. In the events table there are a various columns including startdate (of type datetime). There is also a column for each day of the week that the event is repeated. For example, if the event is repeated on a monday then the column 'repeat_mo will' have a value of 1. My difficulty lies in creating a query to retrieve all the relevant results. For example, a search could include the following variables from the form: postcode -> YO14 7LX when -> tomorrow So I'd need to go to the events table and retrieve all rows that have a similar postcode, a start date that is tomorrow OR has been flagged as being repeated every wednesday. The postcode bit is fine and getting tomorrow date (with php) is fine. But how do I put it all together to also take into account the repeat day flags?
View Replies !
Updating 2 Fields Based On Search
I'm trying to change 2 database fields based on another. I want to update te fields levelend and level (expiration date and membership level) based on the field "gender". So if the gender field reads "K", I want to change levelend to 2556100861 and level to 38 For some reason I just can't get it quite right.
View Replies !
Sub Query - Aggregate Fields Based On Min N Max
In the Users table below there are duplicate users by email address +---------------------+------------------------------+-----------------+-----------+ | ts | email | field1 | field2 | +---------------------+------------------------------+-----------------+-----------+ | 2009-01-31 06:51:14 | user1@rediffmail.com | 05 | 03 | 2009-01-31 16:07:39 | user2@yahoo.com | 02 | 02 | 2009-01-31 16:15:02 | user2@yahoo.com | 09 | 04 | 2009-01-31 16:16:00 | user2@yahoo.com | 06 | 08 | 2009-01-31 16:19:52 | user2@yahoo.com | 01 | 09 | 2009-01-31 02:04:36 | user3@rediffmail.com | null | 01 | 2009-01-31 02:12:34 | user3@rediffmail.com | 01 | 03 | 2009-01-31 02:20:31 | user3@rediffmail.com | 08 | null +---------------------+-----------------------------+--------------+-----------+ I want to fetch one record per user ‘user1,field1,field2’ For user 1 select field1 where min(ts) select field2 where max(ts) the final output should be user1,05,03 user2, 02, 09 user3, 01,03 (max of ‘field2’ is null so it should pick the field value which matches the next min ‘ts’ val)
View Replies !
Finding Duplicates Based On 2 Fields
I've got a client that is cleaning out their CRM database. They recently imported a load of files but they had the duplicate checking set so that firstname, lastname, and email all had to match in order for the CRM to declare it a dup. Now they want me flag all the duplicates based just on firstname and lastname. But have no idea how to do it when two fields have to match. Would something like this be a step in the right direction? SELECT uid FROM users_tbl HAVING count(firstname,lastname) > 1; I'm still waiting on some information from the client so I'm not sure what version of MySQL he's using.
View Replies !
Selecting Fields
Suppose I have two tables A and B Table A has fields A1, A2, A3, ... A9 Table B has fields B1, B2, B3, ... B9 I want to select all fields in A and only B3 in table B. I dont want to list individual fields for A because they are too many I simply want to use a *. How do I do this? I tried this way but they are not working Select * from A1, B3 from B Select *, B3 from A, B
View Replies !
Selecting Certain Fields
i have a table that is logging timestamps that unique gps devices "report" in: gps1 - '2008-08-22 01:04:00' gps1 - '2008-08-22 01:22:00' gps1 - '2008-08-22 01:36:00' gps2 - '2008-08-22 01:04:00' gps2 - '2008-08-22 01:18:00' gps2 - '2008-08-22 01:29:00' gps3 - '2008-08-22 01:48:00' gps3 - '2008-08-22 02:12:00' gps3 - '2008-08-22 02:24:00' I want to select all columns but only 1 per half hour per gps, so if a particular gps reported twice in the same half hour, i'm ignoring all but the last for that half hour (even half hours 1:00 - 1:30, 1:30 - 2:00...). SELECT gps, time FROM report GROUP BY (something to do with 1 per half hour per gps?) Output would be: gps1 - '2008-08-22 01:22:00' gps1 - '2008-08-22 01:36:00' gps2 - '2008-08-22 01:29:00' gps3 - '2008-08-22 01:48:00' gps3 - '2008-08-22 02:24:00'
View Replies !
Selecting The Minimum Of Seven Fields
I am new to mysql. I need to select the lowest number from seven fields where the value is greater than 0. Can someone tell me how to do this. So far, I have Select RF1, RU1, ItemName FROM TABLE UNION Select RF2, RU2, ItemName FROM TABLE UNION Select RF3, RU3, ItemName FROM TABLE UNION Select RF4, RU4, ItemName FROM TABLE UNION Select RF5, RU5, ItemName FROM TABLE UNION Select RF6, RU6, ItemName FROM TABLE UNION Select RF7, RU7, ItemName FROM TABLE; This will give me one long list with all the rate from (RF#) and Rate UNtil (RU#). Now how do I select the lowest one for each itemname?
View Replies !
Selecting Fields From 3 Tables
i have 3 tables. table 1 - products: [Code, typeCode, supplierCode] table 2 - suppliers: [code, name] table 3 - types: [code, name] how can i select the fields from the 3 tables, meaning where products.typeCpde = types.code, products.supplierCode = suppliers.code
View Replies !
Selecting Two Fields In One Query
I have two member tables that i wish to select the names from. They are identical except table name (memberOne, memberTwo). It was a mistake to divide them to begin with but it seemed logical at the time (long time ago) unfortunately. I have a drop down html <select> list working fine with query from one table. How does the query look to select both my tables? List looks like this. Member ID is not important but identical ID's may appear. Code: <select name="memberlist" class="dropList"> <?php $sql="SELECT Name FROM MemberOne"; $result =mysql_query($sql); while ($data=mysql_fetch_assoc($result)){ ?> <option value ="<?php echo $data['Name'] ?>" ><?php echo $data['Name'] ?></option> <?php } ?> </select>
View Replies !
Change Value In Field Based On Order
I have a field that is an INT and can contain a value of 0 or greater. When the value is 0 it creates a unique situation and users of the site will sometimes change this to 0 on purpose because this value can have your item show up in the search results on the first page. Why, if a person orders the information ASC, it will show up first. I have to allow this field to be 0, long story, but I want to "punish" them if this is so. Based on the type of search (ASC or DESC) as the user can pick. I want those items to always show up last. Currently I am passing a variable that gets set via a form w/ that information, but I don't know how to check in the query how the variable is set. I was thinking CASE but I thought that only looks at a field in the db, not some variable that is being passed. Either way, I hope this is making sense. Here is the query (pretty simple). Code: ......
View Replies !
Adding A Key Field Based On Other Columns
I have a MYSQL db based on an excel sheet There's a membership number field that's mostly empty, there's five different levels of membership from full to part time and so on, each represented by a 'Y' in the relevant field, also a date field with the date that they first joined What I want to do is allocate a unique membership number for each member, a six figure number starting at 100001 upwards for the full members starting with the oldest memberships, 200001 upwards for the part time members, and so on.
View Replies !
Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.....
View Replies !
Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records. I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are. I have found some SELECT COUNT( * ) AS num_entries, title FROM title_table WHERE title >1 GROUP BY title But this doesn't seem to work, although looks like it's on the right sort of lines? I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!
View Replies !
Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using: *********** SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC *********** I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?
View Replies !
Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value. example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates). how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?
View Replies !
Updating A Database Field Based On A String Or Filename
I am using MySQL to store images that I serve up with Coppermine photo gallery. Each image is shown in low resolution format. I also store medium and high resolution versions of these files for download. There is a caption field where I put these links using BBcode. It looks something like this: Code:
View Replies !
Trigger That Fills In A Missing Foreign Key Based On Another Field
To provide an easy insert methods for our client, I'm trying to write a trigger that fills in a missing foreign key based on another field that is inserted. Basically, 'dr_market_id' is inserted and I'm trying to find 'market_id' by selecting the appropriate row in the markets table. area_codes_markets.market_id is foreign key to markets.id delimiter | CREATE TRIGGER area_codes_markets_ins BEFORE INSERT ON area_codes_markets FOR EACH ROW BEGIN DECLARE tmp_market_id INT UNSIGNED; DECLARE tmp_dr_market_id INT UNSIGNED; IF NEW.market_id is null THEN SELECT id INTO tmp_market_id FROM markets WHERE dr_market_id = NEW.dr_market_id ; SET NEW.market_id = tmp_market_id; END IF; END;| delimiter ; The trigger gives no warnings. However when I try to insert a row into area_codes_markets, I get a foreign key constraints error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`area_codes_markets`, CONSTRAINT `area_codes_markets_ibfk_1` FOREIGN KEY (`market_id`) REFERENCES `markets` (`id`)) The table 'markets' is populated with the correct ids. Does anyone have an idea what I'm doing wrong? If not, is there a way to print out the values of tmp_market_id and NEW.market_id in the trigger that are echo-ed to the console?
View Replies !
Selecting Language Depended Fields Through Multiple Table References
For a long time i think i need your instructions dear people on sitepoint. I'm making a sql query for getting user info, but i dont know how to handle this situation: SELECT user.name, user.firstname, user.lastname, ___ as user.city, ___ as user.province FROM user, languagefield, city, province WHERE user.id = %i AND languagefield.language = %i AND city.id = user.city #tricky part AND city.name = languagefield.id AND city.province = province.id AND province.name = languagefield.id So i'm trying to get users first and last names plus city and province names from language tables. Is it possible with one query? How would you make SELECT and WHERE clauses? Im using this db tbl scheme: table : city id (int4) primary key name (int6) ref languagefield.id province (int4) ref province.id table : province id (int4) primary key name (int6) ref languagefield.id table : user id (int4) primary key firstname (varchar32) lastname (varchar32) city (int4) ref city.id table : language id (int3) primary key char2name (varchar2) name (int6) ref languagefield.id table : languagefield id (int6) language (int3) ref language.id value (varchar255) id + language unique ***** I'm also thinking and planning to set more current language depended content to my application, so this is very important part of my design, 'cause it could be reused when dealing with other tables and fields.
View Replies !
Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables, Table A- has the field "id" which is the primary key Table B- has the field "id" which is the primary key Select * from tableA where Table A.id is not in tableB.id How can i form a statement that will accomplish the above.
View Replies !
Selecting From A Table Field Twice
example fields of table particle: id radius i j k Each entry in the connections table has the id of 2 connecting particles example fields of table connections: id1 -> id of the particle id2 -> id of the connecting particle What I want to do is form a query that will list the particle information for each particle and its connection. For example, for each entry in the connections table, I want to list: id1, radius1, id2, radius2 (where 1 would be the particle and 2 would be the connecting particle). I'm working with this query at the moment: SELECT x.id1, x.radius1, y.id2, y.radius2 FROM (SELECT id id1, radius radius1 FROM particles WHERE id=id1) x, (SELECT id id2, radius radius2 FROM particles WHERE id=id2) y;
View Replies !
Selecting Field That Aren't Empty
Im trying to run a query to select everything except blank fields however when i run. SELECT clientnumber, LastName, firstname, workphone, email FROM clients WHERE email=NOT NULL; It pulls back "0 rows fetched"
View Replies !
Selecting As 'something' If Field Is NULL
I'm doing the following QUERY: "SELECT *, person.person_id as person_id, type.description as typename FROM person". LEFT JOIN registration on person.person_id = registration.person_id AND registration.event_id = ".$event_id. LEFT JOIN type on registration.type_id = type.type_id WHERE person.person_id != ''"; If there is no registration record linked, then it will return blank for the 'typename' but I want it to return 'default'? Is there a way I can have it do this?
View Replies !
Selecting * Excluding One Field
Is there any way to to SELECT everything in a table except one, two or three specific fields? SELECT * 'EXCEPT' field1, field2 FROM tablename; Or do I have to write each one of the required fields?
View Replies !
SELECTING Field - Is Not Empty
can you do a select based on whether a field is empty or not / true or not basically, i want to do something like $sql = SELECT * FROM table WHERE field IS TRUE or $sql = SELECT * FROM table WHERE field IS NOT EMPTY and then in PHP: i can just do something like $check = mysql_query("$sql"); if(mysql_num_rows($check) > '0') { //do something }
View Replies !
Selecting Rows From Timestamp Field
I've got a database with al lot of rows. Each row has a timestamp field, wich indicated the time that that the row was added. The timestamp field is generated by time(). Now, my question, how can I get all the rows, for specific day? So I would like all the rows from yesterday, or 12 days back, or 3534 days back.
View Replies !
Selecting All Values In A Field Question
I have a total of 6 combo boxes. The user has a choice of selecting an item or he can leave it. If the user does not select an item then I would like to run a query to select everything in that field. For example. There are 6 spec boxes. The user can select all or none of those specs. If the user selects all 6 then I simply run a query using all 6 specs. But if the user does not select an item in, let's say Spec#3 and Spec#5 then I would like to run a query with the selected items, but for Spec#3 & #5 I would like mySQL to select everything from that given column. Basically if there is an item chosen then use that value, if no item is chosen then mySQL should select anything in that field where other selected items match....
View Replies !
Not Selecting Rows Where A Specified Field Is Empty
What I am trying to do in a query, is only select rows where a certain field is not empty. For example, I have a visitor logs table, with the following fields: id | date | pageVisited | referrer Now, I only want to select the rows where there is a referrer. Something like:
View Replies !
Selecting Only Parts Of The Content Of A Field?
Is it possible to select only parts of of the content of a field with MySQL? For example if I have some text fields in a table that contain HTML markup in them and I want to only select what is contained within the first <h1> tag from each field. Note that I don't want to just select the fields that have <h1> in them, but select only the text within that tag and not anything else contained in that field. Is this possible?
View Replies !
Update One Field By Selecting Values From Two Other Tables
Here's the tables: kw_log ------- rotation_name network_offer rotations --------- rotation_name offer_id offers ----- id network_offer I screwed up my initial table design in the kw_log table and need to run an update statement to fill in the value for the rotation_name in the kw_log table. The rotation_name is in the rotations table, along with the offer_id which is the same as the id in the offer table. The only way I can see to figure out what rotation_name to base the field value on is to match the network_offer values in the offers and kw_log tables. (Hope this makes sense as I'm lost on how to do this...)
View Replies !
Selecting Date Time Field, Difference In Minutes
I wonder if is there any way to select from a table all the records which has a Date Field that is at least five minutes old? In other words, I have a table with a date field and I need to select all the records that are older than five minutes, has their date field updated before five minutes. Usually I do it with days by using the to_days() function and comparing the date to now: (to_dayss(now()) - to_days(somedate)).
View Replies !
Multible Fields In A Field?
I am making a gaming site that is PHP/MySQL drivin. I put all the codes, games, basically any informaton in the database, and use PHP to take it out. Well, I've been workking on this little project for about a week now, and it just dawned on me. If I can only get one cheat code in per field, how am I going to add multiple cheats. I use one row for easch game title that I am going to use for my site, so, that means that if I wanted cheat codes, I would have one colomn for each cheat. But I can't do that, because I want to type in one code for the "code page" (where the cheats are viewable after the user clicks on the the game title), so that all the codes show up. Code:
View Replies !
Sort By 2 Fields Unless One Field = 0
Quick recap - I know very little about databases. I'm trying to produce a query for a MySQL table. The table has 3 columns of interest: itemid - a unique integer representing this item lastcommentid - a unique integer which is the unique id of a row in another table itemname - free text name I want to select all the items in this table, ordered by the following logic: Quote: - If the lastcommentid is not integer 0, order the results ascending by lastcommentid (lowest lastcommentid first) - However, if the lastcommentid is integer 0, order the results ascending by itemid - Regardless of the above 2 points, only return items with an itemid higher than N. When done correctly this should return records ordered by their last activity, because either: A comment was made on the item in which case its lastcommentid will be greater than other items' lastcommentid, or, No comment was made on the item but the itemid field is greater because the item was added after other items.
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
View Replies !
Query 3 Fields In Same Table As If They Were One Field
I have a very simple test table which I am using to try and get the query right: id | col1 | col2 | col3 | 1 | 3 | 2 | 3 | 2 | 4 | 3 | 1 | 3 | 2 | 1 | 1 | 4 | 3 | 2 | 4 | 5 | 3 | 1 | 3 | 6 | 3 | 1 | 3 | So the table contains and id field and 3 integer fields - each of the integer fields contains an integer between 1 and 4. What I want to achieve is the value of the most frequently occuring entry across the 3 integer fields, as though all 3 fields were actually one - in my example, this would be 3 as there ar 8 occurenecs. I will also require the lowest occuring value also, in this case it is 4. I cannot work out to query all 3 columns simultaneously to obtain my results - I have been looking at joins but cannot work out how to join within the same table
View Replies !
|
TRACKER
