SELECT Based On 2 Tables
I'm writing some custom stuff to pull from a WordPress install I have one one of my servers.
For one function I want to SELECT all the rows from one table based on criteria from that table and another table (they are related by the row ID of the first table). Here is the SQL I have now....
sql Code:
Original
- sql Code
SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC
SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC
It seems to be returning too many results. It's just returning all the rows from wp_posts that match the post_type and post_status criteria and ignoring the term_taxonomy_id (from wp_term_relationships) requirement.
View Complete Forum Thread with Replies
Related Forum Messages:
Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below: 1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)? 2. How can I insert the correct id’s as implied below? I have tried just about everything (including “Insert …select”, “LAST_UPDATE_ID();”, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message; I would greatlyappreciate someone who could instruct me on how to accomplish this task.
View Replies !
Select Based On Rarity?
I have a table and one column is titled "rarity." Every once in a while, ONE random row from this table will need to be selected... I want to select the random row based on the rarity column. All of the rarities would equal 1. For example, a rarity of .38 would have .38 probability of being selected from the table.
View Replies !
Joining Tables Based On Condition
I want to have a field determine what table the field should be joint on. For example, I have a field that is one of eight different values (contacts, leads, accounts, ect...). I want to be able to join on the specific table specified by the field because this field determines what type the id field corresponds to.
View Replies !
Windows Based GUI To Manage Tables
What is best GUI to utilize to connect to my db to make edits to tables, etc...? I thought MYSQL Administrator could do it but I can't connect to my database on a shared server... if this is to the right tool.
View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
SELECT Based On All Day Names And Their Hours
I have a table which has a DATETIME column which stores datatime for each record. I wanted to calculate the total/average of the records for each day and then for each hour of that day ALL TIMES like not only the current week or current month or year but for all available records. a sample output can be Monday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) Tuesday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) ... Sunday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) I tried something like following SELECT count(id) as cnt,HOUR(hitdate) as hr, hitdate FROM hits GROUP BY DAYOFWEEK(hitdate),HOUR(hitdate) And I am getting the following results, (long result) cnt hr hitdate 1274 0 2008-07-27 00:00:50 1235 1 2008-07-27 01:00:07 921 2 2008-07-27 02:02:13 830 3 2008-07-27 03:00:03 674 4 2008-07-27 04:02:49 617 5 2008-07-27 05:00:37 606 6 2008-07-27 06:01:41 545 7 2008-07-27 07:01:52 534 8 2008-07-27 08:05:58 630 9 2008-07-27 09:04:01 680 10 2008-07-27 10:04:41 688 11 2008-07-27 11:01:26 881 12 2008-07-27 12:00:22 745 13 2008-07-27 13:00:09....
View Replies !
Select Columns Based On Subselect
Is it possible to do the following: select (select from factors where type='factor_1') from survey_results where last_name='some_name'; I get the error: "#1242 - Subquery returns more than 1 row" Can I convert the rows to a list? More info if needed: I have two tables, 'factors' and 'survey_results'. A survey_result row contains a list of factors (> 30). Factors can have different types. For a survey result I'd like to select only the columns related to a certain type of factor and analyze just those factors (e.g., find the min and max scores). I don't want to hard code lists of factors of certain types to use in my outside select statement. I'd like to do it dynamically based on the factors table.
View Replies !
Select Statement Based On Bytes
I have a table with an attribute named "message" with a varbinary type in which information is being stored in. It contains a total of 10 bytes and I want to select ONLY bytes 4-7.
View Replies !
Mysql Select Based On Datetime
I am having an issue returning a known record based on a date field that is of datetime format from mysql. the data looks like this in mysql: 2006-01-26 20:01:45 here is the eror I receive: SQL 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 ཐ:01:45' at line 1 this is the select statement I am using ("SELECT selections from your_show WHERE userid = $userid and date = ".rawurldecode($date)." "); $date is passed as 2006-01-26%2020:01:45
View Replies !
How To Query Multiple Tables Based On Value Difference
I 5 tables all with equal columns,but with different values. Per example: time_start,time_end,num_a,num_b,price etc. How can I retrieve the values of all columns `price` between tableX and tableY where time_start in table Y is higher as a datetime than time_start in X. So,the query is about finding the values of `price` between 2 dates in differnt tables.
View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
Select Record Based On 'change' Date
I currently have a database containing a table with the following fields: signid, sign_typ, changedate and time. The 'changedate' field is the date on which the star sign changes and becomes 'active' until the next 'changedate' in the table. I am trying to search this field by a date the user enters and display the 'sign_typ'.
View Replies !
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag: resource - 1:M - tagged_resource - M:1 - tag So a resource can have many tags and a tag can belong to many resources. The data would look like this: RESOURCE TABLE id: 1 name: resource1 TAG TABLE id: 1 tag: new id: 2 tag: tested TAGGED_RESOURCE resourceID: 1 tagID: 1 resourceID: 1 tagID: 2 How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE? I have tried using 'IN' but it acts like an OR operator rather than AND.
View Replies !
Join Or Combine Two Tables Based On Unique Column
I use MySQL query browser and have made two tables in one database: First table: Test Column names: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)) Second table: Hashtest Column names: Filename (Varchar(255)), RowNumber (Integer), MD5 (Varchar(255)), SHA1 (Varchar(255)), Pad (Varchar(255)) Of both tables the column RowNumber is the primary key. Also, in both tables the column Pad is the same (the same content, not exactly in the same order) Now, I want to combine the two tables into one table, based on 'Pad' I suppose I have to use the Script function of MySQL query browser. Please explain to me what I have to do to combine the two tables into one table. This third table has this columns: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)), MD5 (Varchar(255)), SHA1 (Varchar(255))
View Replies !
Work Out Daily Interest Rate Based On 2 Tables
Hi I have the following DB structure charges cID [bigint] cpID [bigint] amount [decimal 3,2] date_charge [datetime] petitions pID[bigint] interestrate [decimal 3,2] date_claim [datetime] And need to do the following calculation: ($amount * ($date_claim - $date_charge) * ($interestrate / 36500)) The ($date_claim - $date_charge) section needs to work out the difference in days Is something like that possible?
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
CREATE Tablename Based On Result Of SELECT Statement?
Is it possible to have an CREATE statement that contains a SELECT in the section of the CREATE that specifies the TABLE? I want to have a sql script that can execute SELECT YEAR(CURDATE()) and SELECT MONTH(CURDATE()) to give me the Year and Month and then use these to create the Table Name: tbl_MetaData_2008-03 Example: CREATE TABLE tbl_MetaData_X-Y ( ... Where X is the YEAR and Y is the MONTH
View Replies !
Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table? for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0. i don't want merged results. i just need to list all therapist based on the where criteria from a different table. these two tables have the therapist_id in common.
View Replies !
How To Select 2 Tables
I need to select 2 tables . The field 'p_description' is not always aviable but i still need to print it to the screen SELECT * FROM task,p_description WHERE ComponentIDLink = 34 OR task.TaskID = p_description.pTaskLink GROUP BY TaskID ORDER BY TaskName If i use AND it works, but there are colums missing Is it possible in a OR that there is a default value ?
View Replies !
Select Two Tables
i have two tables (i'll called it Table1 and Table2) table1 has more rows than table2. When doing the query, I want the t2.info to came out in the result as a blank, not just select only same columns exists from both table. I need code other than "t1.id=t2.sid" because this will ignore if there is no entries in table2. I want to return blank variable for that rows.. Code: SELECT t1.*, t2.info as info FROM table1 as t1, table2 as t2 where t1.id=t2.sid ORDER BY id
View Replies !
SELECT From 3 Tables
Table users: Fields: id | name | rank Table sales: Fields: id | order | uid | date Table returns: Fields: id | order | uid | date How would one go about selecting: SUM(sales.order), SUM(returns.order) and users.name WHERE date=...
View Replies !
SELECT * FROM All Tables
Is there an easy way to select all tables and all their columns/rows from within a database? The column names are the same in all the databases, so it keeps throwing 'Column: 'column_name' in where clause is ambiguous' Basically I need to get all the rows from each table on top of each other...is there any way to do this? Also, I've noticed I'm limited to about 12 tables to look through in a single query: SELECT * FROM t1, t2, t3...t12 Is there any way I can increase this?
View Replies !
SELECT From Different Tables
is it possible to do SELECT queries from different tables in the same DB in one query? E.g. I would need something similar like that: table 1: a b c 1 2 4 table 2: d e f 3 4 5 And I would need to do a select where from table 1 b = 2 and from table 2 e = 4
View Replies !
Select From Three Tables
where I need to select data from three tables, namely a studentFirstName, studentLastName from a student table, linked to a modules table via studentID - easy enough. But I also have to retrieve a moduleName from modules table where the mod_ID can correspond to any of five modules that a student may be taking. Also the version of mysql available does not support subqueries.
View Replies !
Select Through Several Tables
I have 3 tables - tableA, tableB, and tableC. In tableC, one of the columns is Bid, which refers to a row in tableB. In tableB, one of the columns is Aid, which refers to a row in tableA. How do I select the id of the row in tableA that corresponds with a row in tableC? (Based upon the tableC row's Bid and thus Aid path.)
View Replies !
Select Using Several Tables
I can work around this problem by making several select's but it's getting a bit of performance issue to me... I have -table1 with fields id (primary key), active -table2 with fields id (not relation to table1), rel (this is the relation to table1 :-), ... There are no indexes in table2 yet. What I try to do is to select records from table2 where rel is certain value & table1 should be active. I tried to do it with: select * from table2, table1 where table2.rel = somevalue and table2.rel = table1.id and table1.active = 1; but it returned actually all the records where table2.rel = somevalue...
View Replies !
Select With Two Tables
I need a query to obtain data in a table ordered by recurrence its field in another table. Example: I've table NAME with 1 field and table AGE with 2 fields. (NAME joseph mark mary joseph tom mary victorya joseph) (AGE joseph 26 mark 32 mary 21 tom 28 victoria 34 ) I'd like obtain: 26 21 32 28 34 Because joseph is 3 times in Names and in age is 26, mary is 2 times in Names and in age is 21, anyother is in Names just one time.
View Replies !
SELECT From 2 Different Tables
i'm using this query at the moment: SELECT COUNT(*) AS total FROM buddies WHERE (buddy_from=1 AND buddy_for=2) OR (buddy_for=2 AND buddy_from=1) It results the value '2' (total) if both users are "friends". Now i want to include the following query in the one above: SELECT notification FROM main WHERE userid=2 This must be something like this: Code:
View Replies !
Select With Three Tables
I need combining some table results. I've been able to do this in the past with two tables, but am having some troubles doing it with three. I have 2 reference tables: fav_books fav_authors both have two fields: user_id fav_book / fav_author (depending on the table) I have a third table: users which has a bunch of fields about the user which I need to display, including: user_id However, the fields in 'users' don't need to be searched by, just the info gathered from. What I want to do is compile a ranking of users that match a specific user's interests. So, I want to take the list of fav_books and fav_authors for userA and then get a top 10 list of users that have the most matches of both books and authors. So results like: USER | LOCATION | MATCHES UserB | New York | 5 UserC | San Francsco | 3 UserD | Dallas | 2 Are what I'm looking for. Location (and other fields) would come from 'user', and matches would come from a count/group by of user_id for results from both fav_books and fav_authors. I can do it if I just search fav_books OR fav_authors, but not both.
View Replies !
2 Tables + Select + Max()?
I have 2 tables like this: http://img240.imageshack.us/img240/8695/bild2np0.png And I'd like to have an output like this: http://img99.imageshack.us/img99/3983/bild5mg9.png So the maximum points for the IDs in t1 should be displayed. The other values not. I tried something with a subselect and max(). But it was not possible for me to generate a valid query for that issue.
View Replies !
Select From Two Tables
I have two tables with two columns each, table 1 has an "id" and "first name", and table 2 has "id" and "last name". Table 1 has 50 rows and table 2 has 20. I want to get in 1 query ALL the "first name" fields, and also get the "last name" that matches them, so 20 results will have both "first name" and "last name", and for the other 30 results, the "last name" column will be empty.
View Replies !
How To Select From 2 Tables
I'm building an online database programme (with PHP) where different users can add the contact info of the people in their school. I've set up 2 tables: 'users' (which will hold the login info- user name/ password) and a second table 'data' (where the people can store the information). I have put in a 'users' field in the 'data' table. I want this to relate to the 'id' field in the 'users' table'. In plain English I need to 'select all from the 'data' table where the 'user' field = 'id'field in 'users' table.
View Replies !
Select From 2 Tables
I have two tables (a simplified example): Tname: users field: id, uname Tname: news fields: id, uid, utitle, lastuid (the value of uid and lastuid field is from the users table, and not necessary equal) I want a select statement which retrives all the rows from news table, and the uid and lastuid is substituted with the corresponding usernames (uname). If there was only one field (eg. uid) which I want to substituted than I would use this: SELECT news.id, users.uname, news.utitle FROM news, users WHERE news.uid=users.id; Is there a query that makes this ? Or should I make 2 queries for this?
View Replies !
SELECT On Two Tables
I have two tables : Table Event - event_id Table Event_member - Mevent_id - Member_id When a user register to an event, I add the event_id and member_id in the Event_member. But now, I want to select all the events that a USER is not registered. I have done these requests: SELECT "event_id" FROM event, event_member WHERE member_id = USERID AND event_id != mevent_id or SELECT "event_id" FROM event, event_member WHERE member_id = USERID AND event_id <> mevent_id I have edited because I have forgotten "member_id = $userid". I didn't use directly these requests, I convert them in PHP before using them But they didn't work,
View Replies !
Select From 2 Tables...
I know there are loads of examples out there but im a complete novice and really dont understand code unless someone explains it to me.... Trying to figure out how to do this: $query = "SELECT * FROM story WHERE storyID='" . $_REQUEST["storyID"] . "' AND SELECT * FROM section WHERE sectionID='1a';"; each table (story and section) has totally different columns.... what im trying to do is pull out information mainly from the story table but it also has to match the sectionID from the section table.... both tables have storyID.
View Replies !
Need Help On A Select From 2 Tables
I am trying to get information from 2 tables in one select query. Have 2 tables. Lets call them rip_people and rip_people_comments Basically if you looked at d i g g.com each "posts" has a comment counter on the lower part of the post that shows how many times the post/**** has been commented. Well this is almost exactly what I am having trouble with. I am selecting 15 posts per page but I need somehow to get the comments count along with the 15 posts. This is what I have so far and it lists the same posts because they have more than one comment. PHP SELECT r.rip_people_id, r.title, r.message, r.image, r.video, r.date, c.comment FROM duo_rip_people AS r LEFT JOIN duo_rip_people_comments AS c ON r.rip_people_id=c.rip_people_id ORDER BY r.date DESC
View Replies !
Select From 2 Or More Tables
I need to run a select quer against 2 or more tables and return rows with data from all tables, but if data does not exist in one table, no data is returned from the other tables. How can I ensure that a row is returned even if one table does not contain data corresponding to the other tables? For example: lets say I got two tables: "users" and "user_groups". "users" contains the columns; id and name, and the "user_groups" contains the columns user_id and group_name. I run a select query as follows: SELECT users.name, user_groups.group_name FROM users, user_groups WHERE users.id=user_groups.user_id This works great if a user_group exists for every user. But if a user_group does not exist, then the user's name is not returned at all, even though it does exist. So how can I make it return the data even if the user group does not exist??
View Replies !
Select Distinct On Two Tables
I have two tables, both containing an 'authors' column. Is there a way to get a unique list of authors from the two tables? I tried SELECT DISTINCT `authors` from `table1`, `table2`; but I got an "Column 'authors' in field list is ambiguous" error. Is there also a query to return only the count of distinct authors from the two tables?
View Replies !
Select Distinct On 2 Tables
using mysql 4.0.21 is this the best way to get distinct names from both tables where shift is 20031007: SELECT m.user_full_name FROM press_maint m WHERE shift_date = 20031007 UNION SELECT p.user_full_name FROM press_prod p WHERE shift_date = 20031007 GROUP BY user_full_name
View Replies !
Distinct Select On 2 Tables
I'm trying to do a select distinct data from from 2 tables and the solution I'm having almost works but it's still pulling multiple records from both tables. Here's the Select statement I'm trying: SELECT DISTINCT table_1.table_1_id, table_1.title, table_2.field_1 FROM project, conversation WHERE table_1.table_1_id = table_2.table_2_id
View Replies !
SELECT DISTINCT From Two Tables
I've been driving myself crazy messing up this concept. I have two tables that that both have p_id, I'm trying to select p_id where they're is no overlap. Example: product_table p_id = 2 p_id = 3 manufacturer_table p_id = 3 --- How can I select p_id's from the product_table if they're not added to the manufacturer_table. Example logic: select from product_table AS p, manufactuer_table AS m where p.p_id != m.p_id --- Desired output: p_id = 2 ---
View Replies !
Select Main_Categories From 2 Tables
i have 2 tables -- 1) main_categories table = cat_id, cat_name, user_id 2) sub_categories table = sub_id, sub_name, cat_id, user_id i want to display all "cat_name" from main_categories table and all the "sub_name" from sub_categories table where the user_id='$x' so how do i write the query in my php?? i wrote like this -- PHP Code:  $query1 ="SELECT cat_name, sub_name FROM main_categories, sub_categories WHERE user_id = '$x'"; but i am getting no results. also no errors. also i am able to get the $x. the reason i want to extract the stuff through one query is because i am using a mysqllate and the class function displays the output in one go.
View Replies !
Select From 2 Tables If 1 Is Empty
I want to collect data from 2 tables without any relation between them in one query. PHP Code: $query = "SELECT f.str,c.numb FROM ".TABLE1." AS f ,".TABLE2." AS c WHERE c.cid = 1 "; If TABLE1 and TABLE2 are both not empty, query returns results from both tables. If TABLE1 is not empty and TABLE2 is empty query returns results from TABLE1. If TABLE1 is empty and TABLE2 is NOT, query returns no results at all. UNION doesn´t help because I need result set which distinguish columns from both tables (using php). How can I return results from TABLE 2 if TABLE 1 is empty?
View Replies !
|
TRACKER
