News nID nTitle ----------- 1 test Keyword kID kWord nID -------------- 1 abc 1 2 def 1 3 ghj 1 So i fetch it like $sql = mysql_query("SELECT * FROM news as n, keyword as k WHERE n.nID = k.nID"); PHP while($row = mysql_fetch_array($sql))...
I am a newbie to SQL, I am using this query to look for the minimum value in the field weight of my table. SELECT product_id, MIN(weight) FROM table WHERE 1; It does show one field with the min value, but only one? But I have many products with the same minimum weight. Is there a way I could specify that I need to show a...
I have a table of items which I'm getting from a few different online stores, like Ebay/Amazon, etc. Before today, I wanted to simply group each item based on the year it was made and the manufacturer who made it. So, the results would look something like this total year manufacturer 100 1999 man_a 32 2002 man_b Now, I wan...
My current method to add the rows together is like so: $totalxp = $row['Attackxp'] + $row['Defencexp'] + $row['Strengthxp'] + $row['Hitpointsxp'] + $row['Rangedxp'] + $row['Prayerxp'] + $row['Magicxp'] + $row['Cookingxp'] + $row['Woodcuttingxp'] + $row['Fletchingxp'] + $row['Fishingxp'] + $row['Firemakingxp'] + $row['Craftingxp'] + $row...
Using SQLite. SELECT c.*, COUNT(m.course_id) AS "meeting_count", COUNT(r.meeting_id) AS "race_count" FROM course c LEFT JOIN meeting m ON m.course_id = c.id LEFT JOIN race r ON r.meeting_id = m.id GROUP BY c.id Course has meetings has races. Trying to select the correct count for course meetings and cours...
I have the following table: I am trying to create an SQL query that returns a table that returns three fields: Year (ActionDate), Count of Built (actiontype = 12), Count of Lost (actiontype = a few different ones) Bascially, ActionType is a lookup code. So, I'd get back something like: YEAR CountofBuilt CountofLost ...
I've written SQL count statements before but I need to write a query which returns 2 sets of count values for a condition. The original query counts the amount of people for a company who have invalid information, fine, works great. I now need to extend this query so that it performs the above operation but also includes the total count ...
I have 3 tables: Users (id, name) Orders (id, userId) Orders_Items (id, orderId, status) At first I wanted to list all users with their respective count of orders like this: A, 1 order B, 5 orders That's easy, I'm doing a "select name, count(id) from users, orders where users.id = orders.userId group by name". Now, I'd like to f...
I have a little problem - i would can get with this query topics posts count and last post id, but i can't figure out, how to get right user id. I get first post (lowest id) user id but i want lastest post... I have tried adding "ORDER BY id DESC" but this will not help. Any ideas how to do it? SELECT COUNT(`id`) AS `count`, M...
I have a table, with these columns: ID | Data How to find out which record has highest ID? ...
Given the following rows of course,section,grade,count of grades within course section: course SECTION grade gradeCount ----------------------------------- 1301 001 C 3 1301 001 C+ 3 1301 001 C- 4 1301 001 D 5 1301 001 D+ 3 1301 001 D- 2 1301 001 F ...
I have a row-vector with arbitrary values. I am interested in the column IDs of the columns which contain a value <= a specified threshold the number of columns which lie below the threshold. Is there a more elegant way to compute this in MATLAB than using a for-loop? ...
Basically, I am querying 3 columns in MySQL, Item_id, Date, and New_Qty. When I use the max function on the date column, the other columns all display their maxes for the same date range. SELECT `item_id` , max(date) AS max_date ,`new_qty` FROM `item_warehouse_link_history` WHERE warehouse_id =1 AND item_id=1000 AND DATE BETWEEN '2010...
I have 2 tables SCHOOLS (ID, SCHOOL_NAME, CITY_ID) STUDENTS (ID, STUDENT_NAME, SCHOOL_ID). I want to list schools in a particular city along with the student count (School Name| Student Count) One way to do this is to have co-related subquery - select sh.school_name, (select count(*) from student where s...
Relational Schema: Employee (Enum, Ename) VentingMac (Enum, Cokename, day) The attribute Enum in VentingMac relation is a foreign key referencing the relation Employee. I want to list out all the employee names (Ename), who drink more than 3 (distinct) coke on the same day (assuming they wont drink the same coke each day) ...
Hi, I've been learning SQL for about a day now and I've run into a road bump. Please help me with the following questions: STUDENT (**StudentNumber**, StudentName, TutorialNumber) TUTORIAL (**TutorialNumber**, Day, Time, Room, TutorInCharge) ASSESSMENT (**AssessmentNumber**, AssessmentTitle, MarkOutOf) MARK (**AssessmentNumber**, **Stud...
hey everybody, I'm struggling to find the solution for the following problem: I want to get all the picture albums which have at least one picture. In SQL i would to something like: SELECT pa.id FROM album pa JOIN picture pi ON pa.id = pi.album_id GROUP BY pa.id HAVING count(pi.id) > 0 But I don't know how to do this in JPQL... Obv...
I have two tables. products id title image_ids --------------------- 1 myproduct 1,2,3 images id title file_name ------------------------- 1 myimage myimage.jpg 2 myimage2 myimage2.jpg 3 myimage3 myimage3.jpg I want to query so that the names of the images are concatenated into a single field for each prod...
Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying flags to a user ... ID contactid flag flag_type ----------------------------------- 118 99 ...
I would like to sum over the product of two columns, but I would like to do it in small sections. That is, for every symbol for every date I would like to sum the product of oi and 'contract_settle`. Here's a snippet of my SQLite db: date symbol cont oi contract_settle 20030103 NVLS1C 100 NA 20030103 NVLS1C 03F 19 3...