sql

trying to inner join multiple tables but always returning 0 rows.

I am trying to figure out why this query returns 0 rows as there is data in all 3 tables. Here are my tables: Table1: Applications Columns: ID, Name Table2: Resources Columns: ID, Name Table3: ApplicationResourceBridge Columns: ID, app_id, resource_id And Here is the query SELECT Resources.name , ApplicationResourc...

Java - escape string to prevent SQL injection

I'm trying to put some anti sql injection in place in java and am finding it very difficult to work with the the "replaceAll" string function. Ultimately I need a function that will convert any existing \ to \\, any " to \", any ' to \', and any \n to \\n so that when the string is evaluated by MySQL SQL injections will be blocked. I'v...

Sql results into php array.

Hi, I would like to create an array (in php) from sql results like this: We have the sql-table "Posts" which stores the Name and the Message.Example: Name | Message John | Hello Nick | nice day George | Good bye John | where What i want is to output the names of people who have posted a message but dont display...

Select all table entries which have a fully capitalized string in a specific column?

I have a database table with a few thousand entries. A part of the entries (~20%) have been entered with a fully capitalized strings in the 'name' column. Example: id | name --------- 1 | THOMAS GOLDENBERG 2 | Henry Samuel 3 | GIL DOFT 4 | HARRY CRAFT 5 | Susan Etwall 6 | Carl Cooper How would an SQL query look like that selects all ...

What should I name a table that maps two tables together?

Let's say I have two tables: Table: Color Columns: Id, ColorName, ColorCode Table: Shape Columns: Id, ShapeName, VertexList What should I call the table that maps color to shape? Table: ??? Columns: ColorId, ShapeId ...

SQL Symmetric Key and opening it from C#

Hi, I am trying to encrypt data in SQL Server via a Symmetric key. When a user submits data through a web form, I would like for the data to be encrypted, using my Symmetric Key that I have saved inside the SQL Server. I am trying to find out how to do this. Currently, I run the following: USE myDb GO OPEN SYMMETRIC KEY myKey DECRYPTIO...

MySQL world database Trying to avoid subquery

I'm using the MySQL WORLD database. For each Continent, I want to return the Name of the country with the largest population. I was able to come up with a query that works. Trying to find another query that uses join only and avoid the subquery. Is there a way to write this query using JOIN? SELECT Continent, Name FROM Country c1 WH...

Trying to convert "yy/mm/dd hh:mm:ss" (stored as char) into datetime using computed column

Hi. This seems like it should be simple but it's driving me up the wall. I have two columns - 'tx_date' and 'time' stored each as char(10). (bad database design I know, but wasn't my design) From a query I can convert them into a datetime just fine - "...convert(datetime,tx_date,time,11)..." (so tx_date "09/11/27" and time "07:12:...

SQL NEXTED QUERIES

you all are so damn good at this sql stuff, I'd rather go get some margaritas than pull my hair out for the next 2 - 4 hours (more) ... !! Can someone help me to get the following queries strung together on this table.. CREATE TABLE [dbo].[TblNdx]( [ticker] [nvarchar](12) NOT NULL, [date] [datetime] NULL, [time] [datetime] NULL, [open] ...

How can I prevent inner SELECT from returning NULL?

How can I prevent inner SELECT from returning NULL (when matches no rows) and force query to fail. INSERT INTO tt (t1_id, t2_id) VALUES ( (SELECT id FROM t1 WHERE ...), (SELECT id FROM t2 WHERE ...) ); Side question: is there better way form this query (t1_id, t2_id are foreign keys, but might be NULL) ? ...

Selecting records in groups by date - possible?

I don't think there is an elegant way to do this, but here goes. Database contains 5000 records with timestamps of 2 years. I need to pull the records under each day of the year. So it looks like.. 09/09/2009 - record938, record2, record493 09/10/2009 - record260, record485, record610 ...etc I cannot use GROUP BY. There are duplicat...

need an sql query

I currently have two tables: 1. car(plate_number, brand, cid) 2. borrow(StartDate, endDate, brand, id) I want to write a query to get all available brand and count of available cars for each brand ...

Parse and Import XML into Table in SQL Server

Hi I've written a CLR assembly that exports a table's data to an XML file. Now I want to import this data into a temp table on another instance. The XML file structure is like this: <row> <SystemInformationID>1</SystemInformationID> <Database_x0020_Version>10.00.80404.00</Database_x0020_Version> <VersionDate>2008-04-04T00:00:00</V...

How to Insert Records based on the Previous Insert?

Here's my procedure: PROCEDURE add_values AS BEGIN INSERT INTO TABLE_A ... SELECT t.id, t.name FROM TABLE_C ("This selection will return multiple records") END While it inserts in TableA, I would like insert into another table(TableB) for that particular record which got inserted in tableA. The columns in TableA and TableB ar...

SQL Query between 3 tables

I have 3 tables (note this may not be the best sql db design) Room: TypeName, RoomNumber RoomType: TypeName, ... Reservation: StartDate, EndDate, TypeName, RoomNumber My input parameters are startdate and enddate. I'd like to know the distinct roomtypes available. From my understanding the solution goes like this: AvailableRoomTypes...

Is it wiser to use a function in between First and Next Insertions based on Select?

PROCEDURE add_values AS BEGIN INSERT INTO TableA SELECT id, name FROM TableC ("This selection will return multiple records") While it inserts in TableA i would like insert into another table(TableB) for that particular record which got inserted in tableA Note:The columns in TableA and TableB are different , is ...

Need very simple 'sequence' for GetNextOrderNumber for SQL Server

I'm trying to make an even simpler function than the one described here to get the next value of an order number for a shopping cart. I don't care if there are gaps Only completed orders get an ID (i.e. I'm deliberately not using IDENTITY) Obviously there must not be duplicates I don't care about performance and locking. If we have so ...

Help writing a complex join query

Hello All, I am having a table orders orders ( id int unsigned not null, fcr_date TIMESTAMP, completion_date TIMESTAMP, factory_no varchar(255), vendor_no varchar(255)) Please ignore the data type typos if any. I want to write a sql query that helps me fetch the data per vendor factory. The data to fetch includes th...

SQL query getting data

In SQL Server 2000: hello i have a table with the following structure: sku brand product_name inventory_count ------ ------ ------------- --------------- c001 honda honda car 1 3 t002 honda honda truck 1 6 c003 ford ford car 1 7 t004 ford ford truck 1 ...

select the first 5 records in a particular order for each foreign key

I have a table with three columns fk_id, sort_column, value_column for each fk__id_ I'd like to retrieve the first 5 records order by the sort_column I've tried this for several hours now and I don't have the slightest idea on how to do it I'm using MySQL I'd be grateful for any help Marc EDIT: I should probably clarify the outp...