Update Query Joining Tables From Separate Databases
Apr 17, 2008
In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID
In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID
I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.
How can I do this in SSIS?
Assumptions:
linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments.
TIA.
-El Salsero
View 5 Replies
ADVERTISEMENT
Jan 24, 2006
HiI'm working on an ASP project where the clients want to be able toeffectively perform SELECT queries joining tables from two differentdatabases (located on the same SQL-Server).Does this involve creating virtual tables that link to another database, oram I completely on the wrong track?Any hints as to where I might find more information (buzz-words, etc.) wouldbe most appreciated.Thanks
View 10 Replies
View Related
Nov 19, 2007
Hello,I am a beginner with SSIS. I have a scenario ...
I have source DB 'MySource' and a destination DB 'MyDestination'
I have created a SSIS to push data from my source data base to detination DB.
I have a scenario where in i have to push data from the source DB to the Destination DB , depending on a join condition for which the data is in Destination.
I would not be able to acces both the DBs while writing query to pick data from .. source DB. How do iproceed ..
let me give an exmple
Source DB:
Table1
---col1
--col2
--col3
--
--
--coln
Destination DB;
Table2:
--col1
--col2
--col3
--
--
-- col n
Table 3:
--col 1
--col 2
-- col 3
Now i Have to move data to table 3 into destination DB getting all the records which pass a join on table1 and table2 on cloumns 1 ,2 and 3 ..
I have only read access on the the Source DB.. pls let mw know how i can solve this scenario in SSIS.Thanks and Regards,Raja Tirumala Rao
View 3 Replies
View Related
Apr 29, 2015
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
View 6 Replies
View Related
Apr 10, 2008
Hi,
Im trying to access data from a database on another server in a SQL 2005 query.
use Bury2k29.ServiceDeskForms
select .......
but I get the message
could not locate entry in sysdatabases for database 'Burky2k29'. No entry found with that name. Make sure that the name is entered correctly.
Bury2k29 is the name of the server, and ServiceDeskForms is the database I want to access.
When I open a blank query and enter only the code to access that database it runs fine.
Any ideas?
View 4 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Mar 15, 2007
I know how to join 2 tables, but I have a third I need to insert. For some reason, this doesn't work:
Code:
$rows = ff_select(
"select ".
"u.email as email, ".
"c.user_id as user_id, ".
"u.name as name, ".
"r.age as age ".
"from #__comprofiler as c ".
"left join #__users as u on c.user_id = u.id ".
"left join #__rnr_contest as r on c.user_id = r.userid ".
"where (r.age != chicken) and (r.age != nystrip) and (r.age != regrets) and (u.block = 0) and (c.cb_contactmethod LIKE '%Email%') and (u.usertype != 'Super Administrator') and (u.email != 'please@change.com') and (u.username != 'guest') and (u.username != 'piedmont') ".
"order by email"
anyone see why? It tells me that "chicken" is not a column which is weird because I don't think it's listed as a column in my query... is it?
View 3 Replies
View Related
Jul 23, 2005
Select LOCGeneralHave.LSCH, LOCSubClassHave.LSCH from LOCGeneralHave ,LOCSubClassHave Where (LOCGeneralHave.LCNT <> '0' andLOCSubClassHave.LCNT = '0')This query seems to be ignoring the 'and' part of the clause.Basically I want select from table1, table2 where LCNT in table1 is not0 andLCNT in table2 is 0.I have verified the LCNT's numbersThis query returns 2 columns with 1700 rowsIt needs to only find a few rows.What am I missing, any ideas, thanks for any help.
View 4 Replies
View Related
Mar 19, 2015
I have four tables:
a, b, c and d
table a is related to table b by a foreign key. table b is related to c and so on.
I used the sql statement below to join the tables:
$result = mysql_query("SELECT a.colum1, a.column2,
b.column, c.column, d.column FROM a
JOIN b ON a.pkey = b.foreign key
JOIN c ON b.pkey = c.fkey
JOIN d ON c.pkey = d.fkey ")
or die(mysql_error());
[Code] .....
I succeeded in printing out the first record where the four tables are joined, but not other instances.my print out is this:
a.column1
a.colum2
b.colum
c.colum
d.colum
But there are about ten instances where the joining conditions are met. How do I print out all the records that have met the condition?
View 2 Replies
View Related
Jul 20, 2005
Two tables:T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 isthe primary key.T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factornumeric(18,2))--x1 is the primary key. T2 contains non-overlappingvalues. So for eg., a few rows in T2 may look like.1, 51, 51.999, 512, 52, 52.999, 52........32, 82, 82.999, 82........T2 is basically a lookup table. There is no relationship between thetwo tables T1 and T2. However, if the TestVal from T1 falls in therange between FromVal and ToVal in T2, then I want to updateResultFactor in T1 with the corresponding value of Factor from the T2table.------Example for illustration only---------------Even though tables cannot be joined using keys, the above problem is avery common one in our everyday life. For example T1 could beemployees PayRaise table, c1=EmployeeID, with "TestVal" representingtest scores (from 1 to 100). T2 representing lookup of the ranges,with "Factor" representing percent raise to be given to the employee.If TestVal is 65 (employee scored 65% in a test), and a row in T2(FromVal=60, ToVal=70, Factor=12), then I would like to update 12 intable T1 from T2 using sql;. Basically T2 (like a global table)applies to all the employees, so EmpID cannot serve as a key in T2.---------------------------------------------------------Could anyone suggest how I would solve MY PROBLEM using sql? I wouldlike to avoid cursors and loops.Reply appreciated.Thanks
View 1 Replies
View Related
May 16, 2015
I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:
SELECT A.col5, SUM(C.col6) AS col6sum
FROM TableA AS A
INNER JOIN TableB AS B
ON A.col1 = B.col1
INNER JOIN TableC AS C
ON B.col2 = c.col2
WHERE A.col3 = constant 1
AND B.col4 = constant2
GROUP BY A.col5;
The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."
Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:
1. When A Join B, Left: A, Right: B.
  When Join C, Left: result of A join B, Right: C
2. When A Join B, nbsp; Â
When Join C, nbsp;When A Join B, nbsp; Â
When Join C, nbsp;When A Join B, nbsp; Â Â
When Join C, "line-height:13.5px;">
Q2:Â The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."
How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.
View 4 Replies
View Related
Feb 15, 2012
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
View 14 Replies
View Related
May 17, 2013
I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?
select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id
There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.
View 9 Replies
View Related
Feb 22, 2006
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
View 7 Replies
View Related
May 30, 2008
I need to do a inner join on tables from two separate databases. I understand that you can do this by using this type of syntax:
select a.col1, b.col2from db1.dbo.tab1 a, db2.dbo.tab2 bwhere a.col1 = b.col2however, how do I reference the two databases in the following code?
Thanks,
Tim
Function GetConnectionString() As StringDim ConnectionString As String = ConfigurationManager.ConnectionStrings("MainWeb").ConnectionString
Return ConnectionString
End Function
Using conn As New SqlConnection(GetConnectionString())
conn.Open()
Dim sql As String
sql = "SELECT CaskInfo.CaskID, CoCInfo.CoCName, AmendmentInfo.AmendmentName FROM CaskInfo INNER JOIN CoCInfo ON CoCInfo.CoCID = CaskInfo.CoC INNER JOIN AmendmentInfo ON AmendmentInfo.AmendmentID = CaskInfo.Amendment WHERE "For i = 0 To UBound(words)
If i > 0 Then sql = sql + " OR "
sql = sql + "(CoCInfo.CoCName + ' ' + AmendmentInfo.AmendmentName) LIKE '%" + words(i) + "%'"
Next
' lblResults.text = sql' Exit Sub
Dim com As SqlCommand = New SqlCommand(sql, conn)
Dim result As SqlDataReader = com.ExecuteReader()
Dim SearchResults As StringWhile result.Read()
SearchResults = SearchResults + result.GetInt32(0).ToString + " " + result.GetString(1) + " " + result.GetString(2) + "<br>"
End While
result.Close()
lblResults.text = SearchResults
conn.Close()
End Using
View 5 Replies
View Related
Jul 21, 2000
How do you select data from 2 separate databases residing on 2 servers?
Is there a way?
Angel
View 1 Replies
View Related
Mar 15, 2006
In Enterprise Manager, is there a way to group Databases into Separate folders?
View 6 Replies
View Related
Nov 23, 2005
I have two databases with multiple tables. Same tables same fileds.Both databases contain records, but they should not match each other.(I will run a report for matches before this and delete any data thatis not current to ensure this is the case)How do I go about moving data from one of the databases to the other inorder to create a single database with all the data? Unfortunately mySQL is limited and I cannot image how I would work around the uniqueids. Append or update maybe?Thanks in advanceAndrew
View 1 Replies
View Related
Jan 4, 2008
I would like to write a query that will join two tables in separate databases (same server). Then update a field in one table using data and criteria from the other.
Its easy to do this in Microsoft Access by linking the tables using ODBC, but I found its not so simple to do directly in SQL Server
Thanks.
View 2 Replies
View Related
May 13, 2004
I need to Query tables in 2 databases after getting a connection. I cannot make this piece of code to work. In db2, oracle this type of code works.
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sys = “jdbc:microsoft:sqlserver://192.168.0.152";
conn = DriverManager.getConnection(sys, "name", "pswd");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.jobs");
rs = stmt.executeQuery("SELECT * FROM mydb.myfile");
If I run this I get invalid object “pubs.jobs” error.
Any ideas?
Thanks.
View 4 Replies
View Related
Jan 11, 2007
Hey all,
I want to run a query that returns the count of records returned by two other queries. Having much trouble with this... I'm sure it's just a triviality. Thanks in advance...
View 2 Replies
View Related
Jun 23, 2006
I have an existing database with approx 500,000 rows and accessed by afew hundred users per day creating approx 1,000 new records per dayplus typical reporting - relatively low volume stuff for SQL Server.I'm about to add a process that will be importing data daily fromlegacy databases and summarizing it for reporting purposes, integratingit with the existing database. This volume of data will be considerablyhigher, perhaps 100,000+ rows per day, which will be deleted once ithas been summarized and the results written to some intermediatetables.Is there any concern about mixing different levels of volume within onedatabase? As I'll be creating lots of rows daily and then deleting themI was wondering about fragmentation, transaction logging etc. andwhether having this processing in a separate database from the mainapplication would be 'better'.
View 3 Replies
View Related
May 15, 2015
We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?
We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).
View 3 Replies
View Related
Oct 19, 2015
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies
View Related
Nov 30, 2005
is it possible to do something likeUPDATE Table1, Table2 INNER JOIN Table2 ON Table1.ID=Table2.ParentID SET Table1.Name=xxx, Table2.Address=yyyI want to update at same time the Asp.net Users table and a "UsersDetails" table, sharing the same UserID key.
View 4 Replies
View Related
Jun 14, 2008
hi friends
in my sqlserver 2005 i want to update two tables in a single query.
is it possible? or is it possible by creating a view?
thanks in advance.
shweta
View 3 Replies
View Related
Jul 23, 2005
I am looking for some assistance with an update query that needs to link 3tables:This query ran and reported over 230,000 records affected but did not changethe field I wanted changed, not sure what it did.I did notice that the "name" in "GM_NAMES.name" was colored blue in QueryAnalyzer. Is it bad to name a column "name"?UPDATE ABSENCESset CustomerContactID = cicntp.cnt_idfrom absences, cicntpwhere (SELECT cicntp.cnt_l_nameFROM cicntp INNER JOINgm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOINAbsences ON cicntp.cnt_id = Absences.CustomerContactID)Next I tried this query which is still running after 75 minutes (on alaptop)update absencesset CustomerContactID = cicntp.cnt_idfrom absences, cicntp, gm_nameswhere gm_names.name= cicntp.cnt_l_nameAs you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.Absences.CustomerContactID is what I need updated, when finished it shouldmatch CICNTP.cnt_idGM_NAMES is a temp table and matches records in CICNTP.cnt_l_nameCan some of you school this newbie on the best way to do this?Thanks a bunch!
View 4 Replies
View Related
Mar 3, 2001
I am new to T-SQL and triggers
Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert
AS
Update customer SET cust_id =( SELECT
Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id))))
+ Convert (varchar(10),i.id)
+ Substring(i.lastname,1,3)
+ Substring(i.firstname,1,1)
from employee C INNER JOIN inserted i on i.id=c.id)
from employee C INNER JOIN inserted i on i.id=c.id
Saad
View 2 Replies
View Related
Apr 29, 2014
I have two tables
Books
-BookID
-CustomerID
Magazines
-MagazineID
-CustomerID
How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:
CustomerID, BookCount, MagazineCount
View 3 Replies
View Related
Aug 18, 2007
I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views.
My scripts are as follows:
Main View
SELECT
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER,
--TotalPayable
IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal
WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalPayable,
--NetPremium
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS NetPremium,
--IPT
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS IPT,
--Fee
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS Fee,
--TotalCommission
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalCommission
FROM
ACCOUNTS_CLIENT_TRAN_LINK
INNER JOIN ACCOUNTS_TRANSACTION
ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID
INNER JOIN ACCOUNTS_TRAN_BREAKDOWN
ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID
INNER JOIN LIST_TRAN_BREAKDOWN_TYPE
ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID
INNER JOIN CUSTOMER_POLICY_DETAILS
ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND
CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID
INNER JOIN SYSTEM_INSURER
ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID
INNER JOIN SYSTEM_SCHEME_NAME
ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID
INNER JOIN CUSTOMER_INSURED_PARTY
ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND
ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID
WHERE
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY'
GROUP BY
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
ACCOUNTS_TRANSACTION.Transaction_ID,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER
Add on View 1
CREATE VIEW TOPCARDPA AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01'
Add on View 2
CREATE VIEW TOPCARDRESC AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01'
Join Result Script
SELECT
TOPCARD.AUTHORISATIONUSER,
TOPCARD.AUTHORISATIONDATE,
TOPCARD.TRANSACTION_CODE_ID,
TOPCARD.SURNAME,
TOPCARD.INSURER_DEBUG,
TOPCARD.SCHEMENAME,
TOPCARD.POLICYNUMBER,
TOPCARD.TotalPayable,
TOPCARD.NetPremium,
TOPCARD.IPT,
TOPCARD.Fee,
TOPCARD.TotalCommission,
TOPCARDPA.SELECTED,
TOPCARDRESC.SELECTED
FROM
dbo.TOPCARD TOPCARD
INNER JOIN dbo.TOPCARDPA TOPCARDPA
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND
TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID
INNER JOIN dbo.TOPCARDRESC TOPCARDRESC
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID
AND
TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID
I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.
View 2 Replies
View Related
Apr 29, 2007
Hi. I'm new to SQL, and need to join 2 tables... any hints???
table1:id (int)title(varchar(50))body(text)
table2:id (int)title(varchar(50))body(text)
somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information:
table1:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body"
table2:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body"
I would like to get...
id table title body3 1 "third title" "third body"3 2 "third title" "third body"2 1 "second title" "second body"2 2 "second title" "second body"1 1 "first title" "first body"1 2 "first title" "first body"
Does anyone know how to get this? I am fairly flexible if i need to change things...
cheers, eh!
View 1 Replies
View Related
Aug 30, 2007
Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec
View 11 Replies
View Related
Jan 14, 2008
Hello all,
I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do???????
Mahmoudona
View 4 Replies
View Related
