Join Query
i've got two table in the format:
referrers
--id, name, url, image
country_refs
--id, name, domain, ref1, ref2, ref3, ref4
and i'm using the query Code:
SELECT referrers.name, referrers.url, referrers.image, referrers.description
FROM country_refs
JOIN referrers ON ref1 = referrers.id OR ref2 = referrers.id OR ref3 = referrers.id OR ref4 = referrers.id
WHERE domain = 'gb'
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Using 'if' In A 'join Query' ?
I have two tables items and food_names in mysql db items ->structure **************************************** id food_items 1 veg 2 non-veg 3 veg & non-veg **************************************** food-names ->structure **************************************** id items_id foods 1 1 vbvcb 2 2 cvbvbv 3 3 gfdgdfgd 4 3 bbvcbvcb ******************************************* if i choose veg if(id=1) I want to write a db query for both veg and veg & non-veg else if i choose non- veg I want to write a db query for both non-veg and veg & non-veg else if i choose 'veg & non-veg' then i have to dispaly all.
Query: Inner Join Bug
What is wrong with this query? I cant get it to match the "company" field and it is throwing an error... this query works fine if i do "MATCH(date_year, market1, market2, market3, market4, market5, market6)" , but if I put "MATCH(company)" it breaks.... what am I missing here? PHP $query = "SELECT u.id , u.username , r.id , r.company , r.description , r.market1 , r.market2 , r.market3 , r.market4 , r.market5 , r.market6 , r.location , r.date_year , r.date_month , r.source , r.video , r.audio , r.pp , r.execsum , r.report_url , r.exec_url FROM user as u INNER JOIN user_reports as p ON p.user_id = u.username INNER JOIN emt_report as r ON r.id = p.report_id WHERE username = '$username' AND MATCH(company) AGAINST ('$P_search' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC"; Here is my database schema: ================= user_reports ================= user_id report_id ================= user ================= id username ================= emt_report ================= id company description market1 market2 market3 market4 market5 market6 location date_year date_month source video audio pp execsum report_url exec_url
Using Both JOIN And AVG In One Query
I'm looking to use both JOIN and AVG in the same query but not sure how I'm going to it. I'm VERY new to using MySQL. The ultimate goal is to take one table which counts votes for various criteria in a poll. I want to average those results together. Then I want to join that averaged table with another table and GROUP By URL (which is the column common to both tables). I've successfully used AVG with the first table and used JOIN (with a totally different) table, I'm not sure how to approach using both together.
Help With A Query Join?
I have a query whereby I look in two tables a teams table and results table to output some data for some football scores. SELECT th.team_name AS home_team, ta.team_name AS away_team FROM results r INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id Now I want to add a third join there on another table named reports to see if the match_date in the results table matches a match_date in the reports table SELECT th.team_name AS home_team, ta.team_name AS away_team, re.match_date FROM results r , reports re INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id INNER JOIN reports WHERE r.match_date = re.match_date Now this works great, but I want to output everything in the results table, and not just where results.match_date = reports.match_date as there may not be a report for every result and I want to still output every result nomatter if there is a report or not?
Using Join In A SQL Query
I have a problem which cant sort out. I have 3 tables where the primary keys in two tables also serve as keys in the third table. Table1:one two three four Table2:five six seven eight Table3:nine ten eleven one holds the same information as nine five does the same for ten Now I need to get information from the first and third tables but also need to use the second table to get the information from the third table. I can get the info from the first table grand , but its joining the 3 tables together is where I get stuck. I know its very abstact but this is the way I got the Tables.
Join Query
My problem is with my query below(sname means student name lecname means lecturer name) the table it produces means that the same lecturer can be supervising 2 projects at the same time(eg. Mon 9.00am in the query below) which is impossible. So is there a way I could change it so that lecname can only be associated with one projtitle for each time? INSERT into time select distinct p.sname,p.projtitle,s.lecname,s.time from projectstatus as p,properstafftimetable as s left outer join time as t on t.sname2=p.sname where t.sname2 is null and s.lecname = p.supervisor and s.time = "Mon 9.00am";
Join Query
I'm having trouble with a (simple?) concept in MySQL join. This query gives me the correct data from all 3 tables, but I need to sort by the date from 2 of the tables: SELECT det.CalendarDetailsID, dai.StartDate, wee.DisplayStart, det.Title, det.DetailsFROM phpCalendar_Details detLEFT JOIN phpCalendar_Daily dai ON det.CalendarDetailsID = dai.CalendarDetailsIDLEFT JOIN phpCalendar_Weekly wee ON det.CalendarDetailsID = wee.CalendarDetailsID Is there a way I combine dai.StartDate and wee.DisplayStart dates fields into a new (alias?) field that I can sort with? Or is there a way that I can simply sort the two fields within each other without combining them?
JOIN Query
This query won't work... tried to execute it a million times, it just loads forever. Any clue why? SELECT oh.client_id, oh.id AS load_number, oh.deldtime, oh.pickupdtime, oh.dtime, oh.status, oh.order_uid, oh.driver_id, cm.name AS client_name, oo.order_id, oo.city AS origin_city, oo.state AS origin_state, od.city AS destination_city, od.state AS destination_state, od.order_id, os.id, os.status AS order_status, dm.id, dm.name FROM order_header oh INNER JOIN client_master cm ON cm.id=oh.client_id INNER JOIN order_origin oo ON oo.order_id=oh.id INNER JOIN order_destination od ON od.order_id=oh.id INNER JOIN order_status os ON os.id=oh.status INNER JOIN driver_master dm ON dm.id=oh.driver_id ORDER BY oh.id DESC LIMIT 10
Join Query
I have two tables dispatch and pending orders now i want to get fields from both the tables where either dispatch.mail_id = 'p@yahoo.com' or pending.mail_id = 'p@yahoo.com' order by either dispatch.order_no or pending.order_no. How would I be able to do this with a join query.
Wish To Join A Query
I have this query: PHP Code: SELECT * FROM `news` ORDER BY `id` DESC LIMIT 6 I wish to also select the users id and username from the members table where the users id equals the "owner" field from the query above. As using another query apparently would be stupid?
Join-query
I cannot figure out how to query the db. I got 3 tables: players (player_id, name, number) stat_start (opponent_id, name_id) name_id = players.player_id stat_avb (opponent_id, name_id name_id = players.player_id Now I want to list all my players, and count how many times they have occured in stat_start and stat_avb. I guess I need to do an Inner join as i want all my players listed...
Join Query
this is embedded in a php file, and the request should bring all open assignment_events. it works fine except it spits them out 3-5 times, its the query and not the php, could someone please help with a join? Code: SELECT c.customer_id , a.assignment_id , ae.desc , ae.assignment_event_id , ae.closed FROM customer c , assignment a , assignment_event ae , event_type et WHERE a.customer_id = c.customer_id AND a.customer_id = 1 AND a.assignment_id = ae.assignment_id AND ae.closed = 0 ";
Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk). On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query. I'm also not sure I'm thinking of this right. The fields on clientFamily start off like: clientFamilyID spouseFirstName spouseMiddleName spouseLastName spouseOccupation motherFirstName motherLastName motherOccupation fatherFirstName fatherLastName fatherOccupation I think that part's ok, but if I add: childFirstName childLastName childBirthday childSex and sibFirstName sibLastName sibOccupation how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.
Join Query Problem... Help!
I have two tables: STORES ------- id name ORDERS ------- id total cost store_id date_ordered I want to select ALL stores and their total sales for a date range. Here's my query: SELECT stores.id, stores.name, SUM(orders.total), SUM(orders.cost), COUNT(orders.id) FROM stores LEFT JOIN orders ON stores.id = orders.store_id WHERE orders.date_ordered > 'xxxx-xx-xx xx:xx:xx' GROUP BY stores.id This works, except that it only selects stores that have orders. If I leave out the WHERE clause, it then selects ALL stores, even without orders, and populates the sums with "null" That is what I want - if the stores don't have any orders, the results should simply return null or 0. However it seems that stores without orders are simply not being returned in the results at all.
Join Query Problem
student ------- id name 1 name1 2 name2 3 name3 Class ----- cls_id stu_id marks 1 1 100 1 2 300 5 1 400 5 3 500 I want to show only those student that are not class Ƈ' So I wrote select s.id,s.name,c.marks from student s,class c where s.id=c.stu_id and c.cls_id<>Ƈ' but it gives 1 name1 400 3 name3 500 but name1 is already in class Ƈ' . I desire the output only 5 3 500
Join Query Chaos
I cannot get this join to give me what I need. Here is my setup. I have 3 tables: courses, modules, sessions. courses has this structure:
JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student. select sc.name, Sum(if(student.SNO is not null, 1, 0)) nmbr, from school sc left outer join student on sc.school_id = student.school_id group by sc.school_id this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13. i could not find a solution.But i suspect from NULL threatment.
SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem: SELECT * FROM footballers WHERE sname LIKE '%$criteria%' however, if I want to join two tables and still use the LIKE clause how would the syntax look?? My guess below was incorrect. any help would be appreciated. thank you WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)
Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used). What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?
Ranking Query (self Join)
I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking. I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.
Help With Join Query Mysql 4.0
As I understand subqueries do not work in version 4.0 Here is what i am trying to do; select a, b from table1 where table1.b != (select distinct c from table2); Other words: table1.b and table2.c have values I want to match on I run select a, b from table1; subtract all out using select distinct c from table2; and leave me with a and b from table 1 that have no corresponding value of c in table 2
Generating A JOIN Query
I need some help with a MySQL query, probably a JOIN. Here is the situation: I have two tables. The first one contains customer info. Table CUSTOMERS id | name | address id ... customer ID (MEDIUMINT, autoincrement) name ... customer name, for example John Doe address ... customer shipping address Then I have the second table which contains sale info from all customers....
Query Involving JOIN
I have two tables populated during the use of an application to log user events and application states. They are named "EventTable" and "StateTable" and the structures follow: EventTable: ID EventTimeStep EventID -- ------------- --------- 1 5 E1 2 22 E2 3 56 E3 StateTable ID StateTimeStep StateID -- ------------- ------- 1 1 S1 2 39 S2 I want to perform a query that reports the StateID of the application at the time that each event was logged to the EventTable. The desired output is: ID TimeStep EventID StateID -- -------- ------- ------- 1 5 E1 S1 2 22 E2 S1 3 56 E3 S2 I have tried to create a query with an INNER JOIN where the value for the StateID output field comes from the last row in the StateTable WHERE StateTable.TimeStep <= EventTable.TimeStep and where I use a GROUP_BY EventTable.ID to merge the following rows from the join: 3 56 E3 S1 3 56 E3 S2 However, the closest I can get is a query that gives me the wrong state when applying the GROUP BY clause 3 56 E3 S1 I also think that the queries I have written is slow and inefficient. Is there a better way to perform this query or is my database design fatally flawed?
Table Join Query
I'm trying to join multiple database tables and having a little trouble with it, hope someone can help: There are 3 tables with columns: id, user, date, time, action I'm trying to get user, date, time and action from all 3 tables where the user equals "anyuser" and sort the result by date DESC, time DESC.
Join Query By Date
I've these tables: - PERSON (id,name,age,....) - EXAM (id,date,note,exam_type,id_user) One person has 0 or more exams. I have to do this report: name, age, date, note, exam_type This looks easy, but I have to list for each person, his/her LAST EXAM GIVEN. I mean, only the last exam must be shown for each person, and well, if person has no exam yet... to show blank (if possible)
MySQL JOIN Query
I am a recreational MySQL user, and I tend to make some non-optimal queries. In my latest project I made some LEFT JOIN queries that were extremely slow. I made two changes that made the queries almost instant. 1) indexed the columns that the joins were working on 2) optimized the tables being joined (using phpMyAdmin > Operations) All I can say is WOW!!! I had no idea that the resulting difference in speed would be so great. After I did step (1) it added a tremendous amount of speed, and after I did step (2) the queries responed almost immediately. So if you are running slow with JOINS, give indexing and optimization a try.
Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing
Left Join Query
I have this query SELECT user.user_id, email, password, full_name, company_name, first_login_date, last_login_date, first_draft_date, last_draft_date, first_submit_date, last_submit_date, rowc.* FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id WHERE user.user_type = 'USER' ORDER BY user.email Which returns all records in user table plus rowc records if there are matches in the user id. but user.user_id is null when there are no matching recordings in the rowc. Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?
Query Trouble With LEFT JOIN
My query as it echoes out in PHP... SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP(shop_orders.date) as date, sales_reps.stage, sales_reps.status, ....
Having Troubles With A LEFT JOIN Query
I'm currently building a private members area for a website where designs can be submitted and rated before they are released to the public under one unified "pack". What I am trying to do is to create a query that lists the latest submissions along with their ratings (if they have been rated) and if the user has rated that submission or not. Here is my query: SELECT s.id as id, s.title as title, s.users as creators, ROUND(AVG(r.rating), 1) as rating, r2.id as userhasrated FROM submissions AS s LEFT JOIN sub_ratings AS r ON r.subid = s.id LEFT JOIN sub_ratings AS r2 ON r2.userid = ".$user->getUserId()." AND r2.subid = s.id LEFT JOIN packs AS p ON p.id = s.pack AND p.released = 0 WHERE s.state > -1 GROUP BY r.subid ORDER BY s.id DESC LIMIT 10 The problem is that it only returns two rows: one with some submission that hasn't been rated and one with some submission that has been rated (but only rated by the user indicated by $user->getUserId()). How can I fix my code so that it returns as many rows as I desire?
JOIN Query Related Question...
I have two tables as shown below: Table 1: Actor.............................Table 2: Movie Field:...ID.....Last......First..............Field:...ID.....Last......Movie ...........1.....Willis......Bruce.......................1.....Willis......Die Hard ...........2 ....Cruze....Tom..........................2.....Cruze.....MI-III ...........3.....Pitt.......Brad ...........4 ....Depp.....Johnny Then I JOIN the tables with Query which works fine, following is the result: Field:...Last......First........Movie ..........Willis......Bruce......Die Hard ..........Cruze....Tom.........Cruze MI-III ..........Pitt.......Brad ..........Depp.....Johnny MY QUESTION: What JOIN option should I use so it would display data that are in both tables (such as Bruce Willis and Tom Cruze).
How To Join Three Table Into A Little Complicated Query
I am using version 4.1.22. I simplifying this for example's sake. I have three tables: order: order_id tip_amount item: item_id order_id price vendor_id vendor: vendor_id vendor Name Each order can have many items. Each item has one vendor. I need a query that displays all orders grouped by order_id, EXCEPT where there are multiple vendors where I need it to show grouped by order_id in the first column then grouped by vendor_id in the second column. Plus I need the tip_amount to show ONLY on the first line of the order. It would like this (notice order# 299 has two entries and the tip amount was added to the first record vendor record that order): PHP order_id vendor_name total tip298 ABC $30 $2299 DEF $25 $4299 XYZ $12 300 LKW $17 $7...
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
Problem In Left Join Query?
I have a pretty complex query and it's practically crashing my system due to generating more data than I am looking for. I basically have 2 tables. They should contain very similar data as they contain information about the same types of things. I was asked to join the 2 tables, getting any information from table 1 that does not exist in table 2, but keep everything of table 2. Anyway I tried this query and it just hangs forever. One problem could be because I have no ID field to match up between the tables. I'm trying to join them based on an address, city, and zip. Select * INTO OUTFILE 'output.tab' FIELDS TERMINATED BY ' ' LINES TERMINATED BY ' ' FROM table2 LEFT JOIN table1 ON table2.address = table1.address and table2.city = table1.city and LEFT(table2.zip,5) = table1.zip; My resulting file should be 13000 records in length, with 3000 records that have additional information based on table1 data.
MySQL Query Assistance (JOIN)
This is probably easy for some of you DB gurus, but for me, I am having some trouble. Even after R(ing)TFM. Background: This is for a home-grown photo gallery that I am making for my daughter, as she is going to study in Italy next year. The query I CAN do is to pull all the sets of images from the DB and their associated images aka: `filename` PHP <?php $query = "SELECT sets.sid AS `setID` , `name` , `filename` FROM `sets` , `pix_to_sets` , `pix` WHERE sets.sid = pix_to_sets.sid AND pix_to_sets.pid = pix.pid ORDER BY sets.time_stamp DESC"; ?> However, it would be "super cool" if I can limit this query to retrieve ALL the sets, but ONLY get one "filename" per set. Any suggestions? I am willing to read, so if you want to point me to a page in the manual that would help, I would appreciate it. Or, if you are so kind as to show me the query, PLEASE explain it, so I can understand it, and do it myself next time.
Join Query - I'm Losing My Mind
I'm not sure if I've totally lost my mind on this, but I can't seem to find the solution. I have two tables: projects --------- project_id project_name project_type project_status -------------- profile_id project_id status comment So lets say I have the following data projects -------- 1, Project 1, Internal 2, Project 2, Internal 3, Project 3, External project_status -------------- 1, 1, submitted, comment 1, 2, final, comment2 2, 2, initial, comment3 I want to select all rows from projects joined with the data from project_status using project_id as the key, like this: project_query -------------- project_id project_name project_type status comment BUT I only want to join on rows in project_status where profile_id = 1, i.e. I get this data from the query: 1, Project 1, Internal, submitted, comment 2, Project 2, Internal, final, comment2 3, Project 3, External, , , Basically I don't know how I can join the project table with the project_status table which it only displays results with profile_id = 1.
Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username FROM (( " . phpbb_forums . " f LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id ) LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id) ORDER BY f.cat_id, f.forum_order"; Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions? The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?
Select With Join Query Question
I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. The problem I'm having is that when there is more than one record in Submissions that match a record in Jobs and one Submissions record has a status of 1 and one record doesn't, my query matches the one with status !=3D 1 and returns the record for Jobs (even though it has a record in Submissions with a status of 1 also). I've tried a variety of queries including left outer joins and more simple join relationships. I'm using MySQL 3.23.47 on Windows. Here's an example query: select j.job_id from jobs j left outer join submissions s on (j.job_id = =3D s.job_id) where s.status_id !=3D 1 group by j.job_id Any help is greatly appreciated.
DELETE Query With Left Join?
I want to delete items from a table that are determined with a left join. using DELETE ( SELECT * FROM item_colors LEFT JOIN items ON item_colors.item_id = items.item_id WHERE items.group_id =3 ) FROM item_colors does not work. how would be the right syntax? The SELECT part returns the items I want to delete from item_colors correctly.
Distinct And Join Query In MySQL
I have a query question, it may be really easy but somehow I cannot get it What I have is a table with columns, two of which I will name Item1 and Item2, I would like to get a distict set of pairs Item1-Item2 from this table, more specifically if table looks like this ....
Help Needed For Query Involving JOIN
I have two tables populated during the use of an application to log user events and application states. They are named "EventTable" and "StateTable" and the structures follow:
Table Join Invalid Query
I have this table join below and I am getting an invalid query. How can I put the 15th line ($sql .= " group.id = master.id "; and the 17th line ($sql .= " where ".$currentrow_sql ; together. Is it even possible to do that?
Figuring Out When A Query Uses An Index On Join
The variable "Select_full_join" is showing a very high number when the recommendation is to have this at zero. I can get a full list of all queries generated on the site but how do I figure out which ones aren't using an index in the join?
Join Query Result Difference Between 3.23.49 And 4.0.13
I have what seems to me a very common operation i'm performing. I need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. There must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what I expected, nor what I received with v3.23. I'm including sample data and queries with my results.....
Simple JOIN Query Issues
What I want to do is have multiple tables interact with each other. payments TABLE id | paidfrom | paidto | paidmethod 1 | 2 | 1 | 2 2 | 3 | 1 | 3 users TABLE userid | username 1 | Jake 2 | Todd 3 | Spencer methods TABLE methodid | methodstring 1 | Cash 2 | Check 3 | Credit Card
Cross Join Effects With Query Optimization
ELECT count(t.id) FROM `ll_textlink` AS t, `ll_period` AS p , `ll_user` AS ua , `ll_user` AS up WHERE t.actual_period = p.id AND p.status_approval_publisher = Ƈ' AND p.value > Ɔ' AND t.online >= Ƌ' AND Ƈ' >= ( SELECT count(p.id) FROM `ll_period` AS p WHERE p.textlink = t.id ) AND t.online <> Ɗ' AND t.current_value > Ɔ' AND t.advertiser = ua.email AND t.publisher = up.email AND ua.rights = Ɔ' AND up.rights = Ɔ' ; This is what I am at now: SELECT count(t.id) FROM `ll_textlink` AS t INNER JOIN `ll_period` AS p ON (p.textlink = t.id) INNER JOIN `ll_user` AS ua ON (ua.email = t.advertiser) INNER JOIN `ll_user` AS up ON (up.email = t.publisher) WHERE t.actual_period = p.id AND p.status_approval_publisher = Ƈ' AND p.value > Ɔ' AND t.online >= Ƌ' AND t.online <> Ɗ' AND t.current_value > Ɔ' AND ua.rights = Ɔ' AND up.rights = Ɔ' ; However, the number return is quite a bit higher than the from the first query. Are there any cross-join effects and if so how can I avoid them?
Query Returns Unexpected Users. Not Using Join.
Having a bit of a problem trying to get the correct results. Basically I have three tables I need to select from; user, platform and game. I'm running the following query select distinct u.user_name, u.user_avatar, u.user_rating from user_game ug, platform p, user u where ug.game_id = 1 and p.platform_id = 1 and u.user_country = 1 Basically I want it to return users who own the game "1", have it on platform "1" and live in country "1". Unfortunately does not return users who meet ALL WHERE criteria. Any ideas where I am going wrong? I know an alternative is to use JOINS but I don't want the performance to be hit by doing that.
|
TRACKER
