Help With Three Table Join Query.
Apr 4, 2008
I feel like I'm a join away, here... I have tables for servers, databases, and backups. What I want to know is, what was the the most recent backup date and type for each database?
/* Tables */
CREATE TABLE [dbo].[Servers](
[ServerID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServerID] DEFAULT (newid()),
[ServerName] [nvarchar](60) NOT NULL,
CONSTRAINT [PK_Servers_ServerID] PRIMARY KEY CLUSTERED ([ServerID] ASC),
CONSTRAINT [UNIDX_Servers_ServerName] UNIQUE NONCLUSTERED ([ServerName] ASC)
)
GO
CREATE TABLE [dbo].[Databases](
[DatabaseID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DatabaseID] DEFAULT (newid()),
[ServerID] [uniqueidentifier] NOT NULL,
[DatabaseName] [nvarchar](100) NOT NULL,
[RecoveryModel] [nvarchar](11) NOT NULL,
[OwnerUID] [nvarchar](30) NULL,
CONSTRAINT [PK_Databases_DatabaseID] PRIMARY KEY CLUSTERED ([DatabaseID] ASC),
CONSTRAINT [UNIDX_Databases_DBName_ServerID] UNIQUE NONCLUSTERED ([DatabaseName] ASC, [ServerID] ASC)
)
GO
ALTER TABLE [dbo].[Databases]
ADD CONSTRAINT [FK_Databases_Servers] FOREIGN KEY([ServerID])
REFERENCES [dbo].[Servers] ([ServerID])
GO
CREATE TABLE [dbo].[Backups](
[BackupID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BackupID] DEFAULT (newid()),
[DatabaseID] [uniqueidentifier] NOT NULL,
[BackupDate] [datetime] NOT NULL,
[BackupType] [char](4) NOT NULL,
CONSTRAINT [PK_Backups_BackupID] PRIMARY KEY CLUSTERED ([BackupID] ASC)
)
GO
ALTER TABLE [dbo].[Backups]
ADD CONSTRAINT [FK_Backups_Databases] FOREIGN KEY ([DatabaseID])
REFERENCES [dbo].[Databases] ([DatabaseID])
GO
/* Some Sample Data. */
INSERT INTO [Servers]
SELECT 'B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','AEPPROD03'
GO
INSERT INTO [Databases]
SELECT '03FC0DF7-163E-45DC-A837-09B14F3740A4','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','msdb','SIMPLE','sa' UNION ALL
SELECT 'DD0757B9-D1BE-4407-ABE0-24625CE65076','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','model','SIMPLE','sa' UNION ALL
SELECT 'B9232B6F-3E09-480B-8658-4FE9ED2CC387','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','master','SIMPLE','sa' UNION ALL
SELECT '9C5CC23D-72D8-40CC-B1B0-665CD5F27120','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_RedFlag','FULL','MM04519' UNION ALL
SELECT '13055420-D791-4802-B75E-ADAB4C022BE7','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_ProductionData','BULK_LOGGED','M M04519'
GO
INSERT INTO Backups
SELECT '94539C6E-C459-42B1-928C-02E94BA2D230','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 2:04AM','Tran' UNION ALL
SELECT '24CE98B1-C737-46EF-92D2-048309DF192B','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 8:04PM','Tran' UNION ALL
SELECT '6521DA25-BB6C-4407-964A-09BBEAB8978D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 8:04AM','Tran' UNION ALL
SELECT 'B0CB6502-F022-4F46-9994-177BB61E082F','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 12:04PM','Tran' UNION ALL
SELECT '69C7078B-F5C3-4805-815C-1D3F6450628E','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 6:04AM','Tran' UNION ALL
SELECT '8C6D591E-8633-409C-8585-231B3C0920FC','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 8:04AM','Tran' UNION ALL
SELECT 'A7216995-BC83-48CF-B5E6-27EFF2E8A841','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 6:04PM','Tran' UNION ALL
SELECT 'A2537957-E601-4BCE-A23B-317021219BA0','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 8:04PM','Tran' UNION ALL
SELECT '4A23B5C6-65A4-483D-8F70-32B2D459769A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 4:04PM','Tran' UNION ALL
SELECT '8812B10D-B2F0-48A1-ACEF-451FC1572D18','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr 4 2008 2:40AM','Full' UNION ALL
SELECT '60C80D58-5FDC-41E4-A529-4CBA6A04B4EA','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 2:04PM','Tran' UNION ALL
SELECT 'B7D4F3FF-DDC9-4BD3-8AE8-4CD9C4C31375','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 2:04AM','Tran' UNION ALL
SELECT '1A2C3054-5563-465C-9DF8-53A154DB4DF1','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 12:04PM','Tran' UNION ALL
SELECT '90C4F821-3251-482D-B103-566F7AA72CBE','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 6:04PM','Tran' UNION ALL
SELECT '53F9773E-9F09-44E8-9BC8-6F253D66106C','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 2:04PM','Tran' UNION ALL
SELECT 'F60C2566-44C4-495E-80BA-76831048451D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 10:04PM','Tran' UNION ALL
SELECT 'FBF29499-D4ED-4AB0-BBE8-76FFA3CC6737','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr 3 2008 2:40AM','Full' UNION ALL
SELECT '88C1F74E-CB0E-4CEC-9720-8D47558AAC5A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 12:04AM','Tran' UNION ALL
SELECT '45DD22A0-16A0-4BEE-942E-8FA27DC52C31','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr 4 2008 2:10AM','Full' UNION ALL
SELECT 'F9F2EEB2-EFAD-4229-994C-9F175269AF76','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 10:04AM','Tran' UNION ALL
SELECT 'FE754924-E31A-43BC-8BD3-B3CA0EA02633','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 10:04AM','Tran' UNION ALL
SELECT '8C692625-B071-4A12-A620-B7A4A56AC1B7','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 4:04AM','Tran' UNION ALL
SELECT '62FBC73D-49C3-4572-A017-C025ACBF0948','13055420-D791-4802-B75E-ADAB4C022BE7','Apr 3 2008 1:21AM','Diff' UNION ALL
SELECT '938DF985-C0E4-41E7-9510-C02FFE06F186','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 10:04PM','Tran' UNION ALL
SELECT 'F19BD5F6-0FD4-4BD2-91DA-D03CF9719124','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 3:04AM','Full' UNION ALL
SELECT 'D7B39FE1-B4BF-4AA0-9D3F-D0A363BFB757','13055420-D791-4802-B75E-ADAB4C022BE7','Apr 4 2008 1:20AM','Diff' UNION ALL
SELECT '500BC0C0-0B7D-4D65-B635-D51F89DCA726','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 4:04AM','Tran' UNION ALL
SELECT '943474EF-2AE1-49B5-9B8F-DA42630EB195','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr 3 2008 2:10AM','Full' UNION ALL
SELECT 'CA58E9A5-155B-404A-B57D-DA902A2A1202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 6:04AM','Tran' UNION ALL
SELECT 'F2F58383-1E7B-4191-BB89-E99E63B08202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 12:04AM','Tran' UNION ALL
SELECT '985F479B-4C11-4C6D-AE7E-F44B3426DF7A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 3:04AM','Full' UNION ALL
SELECT '966B6917-C284-46CA-8664-9DFBEF222BEF','13055420-D791-4802-B75E-ADAB4C022BE7','Mar 30 2008 1:32AM','Full' UNION ALL
SELECT 'A588C63F-3887-4B4E-9AD3-F668BEB4194C','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 4:04PM','Tran'
GO
/* The query so far */
SELECT
CASE
WHEN DATEADD(dd,-2,getdate()) > MAX(B.BackupDate) THEN 'OMFG'
WHEN DATEADD(dd,-1,getdate()) > MAX(B.BackupDate) THEN 'wtf?'
ELSE 'k'
END AS BackupStatus,
S.ServerName,
D.DatabaseName,
MAX(B.BackupDate) AS BackupDate,
B.BackupType
FROM Backups B
INNER JOIN Databases D
ON B.DatabaseID = D.DatabaseID
INNER JOIN Servers S
ON D.ServerID = S.ServerID
GROUP BY S.ServerName, D.DatabaseName, B.BackupType
ORDER BY S.ServerName, D.DatabaseName
GO
/* Current Output */
-- BackupStatus ServerName DatabaseName BackupDate BackupType
-- ------------ ------------- ------------------- ----------------------- ----------
-- k AEPPROD03 master 2008-04-04 02:10:00.000 Full
-- k AEPPROD03 msdb 2008-04-04 02:40:00.000 Full
-- k AEPPROD03 pn_ProductionData 2008-04-04 01:20:00.000 Diff
-- OMFG AEPPROD03 pn_ProductionData 2008-03-30 01:32:00.000 Full
-- k AEPPROD03 pn_RedFlag 2008-04-04 03:04:00.000 Full
-- k AEPPROD03 pn_RedFlag 2008-04-04 06:04:00.000 Tran
/* Desired output */
-- BackupStatus ServerName DatabaseName BackupDate BackupType
-- ------------ ------------- ------------------- ----------------------- ----------
-- k AEPPROD03 master 2008-04-04 02:10:00.000 Full
-- k AEPPROD03 msdb 2008-04-04 02:40:00.000 Full
-- k AEPPROD03 pn_ProductionData 2008-04-04 01:20:00.000 Diff
-- k AEPPROD03 pn_RedFlag 2008-04-04 06:04:00.000 Tran
Any help is appreciated.
Thanks.
-D.
View 8 Replies
ADVERTISEMENT
Apr 14, 2015
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
View 2 Replies
View Related
Jun 3, 2006
Hi,
I was wondering if it would be possible to do a join on a grouped select.
SELECT Top 1 forumThreads.psRelTopId FROM forumThreads where forumThreads.psRelTopId > 0GROUP BY forumThreads.psRelTopId
This will return a specific psRelTopId and I would like to retrieve the psSubject column that is associated to that row.
How would I do a join in this scenario, am looking to see if it can be done in one step as opposed to a two step process. This is within a Stored Procedure of course.
View 4 Replies
View Related
Nov 12, 2005
I'm trying to set up a query in SQL and so far I have the following:
Code:
SELECT
CONTACT1.ACCOUNTNO, MAX(CONTACT1.COMPANY) AS Expr1, MAX(CONTSUPP.ACCOUNTNO) AS Expr2, MAX(CONTACT1.CONTACT) AS Expr3, MAX(CONTSUPP.CONTSUPREF) AS Expr4
FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO
WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTSUPP.CONTSUPREF <> 'PTG')
GROUP BY CONTACT1.ACCOUNTNO
ORDER BY CONTACT1.ACCOUNTNO
CONTACT1 will only ever contain ACCOUNTNO once, CONTSUPP on the other hand may have multiple instances of the same ACCOUNTNO. If CONTSUPREF contains 'PTG' in CONTSUPP for a particular ACCOUNTNO, I want the query to suppress the ACCOUNTNO altogether, but ONLY if 'PTG' is in CONTSUPP for that particular ACCOUNTNO. The problem I have at the minute is that I'm able to supress the lines from CONTSUPP where CONTSUPREF contains PTG, but it still returns one line for that ACCOUNTNO if there are any more.
In the attached example, I would like examples 2 and 3 to show but not example 1.
I hope that makes sense.
Thanks,
James
View 4 Replies
View Related
Mar 27, 2004
Hey guys,
I need to perform a query on two tables that look like this:
ATTENDANCE
attendanceid
memberid
meetingid
meetingdate
MEMBERS
memberid
firstname
lastaname
I can run this query just fine:
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid
This gives me memberid's for members that are present.
I need members that are not present.
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid
This returns a thousand rows when it should return no more than 25.
I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)
Thanks,
Mike
View 2 Replies
View Related
Jul 30, 2007
I want to do a Query in Table ZT_TransFmt9_Headerto join with other Table and then get the value of KeepMonth from ZT_DataBackupbut Table ZT_TransFmt9_Header is far from away ZT_DataBackupit seems have to run a complicated SQL Query to reach goal..
select BusinessCode from ZT_BillerInfo A, ZT_TransFmt9_Header ins where A.JihsunCode=ins.StoreCode
-----------ZT_TransFmt9_Header to join with ZT_BillerInfo for get BusinessCode
when I get BusinessCode , next I need put BusinessCode in where clause to select data from zt_billerinfo ( Table zt_billerinfo have a column named BusinessCode which can mapping with the BusinessCode I have just select from ZT_BillerInfo and ZT_TransFmt9_Header and then join with Table zt_biller get value of CompanyCode, and then use company code in Table Databackup to get the keepmonth
* I know my descrition may cause you feel confused. I past a Table picture herehttp://picasaweb.google.com.tw/jovi.fat/TAbleRelation/photo#5092934117841944082
View 1 Replies
View Related
Feb 1, 2008
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View 2 Replies
View Related
Dec 16, 2004
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
WHERE
[table4].[field2]=[table1.field2]{this is an inner join}
[table4].[field2]=[table2.field2]{this is an inner join}
[table4].[field2]=[table3.field2]{this is an inner join}
[table4].[field2]=[table5.field2]{this is an inner join}
[table5].[field3]=[table6.field2]{this is an inner join}
[table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
View 2 Replies
View Related
Jul 19, 2015
writing a cross join query with one table:
Cities(City_name, X_coordinate, Y_coordinate)
the result should be all combinations without reverse column returns
SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???
for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)
View 8 Replies
View Related
Jul 25, 2007
I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
View 1 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Jan 2, 2006
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
View 3 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
View Related
Aug 15, 2015
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN
[code] ....
View 9 Replies
View Related
Jul 23, 2005
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
View 3 Replies
View Related
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
View 4 Replies
View Related
Mar 10, 2008
Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
View 2 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Nov 5, 2004
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
Thx
View 12 Replies
View Related
Feb 24, 2006
I have one main Table "MainTable" which I want to relate with "subTable1, subTable2, ..." in such a way that:
"ith subTable" have to be related/joind on "ith row" of the "MainTable", "jth subTable" have to be related/joined on "jth row" of the "MainTable" and so on...
What I want Actually?
I want that when ever I delete a Record in the "MainTable", The corresponding "subTable" have to be deleted Itself.
I thought a solution that, I can cerate a Trigger on Delete of the "MainTable" and it would delete the corresponding "subTable". But I dont know how to ceate that.
Secound solution what I thought is, that may be there is some majic power in the Table Joinings. That I might join "MainTable" row with "subTable" ( ofcourse that I dont know either :))
So my question is, that what is the actual solution for my problem?
What ever solution is please give me a sample also with that. Like in a Trigger how can I write some Expression which can delete the "subTable" for the Currunt delete Row.
Regards,
Omer Kamal
View 3 Replies
View Related
Mar 30, 2007
I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
Thanks
View 4 Replies
View Related
Sep 29, 2007
Code:
SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total,
MAX(RecJobAds.PostedDt) as PostedDt,
MAX(RecJobAds.JobTitle) as JobTitle,
RecJobAds.JobId,
MAX (AgencyCandidate.AgencyId) as agentID,
MAX(RecJobAds.AdStatus) as status,
MAX(RecJobAds.CompanyId) as CompanyId,
MAX(RecJobAds.RecId) as RecId
FROM RecJobAds
LEFT JOIN AgencyCandidate
ON RecJobAds.JobId = AgencyCandidate.JobId
GROUP BY RecJobAds.JobId
ORDER BY Total ASC
i have the above query. but i would like to join in a third table, where my query is
Code:
select * from RecruiterMA where Activated = '1'.
anyidea in which place i can put the second query in the first query? thank you very much!
View 4 Replies
View Related
Jun 10, 2013
I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,
[Code] .....
Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND
[Code] .....
View 9 Replies
View Related
Nov 30, 2007
Hi, im searching for some help, here is my problem:i have an Appointments Table ex:Appointments AppointmentID : int ContactsList : xmlstruture of the ContactsList column:<ArrayOfContactsInfos> <ContactsInfos> <ContactID>1</ContactID> </ContactsInfos> <ContactsInfos> <ContactID>2</ContactID> </ContactsInfos></ArrayOfContactsInfos>And my Contacts Table:Contacts ContactID : int FirstName : varchar(200) LastName : varchar(200)What i want to do... is find the right query for retreive a list of Appointment based on the FirstName or LastName of a Contact in the ContactsList collumnis this possible to do a sort of join between the xml and the Contacts Table?and if yes how?Please can you help me?
View 1 Replies
View Related
Mar 5, 2008
Hello,There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name)
table B holds the names for gomA and gomB in tableA.I wish to select A table and inner join to it gom_name for gomA and gomB IDs.The problem begins when in table B there is multiple values for gom_id, with difference names.So, to retrieve the correct B.gom_id, there is 2 conditions. first bot_date>B.hist_date and after this, if we still gets multiple records then the top record will be selected (order by date)
Any ideas?
View 7 Replies
View Related
Jul 21, 2004
I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
WHERE news_id =41
SELECT CONCAT('users.user_fname', 'users.user_lname') *AS edit FROM users WHERE users.user_id=54
my previous attempts are
trouble with query.
I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.
now when I'm editing the article I want to get the name of the author from the users table.
I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.
they both need to lookup the user_fname and user_lname fields from the users table.
my current sql is below
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41
which gives me
Code:
news_id *news_subject *news_article *news_author *news_date *news_edited *edit_author *org_author *
41InterclubBunbury IC club has asked us all to attend a inter...771090247547109041836254Adam Green
Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ???
every way I try doesn't work.
this is what I'm currently trying to get it with.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (
SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author
FROM news
WHERE news.edit_author = users.user_id
)
WHERE news_id =41
Heres a different approach that I thought would work
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (
SELECT concat( users.user_fname, users.user_lname ) AS edit
FROM users
WHERE users.user_id =54
) AS edit
WHERE news_id =41
but no i get this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near '(
SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE' at line 4
I also tried
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author, *(SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id
WHERE news_id=41
but got this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE us' at line 2
can anyone help me with this query its been driving me up the wall all morning.
View 2 Replies
View Related
Jul 28, 2004
Hi,
Please help me in writing a join query.
I have three tables with three columns each. Now I want to retrieve data from all the three
tables as one.
Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points
These are related to three different divisions. So, on analysis we have get the no. of points
accumulated on a day. On any day the points can be in all three divisions or in any one or two
divisions. My database SQL Server 2000.
So, how to get this sort of output.
EmpidDatePts(DIv1) Pts(DIv2) Pts(DIv3)
V00107-24-2004NullNull25
V00207-24-200420Null25
V00307-24-2004Null30NUll
V00107-23-200415NullNUll
V00207-23-2004102525
V00107-22-2004Null1025
I'm badly in need of help. Any sort of help is appreciated.
M.L.Srinivas
View 3 Replies
View Related
May 30, 2012
how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....
Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal
FROM dbo.Cust P
INNER JOIN
(SELECT
CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
Sum(Balance) as Bal
FROM dbo.CAsh
Where Accumulator='DP'
GROUP BY CustID) A ON P.CustID = A.CustID
The 2 new tables are ViewDis (X) - Sum (Distance)and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.
Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
FROM dbo.Cust P
INNER JOIN
[code]....
View 14 Replies
View Related
Apr 8, 2008
I've a table like
Object ObjIdParentObjId
------ ----------------
Obj1 |101 |0
Obj2 |102 |101
Obj3 |103 |0
Obj4 |104 |102
If the 'ParentObjId' is 0 it means no more parent,
If I filter the Object value as Obj4, it should show the following rows
Obj4 |104 |102
Obj2 |102 |101
Obj1 |101 |0
Guide to me write a SQL for this situvation?
View 17 Replies
View Related
Jun 10, 2008
i have two tables field1 is the primary key:
table1
--------------------
field1|field2|field3
---------------------
table2
---------------------
field1|field4|field5
---------------------
what we need is to get the results based on field1 matching datas, but is also a requirement that if there is no matching data in field1 of table2 w.r to table1 we just need to return table1 datas with null values for table2 fields.
is that possible, i used inner, right joins but all in vain.
what I need is a output like this
output
---------------------------------------
field1|field2|field3|field4|field5
---------------------------------------
View 5 Replies
View Related
May 17, 2006
i hav 2 tables which contain a common field called ID.
Table A Table B
ID ID
a a
b b
c
d
Now i want results from Table A in such way that ID's Which are in table B do not get selected in table A.
how do i use join query for this
View 2 Replies
View Related
Mar 15, 2007
Hi
I have two tables and one linking table they look something like this
<<<<<<<Application Table>>>>>>
AppID AppName
1 MS Word
2 Excel
3 SoftGrid
<<<<<<System Table>>>>>>>>
SysID SysName
1 System 1
2 System 2
3 Server 1
<<<<<<Link Table>>>>>>>>>
SysID AppID
1 1
1 3
How can i retirve the data so that it picks up and displays all the applications that are not on the the server as well as those that are
for example the output im trying to get is as follows from the data above.......
SysName AppName Y/N
System 1 MS Word Y
System 1 Excel N
System 1 SoftGrid Y
System 2 MS Word N
System 2 Excel N
System 2 SoftGrid N
Server 1 MS Word N
Server 1 Excel N
Server 1 SoftGrid N
i want to able to get a dynamically generated Y/N colunm
Im ok with doing innerjoin queries but the problem is how do i get the rest of the results that have no links to show that there it does not exist.
Thanks
View 3 Replies
View Related
