Hi Experts, I'm tryin to join two tables. The problem i'm having is that one of the columns i'm trying to join on is a list. So is it possible to join two tables using "IN" rather than "=". Along the lines of SELECT ID FROM tableA INNER JOIN tableB ON tableB.misc IN tableA.misc WHERE tableB.miscTitle = 'help me please' table...
We're using a "1 audit table for each monitored Table"; However, in our case emp(PARENT) table has a child table emp_address which also needs to be monitored, so we have emp_audit and emp_address_audit tables. postgres audit SQL : how to join PARENT and CHILD tables for reporting purposes. /* Employee table */ create table emp ( ...
INSERT INTO GIADM.GI_TRANSFER_EMP(TRANSFER_EMP_ID,USER_ID,EMP_LAST_NAME, EMP_FIRST_NAME,EMP_ACTIVE_FLAG) VALUES (select a.USER_ID as transfer_emp_id, u.user_id as user_id, u.user_last_name as emp_last_name, u.USER_FIRST_NAME as emp_first_name, u.USER_ACTIVE_FLAG a...
I'm curious on how some other people have handled this. Imagine a system that simply has Products, Purchase Orders, and Purchase Order Lines. Purchase Orders are the parent in a parent-child relationship to Purchase Order Lines. Purchase Order Lines, reference a single Product. This works happily until you delete a Product that is refe...
I am trying to create a calculated field in one of my database tables, but I keep getting the error Error Validating the formula for column FullName I am not trying to incorporate other calculated fields so this should work. I'm using SSMS 2008 R2 with a SS2005 back end. So in the formula field I've tried: Trim([dbo].[Contact...
I have a script that needs to insert 50+ rows into a table, is there a way to loop though each row I want to insert, rather than coding this below statement 50 + times in TSQL? IFEXISTS ( SELECT 1 FROM table where column 1 = ) UPDATE table Column1 = value, Column2 = value, Column3 = value, Column4 = value WHERE c...
i would like a linked datasheet (table) to open once access is opened. how do i do this? ...
I know that within a trigger - at least for SQL Server - one should never assume that the inserted table has just one row, which means SQL like this in a trigger is usually bad: select @UserID = ID from inserted But out of curiosity, can a set of INSERT statements ever result in an inserted table of more than one row? I know it's easy...
I have a random question. If I were to do a sql select and while the sql server was querying my request someone else does a insert statement... could that data that was inputted in that insert statement also be retrieved from my select statement? ...
Trying to execute an update of a single field where the current db wants to pull values from last night's backup. Should be something close to: update myTable set status = (select status from .lastBackup.dbo.myTable as t where t.idField = idField) Help? ...
I'm building a query that has a bunch of optional parameters, some of which are Table-Valued Parameters. The problem that I'm facing is how to most efficiently use the TVPs in this query? Each TVP has the type: TABLE( [variable] nvarchar(30)) I know that I could typically: INNER JOIN @TVP to filter out anything that is not in the ...
I am trying to do an inexact join (I am not sure what the proper term is) where I could perform pattern matching. Basically, instead of doing a JOIN like this: .... JOIN .... ON (t1.col = t2.col) I would like to do do something like: .... JOIN .... ON (t1.col ILIKE %(t2.col)% ) The second example is obviously not proper syntax. Is ...
Database : SQL Server 2005 Problem : Copy values from one column to another column in the same table with a billion+ rows. test_table (int id, bigint bigid) Things tried 1: update query update test_table set bigid = id fills up the transaction log and rolls back due to lack of transaction log space. Tried 2 - a procedure on fol...
I am using Oracle SQL Developer. I am using parameters &TableName My query comes back to me in the results with an OLD: tag before it, and again with a New: tag (the variable is replaced with the value that I have typed in) and then my results follow this. How do I get rid of this annoying return and change it to just display my resul...
Query SELECT ppc.personid, ppc.category, ppc.phonenumber, ppc.createts FROM person_phone_contacts ppc WHERE CURRENT_TIMESTAMP BETWEEN ppc.createts AND ppc.endts AND CURRENT_DATE BETWEEN ppc.effectivedate AND ppc.enddate ORDER BY ppc.personid, ppc.category, ppc.createts DESC Resulting Data 3742 | Home | xxx-xxx...
Here's a fictional scenario with some populated data. For tax purposes, my fictional company must retain records of historical data. For this reason, I've included a version column to the table. TABLE EMPLOYEE: (with personal commentary) |ID | VERSION | NAME | Position | PAY | +---+---------+------------+----------+-----+ | 1 | ...
In the application development there is a concept of defensive programming. How to implement defensive programming techniques and writing robust code using Transact-SQL? ...
I have a table that contains multiple records per employee by effective data and effective sequence: EMPLID, EFFDT, EFFSEQ. I am trying to come up with a view in which I could specify an As Of Date other than the current date and get the most current record for each employee as of that date. The query that I usually use is: SELECT C.E...
I want to write a query in T-SQL to perform a search on two concatenated columns. The two columns are fname and lname. Here is what I have so far: SELECT fname, lname, ... FROM users JOIN othertable ON foo=bar WHERE fname+' '+lname LIKE '%query%' SQL server doesn't like that syntax, though. How do I structure t...
Hi, I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ? So considering performance and other aspects when and why should I prefer one over the other ? ...