sql

MySQL Certification Guide Practice Qn - Views' Column Names

the question is Which of the following methods for providing explicit names for the columns in a view work? a. Include a column list b. Provide column aliases in the view SELECT statement c. Rename the columns when you select from the view answer a. Works: Include a column list b. Works: Provide column aliases in the view SELECT stat...

how to find out the rank of a row based on a mysql sum?

Hi there, I'm wanting to find the ranking / number of a row. I'm not sure if I'm explaining this well so I'll try. I have the query $sql = 'SELECT SUM(amount) AS total FROM sales ORDER BY total DESC'; $res = mysql_query($sql); while($row = mysql_fetch_array($res)) { // go through and print each row biggest 'total' first echo $row['...

How to fetch result from MySQL row with multiple same-name columns with PHP?

select * from A left join B on A.columnc=B.columnd results returned by above SQL will include both columns of A and B. And what if A and B have some columns with the same name? How to retrieve the value from PHP? ...

SQL query - LEFT 1 = char, RIGHT 3-5 = numbers in Name

Hi everyone I need to filter out junk data in SQL (SQL Server 2008) table. I need to identify these records, and pull them out. Char[0] = A..Z, a..z Char[1] = 0..9 Char[2] = 0..9 Char[3] = 0..9 Char[4] = 0..9 {No blanks allowed} Basically, a clean record will look like this: T1234, U2468, K123, P50054 (4 record examples) Junk d...

Join query from table with multiple foreign keys to same table primary key

I have a workorder system using SQL Express 2008. I have a table called Workorders that has several personnel that are linked to it via UserID. In the Workorder table I have TechID for the Technician, CustomerID for the Customer, QAID for quality assurance. These are linked back to the User Table via UserID (User Table PK). I want to joi...

Selecting only authors who have articles?

I've got two SQL Server tables authors, and articles where authors primary key (AuthorID) is a foreign key in the articles table to represent a simple one-to-many relationship between authors and articles table. Now here's the problem, I need to issue a full text search on the authors table based on the first name, last name, and biograp...

Debugging/tracing embedded sql

Is there a way to trace/log sql from the code (not from the db side, but rather what the code thinks it's doing)? I am using Pro*C/C++: Release 8.1.7.0.0 for Oracle ...

[SQLite3] Get Product Onhand Quantity

Using sqlite3, I have two tables: products, orders. I want to know how many products are left in the shop. SELECT pid, txt, price, qty-coalesce((SELECT SUM(qty) FROM ORDERS WHERE pid=?),0) FROM PRODUCTS WHERE pid=? This works if I select 1 product, I would like a li...

Paging design recommendation for asp.net and sqlserver 2005

I am relatively new to programming. My work basically revolves around data and analysis. I want to create a simple asp.net page which shows huge chunk of data from the database. There could be a millions of rows of data which is used for different kinds of analysis/searchin/filtering etc.. Should I write paging logic at the front end ...

SQL query with 2 grouping

This is a query that is executed without problem with MySql 5.0.51: SELECT cc_split.idSplit, count( voteup.vote), count( votedown.vote) FROM cc_split LEFT JOIN cc_split_usager AS voteup ON voteup.idSplit = cc_split.idSplit AND voteup.vote >0 LEFT JOIN cc_split_usager AS votedown ON votedown.idSplit = cc_split.idSplit...

What kind of DB structure should I use for a site has infinite sub-category?

For example, "Dole Banana" is a kind of product, it's listed under the "Bananas" category, when I open the "Fruits" category, I want to see "Dole Banana". + Food |--+ Fruits |------+ Bananas |------+ Apples |--+ Vegetables |------+ Onion |------+ Spinach ...

Want a Strongly Typed Result from a JOIN in .netTiers

Given a query like: SELECT table1.field1 FirstField, table2.field2 SecondField FROM table1 INNER JOIN table2 ON table1.FK = table2.PK WHERE table1.somefield = 'somevalue'; My objective is to return a strongly typed result set using .netTiers. I assume that I cannot use a view because the WHERE clause needs a parameter, whi...

SQL: cascade UNION and JOIN

Hi I have a union opertaion between two tables SELECT ID_1, name_1, surname_1, FROM T_ONE UNION SELECT ID_2, name_2, surname_2 FROM TABLE_2 now I want to join the result of this JOIN operation with another one table or even with all TABLE_1. How can I handle this new table result of the UNION. for ex after the previous UN...

SQL statement to include rows that have no matches in a correlated subquery

I have the correlated subquery below. Each id (147,148,149) in table1 has many records in table2. The id 148 however has no records that match the time condition in the statement. Therefore it is not included in the output. I want it to be included with a 0 for the count column. What needs to be changed? SELECT b.fkid, COUNT(DISTIN...

kohana quotes in query.

hey, i want to format a date in mysql using DATE_FORMAT(tblnews.datead, '%M %e, %Y, %l:%i%p') i cant seem to get the quotes right , so i keep getting errors. how would you put this in a query? ...

finding max in mysql when column is varchar

I have a varchar column that has data like this top<somenumber> so the word 'top' followed by numbers. I am trying insert a record in this table and am trying to find out what is the next value that should go in. Is there anyway I can find the max value for this column? maybe something like this? select * from catalog ORDER BY CAS...

Insert once, but two records created in MySQL

for($i = 1 ; $i <= 3; $i++) { if(!empty($_POST['fl' . $i])) { $dml = "insert into flAptitude(accountId,language,qualification,certificate) value($accountId,'" . $_POST['fl' . $i] . "','" . $_POST['qualification' . $i] . "','" . $_POST['certificate' . $i] . "')"; mysql_query($dml,$con); file_put_contents("fl$i.txt",...

Is using char as a primary/foreign key a no no?

Consider that there is a bunch of tables which link to "countries" or "currencies" tables. For making data easier to read I'd like make CHAR field with country code (eg US, GB, AU) and currency code (USD, AUD) a primary keys in each of those 2 tables and all other tables will use this CHAR as a foregin key. Database is mysql with innod...

VBScript on checking if the records exist.

I have a table and a text file. Once the records in the table copied into textfile, the records will be deleted. But the table are still in used and will be inserted with a new records from time to time(by another program). I what to do checking on How to make sure that if there are no records in the table, the program will never copy i...

RubyODBC Cannot allocate SQLHENV

I'm trying to connect to SQL Server on Ubuntu 9.04 using Ruby. I translated and followed all the steps outlined in getting OSX talking to SQL Server from here: http://toolmantim.com/articles/getting_rails_talking_to_sqlserver_on_osx_via_odbc Everything is working on the FreeTDS and unixODBC end. I can see and query the database using t...