I am trying to do a check to see if a student meets the prerequisite before they can register for a course section. CourseSecID = 27 (ID of the course section the student wants to register for.) What I am doing is querying the student's acad cred records to see if any of the courses matches the prereq that is connected to the course they are wanted to register for. If it matches, then they have met the prereq and can then register for the course section. Below is my query and any help or direction is greatly appreciated. The …

Member Avatar
Member Avatar
+0 forum 4

Hi.. i want to get all columns from tbl_class where course_id of tbl_course is equal to course_id of tbl_class more over course_id of tbl_course should be thw one who has semester = 5 brife structure of tables are: tbl_class >class_id >course_id >class_room tbl_course >course_id >semester i have design this kind of querry logicaly as m not good in sql but it doesnt work it return multiple values from subquerry that is i am getting 2 course ids now i want * from tbl_class where course_id = result by subquerry SELECT * FROM tbl_class WHERE course_id = (SELECT course_id FROM tbl_course …

Member Avatar
Member Avatar
+0 forum 2

I came up with this query now when i tried to create a view it failed to create SELECT aDate FROM ( SELECT @maxDate - INTERVAL (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) DAY aDate FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a, (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION …

Member Avatar
Member Avatar
+0 forum 6

I'm trying to select rows from a table that meet a bunch of criteria, but one of the criteria is that a row doesn't already exist with two matching columns both being true at the same time. For example, suppose table t1 has columns: a, b, c and d Here's what I'm trying, but it's giving me a syntax error ... SELECT * FROM t1 WHERE a, b NOT IN ( SELECT a, b FROM t1 WHERE c = 5 AND d = 6 ) AND b NOT IN (1, 2, 3, 4)

Member Avatar
Member Avatar
+0 forum 6

Hello buddies , I have no Idea what is subquery, every time I tried to link two tables in one sql query I failed here is the scenario I want: in this pic I have posts with "**user_id**" only I need to show the username which stored on another tables called users I've tried using but its not working. $sq= "SELECT user_id, username FROM users WHERE user_id IN (SELECT user_id FROM posts WHERE user_id= '".$id."')"; posts table ![111111](/attachments/small/3/111111.jpg "align-left") ![222222](/attachments/small/3/222222.jpg "align-left") this is the users tables

Member Avatar
Member Avatar
+0 forum 7

SELECT t.* FROM (SELECT * FROM jobs WHERE `status` IN (0,1,2,3) ORDER BY `status` DESC, job_datetime LIMIT 11, 20) AS t That's a cut down sample of my sql. It was more complicated, but I've pared it down to this just to see why I was getting some strange numbers for pagination (e.g. 10 records per page). The above returns 12 records. With the LIMIT clause on the outside, it works as expected, but that's not something I'm able to do as I need to limit the subquery for additional joins later on. Can somebody shed some light on this …

Member Avatar
Member Avatar
+0 forum 3

Hi everyone, Trying to do a subquery to select the minimum price from table1, table what holds 15 million rows. I think that my approach is not the most suitable way of doing this. I want to select after oen_norm because oen_norm may have 1-10 rows per entry, but I need the smallest price for each oen_norm. Could you please help on this one ? I have indexes set up, but that doesnt seem to help for this particular query. SELECT * FROM db.table1 as a WHERE a.quality IN (1,2,3) AND a.price = (SELECT MIN(price) FROM db.table1 WHERE oen_norm = …

Member Avatar
Member Avatar
+0 forum 6

I have a query in which I need to display some group function figures SUM COUNT etc from a joined table. I also need to select the latest version (by datetime) of a matching record from a separate table, which I can do using an ordered subquery in the join. My issue is that the group functions appear to be joining on every row in the subquery (not just the latest one) and thus displaying incorrect inflated results of the SUM/COUNT functions. So I guess what I'm looking for is one of the following: 1) a better way to do …

Member Avatar
Member Avatar
+0 forum 3

Hi thought i'd try here see if anyone can help solve this quicker. First off we have a email database. The main table is the email data, all unique rows per email address with a unique id. eg. emailid,email 1,email@example.com 2,email@example.co.uk Next we have another table which logs the data of each time an email has been attempted to send such as incid,emailid,sent,campaign_id,datesent 1,1,1,send1,2012-02-20 2,2,0,send1,2012-02-20 3,1,1,send2,2012-02-27 4,2,1,send2,2012-02-27 The part i'm having trouble with is a query that pulls the next emails a campaign is up to [CODE=mysql]( SELECT * FROM `the_main_table` as `mt` LEFT JOIN `sentdata` AS `sd` ON mt.emailid …

Member Avatar
Member Avatar
+0 forum 20

SELECT * , 3956 *2 * ASIN( SQRT( POWER( SIN( ( 122.4058 - ABS( dest.lat ) ) * PI( ) /180 /2 ) , 2 ) + COS( 122.4058 * PI( ) /180 ) * COS( ABS( dest.lat ) * PI( ) /180 ) * POWER( SIN( ( 37.7907 - dest.lon ) * PI( ) /180 /2 ) , 2 ) ) ) AS distance FROM members dest HAVING distance <25 Above query run perfectly .. Users will have one field name search_distance .. So i want to calculate users location and current location distance based in search_distance too.. Please …

Member Avatar
Member Avatar
+0 forum 1

I created a stored procedure as below: [CODE] DELIMITER$$ CREATE PROCEDURE updateCorporateDetails(IN companyName VARCHAR(30), IN rocNo VARCHAR(20), IN address VARCHAR(30), IN postalCode INT, IN state VARCHAR(20), IN country VARCHAR(20), IN contactNo VARCHAR(20), IN email VARCHAR(20), IN fax VARCHAR(20), IN lastUpdate DATETIME, OUT total INT) BEGIN DECLARE var1 INT; SET @var1 = (SELECT id FROM vwRetrieveCorporateDetails WHERE companyName=companyName AND rocNo=rocNo AND country=country); SELECT @var1; END$$ DELIMITER; [/CODE] Then I attempted to call the stored procedure as below: [CODE] call updateCorporateDetails('Tanja','444','No. 6 Underground Street',33333,'SEL','MY', '012-6666666','admin@tanjong.com','03-6666666',now(),@total); [/CODE] Guess what I keep getting the following error: "Subquery returns more than 1 row" But the …

Member Avatar
Member Avatar
+0 forum 5

I have a query which generates a soccer standings table and although it works fine there I am trying to implement a couple of variations on it but everything I have tried has failed. I have searched and asked many times and for both problems and it seems as though a subquery would be the solution, but every attempt I have made has not given the desired result. An example of the output can be seen at [URL="http://www.margate-fc.com/content/test/standings.php?season_id=103"]http://www.margate-fc.com/content/test/standings.php?season_id=103[/URL] for the 2010 season and [URL="http://www.margate-fc.com/content/test/standings.php?season_id=104"]http://www.margate-fc.com/content/test/standings.php?season_id=104[/URL] for the 2011 season. I have also attached a dump of the relevant tables in a …

Member Avatar
Member Avatar
+1 forum 3

Hi, Although I'm not really a novice when talking about web development, I'm not really used to writing MySQL queries that are a bit more advanced than the absolute basic SELECT/INSERT/UPDATE/DELETE queries. Anyway, currently I started working on a project that shows relevant items to the user: A 'you might also like' screen, or another example, a 'people who bought this product also purchased this product' function. In my case, the user can 'favorite' an item and see which other items might be interesting to him/her. To achieve this, I came up with the following query: [CODE]SELECT item FROM likes …

Member Avatar
Member Avatar
+0 forum 3

The End.