Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....
View Complete Forum Thread with Replies
Related Forum Messages:
Union Query
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT [faxid] as ID from faxdata UNION SELECT [letid] as ID from letdata UNION SELECT [memoid] as ID FROM MEMODATA; I get an ODBC error. The same query runs when the backend files are MDB files and it runs with MYSQL if I only combine 2 tables. Is there some limit with MYSQL on being only able to use a UNION on 2 tables.
View Replies !
Help With A Query That Has UNION And SUM
I am trying to join 2 tables (union) and calculate the final quantity like below: PHP (SELECT SUM(`sales`.`Quantity`) FROM sales WHERE `sales`.`SKU` = 'S0089-060927-S0072-bar-exP^223') UNION ALL (SELECT SUM(`reversals`.`Quantity`) FROM reversals WHERE `reversals`.`SKU` = 'S0089-060927-S0072-bar-exP^223') The result that I am getting when executing this is 2 lines. One is the sales total quantity (let;s say 10) and the other is reversals total quantity (let's say 2). How can I merge so that i have the net quantity (ie 8)?
View Replies !
SELECT / UNION Query Help!
I have a list of queries in a union, ex: (SELECT * FROM tbl WHERE a='b') UNION (SELECT * FROM tbl WHERE c='d') UNION (SELECT * FROM tbl WHERE e='f') etc. I would like to calculate in the query the percentage of criteria each row (primary key ID) fits. For example, if a row with id='20' fits 2 of 3 SELECT criteria, then an additional variable (created in the query) such as: percAccurate would equal to (instances/total_select_queries), so in this case: for id=20, percAccurate = (2/3) = 66% A result row could be returned like this:
View Replies !
Type All In Explain For Union Query
I am trying to optimize a union query for which the union result is a type of all, but when each component is explained the types are either ref or eq_ref. There are quite a few tables and views which form the bases for the ultimate query, so I don't think posting all of those would be viable. I am just looking for pointers.
View Replies !
Union Query To Join 4 Tables
I am trying to make a union query to join 4 tables. I have reduced the tables to just 5 fields and made sure that the field types and names are the same. I keep getting an error message ODBC call failed. I have tried various combinations of the tables and find that I can use any 2 of them but as soon as a third is included in the statement the query fails. Is there a limit on the number of tables in a union query. The union query looks like this in its simplest form when I have made sure that the fields names, types and position match but I have also tried by specifically naming the fields in the same order for each table select * from T1 union select * from T2 union select * from T3 UNION select * from T4;
View Replies !
UNION SELECT Strange Query Result
I want to combine the results of two queries. One query with a combination of tables and one query in one of the tables where the combination does not count for [so that all devices will be shown, and not only devices that will show after the first query, but also devices that are not being showed in the first query]. The query I made: ..........
View Replies !
Unique Results On Join/Union Query
I have 2 tables: Table 1: groupid, groupname etc, Table 2: groupid, firstname, lastname etc; I have these data in table 1: 1, groupname1 2, groupname2 In Table 2 I have: 1, Fname1, lname1, 1, fname2, lname2 1, fname3, lname3 2, Fname1, lname1, 2, fname2, lname2 For each loop, i'm looking for these results: ROW1 Groupname1 + all matching fname,lname using its groupid ROW2 Groupname2 + all matching fname,lname using its groupid
View Replies !
UNION, Individually Works Fine, But Not In Union
Well, I am using an UNION, and while both of the queries works fine while used individually, they don't work while in the UNION. Here is the code - PHP ( SELECT * FROM job WHERE date_fin !=0000-00-00 ORDER BY date_fin ASC ) UNION ( SELECT * FROM job WHERE date_fin =0000-00-00 ORDER BY date_creation ASC ) What do u think guys? Note : MySQL version 4.1.21 Note : Not working means, this part is not working => 'ORDER BY date_fin ASC', means, the returned results are not ordered as I want. MySQL doc. tells, To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT: (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Replies !
Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account. For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done? I run MySQL 4.0.17.
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 !
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 !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
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 !
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 !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Howto Make A Query Based On Another Result?
I need to make query from another query result. tbl_A fields: ID and Date tbl_B fields: ID and FileName Code: Select ID FROM tbl_A where Date>=CURDATE() this 1st_query_result will be any ID with current date from tbl_A. Then I need this 1st_query_result to query the filename which store at tbl_B. something like this: Code: select FileName from tbl_B where ID = 1st_query_result
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 !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page * The code selects all the bookings for a range of time. * It then counts how many concurrent bookings there are for each hour. * If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table. We also notify people if there has been a cancellation This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding! We've ended up with 5 sessions booked In the course of 2 page requests this seems to happen: User1 - submits their booking site selects to see if there's availability ( there is!) User2 - submits their booking site selects to see if there's availability ( there still is!) site inserts User1's booking site inserts User2's booking
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
View Replies !
Not In + Union
got tables brochure_applied, and brochure_content_protected, and brochure_user_protected. each has the column bid. brochure applied also has uid. uid is the reference key (as shown in my example query). i basically wanna get all the bids from brochure_applied that have uid = 3, but remove all the bids that are either in brochure_content_protected and also brochure_user_protected. my query is: select bid from brochure_applied where uid=3 not in ((select bid from brochure_content_protected) union (select bid from brochure_user_protected))
View Replies !
How To Do A Union
I have a query I need to do that combines two queries The structure for the first table is Application_Period CampusID GroupID IndivID This table uses the lookup table Time to get the groupID and IndivID. TimeID Application_Period TimeID matches GroupID and IndivID. How can I combine the two tables into one without the UNION operator?
View Replies !
UNION HELP
I have written a PHP search App and I want it to search between like 8 or so tables in one Database. Here is my union mysql code: $query = "(select * from arcamax where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from drudge where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from google where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from human_events where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from newsmax where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from street where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from townhall where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from weatherbug where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from worldnet where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") order by mailedlist"; Now if I run a search with the above code it does not work...but if I limit the number of tables to two it works fine.
View Replies !
Using UNION
(SELECT id,name FROM table1) UNION (SELECT id,name FROM table2) UNION (SELECT id,name FROM table3) order by name limit 1; this query is works. I can get the fields values. But i cant get the owner table that i read. for example i get this values 3,george (which table has this record???)
View Replies !
UNION On Ver. 3.23
Are there any alternatives to UNION? Running mySQL ver. 3.23 Upgrade is not an option! My query looks like this: (SELECT sum(t.timer) AS sumtimer FROM emply WHERE t.tmnr = 1 AND t.tjobnr = 69 AND t.tfaktim = 2 AND t.tdato BETWEEN '05/7/4' AND '06/2/1') UNION ( SELECT sum(f2.timer) AS fakbare FROM timer f2 WHERE f2.tjobnr = 69 AND f2.tmnr = 1 AND f2.tfaktim = 1 AND f2.tdato BETWEEN '05/7/4' AND '06/2/1')
View Replies !
Union Without Union
I am running version 3.23 of MySQL. It does not allow UNION statements. How can I pull this off without a union. I hear it can be done with a LEFT JOIN SELECT `id`,`name`,`date_of_event` FROM `events` ORDER BY `date_of_event` ASC UNION SELECT `id`,`name`,`date_of_event` FROM `upcoming` ORDER BY `date_of_event` ASC
View Replies !
Whats A Union?
I know how to create a join - correct me if I am wrong, but its something like the following: SELECT contacts.firstname,contacts.lastname,address.line_ 1 FROM contacts,address WHERE contacts.address_hash='$myhashkey' AND address.hash='$myhashkey'; I have seen mixed references in my books about unions and joins that make me think they are in some way related. My Core MySQL book, which I think is great, is unfortunately not clear enough for my head to grasp. I'd appreciate if someone would could give me a real and an imaginery world example (thus sample code and perhaps compare apples and oranges in a shopping list or whatever)...
View Replies !
UNION ALL GROUP BY
I have: SELECT x, count(x) FROM table1 WHERE ... UNION ALL SELECT x, count(x) FROM table2 WHERE... UNION ALL SELECT x, count(x) FROM table3 WHERE ... UNION ALL .... .... and I need to do GROUP BY with all the sentences.
View Replies !
Can You Join A Union
I would like to know if and how it is possible to join the *result* of a union with another table (without first doing individual joins to each part of the result that then will be combined into a union) ?? For example, I would like to do such a join, if possible, to avoid doing something like this: select tableA.col1 as c1 , tableC.col2, tableC.col3, tableC.col4, tableC.col5, ..... from tableA inner join tableC on tableA.col1 = tableC.col1 union select tableB.col1 as c1, tableC.col2, tableC.col3, tableC.col4, tableC.col5, ..... from tableB inner join tableC on tableB.col1 = tableC.col1 The problem with the code above is that I will have a lot of wanted output fields which in the code will be duplicated from tableC in each part of the union statement, and if I in the future will add a field it will then have to be added in two places. Instead of the code above I would like to do extract the output columns from tableC to only one place, with code something like this: select c1 , tableC.col2, tableC.col3, tableC.col4, tableC.col5, ..... from tableC inner join ( select tableA.col1 as c1 from tableA union select tableB.col1 as c1 from tableB ) as VirtualUnionTable on tableC.col1 = VirtualUnionTable.c1 but this does not work (at least not with mysql 4.0.13) and I don't know how to refer to the result of the union and the join column of that union (as you can see above I tried to name it with "as VirtualUnionTable" and then "on... = VirtualUnionTable.c" but it does not work). As I mentioned, the purpose was to eleminate the duplication of all columns from tableC, which I will have to do if I join tableC individually to the selects which are then combined into a union, as in the first example above. Is it even possible to make a join to the result of a union without doing the joins separately first, and can someone show how that kind of code would look like ?
View Replies !
UNION And Optimization?
My question is about a UNION query to deal with an (annoying) JOIN over two tables. I am joining over a double column primary key (where the order of the columns can be changed). This is so slow using a join, but very fast using a union. How come this is? Code:
View Replies !
Union Count
First I'm trying to add together two counts in a union.Code: select count(id) as count from products where catid=2 union select count(id) as count from collections where catid=2 How can I get the sum of those two counts?
View Replies !
Using Union On 2 Tables
i have two tables, "txns" and "proc". "txns" has 100 records while "proc" has 55 records. All records in "proc" table can also be found in "txns" table. I mean all those 55 records in proc are also in txns. (txns - proc) txns=100 records - proc=55 records ------------------ 45 records that have no match the question. What must i do to find those records that dont have a match in txns table and view those unmatched records
View Replies !
UNION Question
I am performing a UNION on 3 tables as follows: SELECT DISTINCT col1_1, col2_2, col3_3 FROM ( SELECT col1, col2, col3 FROM table1 UNION SELECT col1, col2, col3 FROM table2 UNION SELECT col1, col2, col3 FROM table3 ) SORTED_TABLE LIMIT 0, 20 All works well but my question is, is it possible to store the name of the table from which each record in the new table has come from. So I'd therefore be able to see that record x has come from table1 originally.
View Replies !
UNION & SQL_CALC_FOUND_ROWS
I have the following query: SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=0 OR kind=1 ORDER BY rank LIMIT 40,10 it would run slowly because 'OR' breaks usage of indexes. So I rewrote it using the UNION: (SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=0 ORDER BY rank LIMIT 50) UNION ALL (SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=1 ORDER BY rank LIMIT 50) ORDER BY rank LIMIT 40,10 The problem is that FOUND_ROWS() returns just 100 which is the number of rows in a temporal table created by UNION ALL, but I'd like to get the entire number of rows that have kind of 0 or 1
View Replies !
Mysql UNION
I have 2 tables with data i need to loop out to a webpage. Table1 -Field1_ok -Field2 Table2 -Field1 -Field2-er I've tried using... SELECT Field1 AS f1 FROM Table1 UNION SELECT Field1 AS f1 However, not every value in each table is outputted.
View Replies !
ORDER BY With UNION ALL
We have a problem using ORDER BY with UNION ALL. The problem occurs when using UNION ALL between many complex select's, but I'll try to make things more simple, posting smaller select's. When I run the select below, which I call select1, query results are ok, and ORDER BY clause is respected, sorting my query. Code:
View Replies !
UNION Problem
I had this problem in 4.1.7, upgraded to 4.1.11, but am still having the problem. If I look at WinXP Task Manager, MySQL is taking 99% of CPU cycles and grabbing more memory every few seconds. The first two of these SQL statements work. The 3rd one, which contains a UNION statement, hangs. 1) select distinct(Metadata.metadataID),Formats.format FROM Metadata JOIN Formats ON Metadata.MetadataID = Formats.MetadataID WHERE Metadata.Private='0' AND Metadata.MetadataID IN (Select Metadata.MetadataID from metadata where (title like '%pancreas%')); This returned about 700 valid rows for me. 2) Select keywords.metadataID FROM keywords where keyword like '%pancreas%'; This also returns about 600 valid rows. But when I combine the two in the SQL below, MySQL never returns. It just sits there consuming 99% of the CPU cycles and reducing the system to a grindingly slow pace: 3) select distinct(Metadata.metadataID),Formats.format FROM Metadata JOIN Formats ON Metadata.MetadataID = Formats.MetadataID WHERE Metadata.Private='0' AND Metadata.MetadataID IN (Select Metadata.MetadataID from metadata where (title like '%pancreas%') UNION Select keywords.metadataID FROM keywords where keyword like '%pancreas%');
View Replies !
Union Statements
I am trying to create a union statement to join two queries together. one of the fields on one of the queries is a constant, ie because it is from the table i want it to output the letter 'B'. When i try to union this with a variable in another query, i get the error: "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'"
View Replies !
UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days. Notes: Server language is ColdFusion gamedate is type DATE SELECT DISTINCT gamedate FROM schedule WHERE gamedate >= now() UNION ( (SELECT DISTINCT gamedate FROM schedule WHERE gamedate < now() ORDER BY gamedate desc LIMIT 1) ORDER BY gamedate asc LIMIT 3 The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".
View Replies !
Union In Column Name
I created a database, then when I tried to create a table, one of my column names was supposed to be UNION (as in labor union). I kept getting a sytax error until I removed it. Then it worked fine. Anyone know why?
View Replies !
Union In Mysql 3.23
Does mysql 3.23 supports "union" It doesn't accept the syntax saying i have an error near 'UNION' i took the most simple example and still it doesn't work. Example: SELECT Custnum FROM Custnotes UNION SELECT Custnum FROM Note_NoTipul
View Replies !
Union Repeat
which I want to be able to insert into a different database table: The results are in these three variables which ar being looped: myString / typenumber / codenumber How can I insert these loop results into another database table? row by row? Like this: auto_id column1 column2 column3 1 value1(myString) value1(typenumber) value1(codenr 2 value2(mystring) value2(typenumber) value2(codenr) etc.... If I use this: one row is being inserted. Someone said I have to use this but then use the UNION REPEAT..... <CFQUERY NAME="update" DATASOURCE=""> INSERT INTO dev (dev_volgnr,dev_typenr, dev_nr) SELECT '#myString#','#typenumber#','#codenumber#' </CFQUERY>
View Replies !
UNION Issue
I'm trying to display info from 2 different tables in a table on my site. I have the structure set to 7 columns and a count of 30 rows. I can get it to work fine if I just use one select statement with desc limit from 1 table. But what I want is for it to display info from 1 table first that has 20 rows then grab the remaining 10 rows from the other table. I've tried using UNION for this but I keep getting the column error for different column sizes. I'm running mysql 5 with php 5.2.5. Is there a better way of doing this? From what I understood about mysql 5 you could run 2 select statements. But that doesn't seem to work. These are the 2 individual queries that work on their own: $sql = "SELECT * FROM tbl_user tb INNER JOIN tbl_user_2 tbl ON tb.user_id=tbl.user_id WHERE tbl.status > 0 AND tb.user_check='Y' ORDER BY tbl.status DESC"; $sql = "SELECT * FROM tbl_user WHERE user_check='Y' ORDER BY logged_today DESC";
View Replies !
Using Union In A Subquery?
I'm trying to make a query that fetches messages that were created by one of your friends (friendships are stored in a separate table) and was thinking this query would possibly do the trick: SELECT * FROM public_messages WHERE author_id IN ( (SELECT friend_from FROM friendships WHERE friend_to=1 AND pending=0) UNION (SELECT from_to FROM friendships WHERE friend_from=1 AND pending=0) ) Where the user's id is 1... Running this query gives an error of: #1064 - 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 'UNION ( SELECT from_to FROM friendships WHERE friend_from = 1 AND pending = 0 ) ' at line 1 and of course it works fine without the union... Is such a thing possible in one query?
View Replies !
Group By In Union
I need some help with this query, i need to be able to group the combined results by "N_U_R_C". The following query does not work because of the line in red. Quote: select i.id , i.id as substitute_for , i.fd_Date , i.catid , i.Make , i.Model , i.modelGroup , i.Description , i.N_U_R_C as nurc , i.Price , i.pic1 from inventory i, inventory_category c where i.catid=c.id AND c.parent=47 AND i.Model='$model' AND i.Make='$make' AND i.I_IC_C_S!='S' AND i.N_U_R_C!='C') union all (select i_sub.id , i_real.id as substitute_for , i_sub.fd_Date , i_sub.catid , i_af.make , i_af.model , i_sub.modelGroup , i_sub.Description , i_sub.N_U_R_C as nurc , i_sub.Price , i_sub.pic1 from inventory i_real join inventory_alsofits i_af on i_af.itemid = i_real.id join inventory i_sub on i_af.itemid = i_sub.id join inventory_category c_real on i_real.catid = c_real.id where c_real.parent=47 AND i_af.model='$model' AND i_af.make='$make' AND i_real.I_IC_C_S!='S' AND i_real.N_U_R_C!='C' ORDER BY i_real.Make='$make') GROUP BY nurc ORDER BY $order $sortorder LIMIT $limitvalue,$limit
View Replies !
Conditional Union
My objective is to gather all the type groups for a mailing list. I also would like to add to the list an 'Undefined' item for all the users that are ungrouped: 1. I'm trying to make a conditional union, where a union will occur only if the previous condition is true (if any null typeIDs are found) 2. I would like to make one query only [to use with a php function wich only allows single queries] So this is a 'sketch' of my query: SELECT typeID, name FROM pa_users_broadcast_types UNION IF((SELECT COUNT(*) FROM pa_users_broadcast WHERE ISNULL(typeID))>0) SELECT (Ɔ') typeID, ('Undefined') name;
View Replies !
|
TRACKER
