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 > 0
GROUP 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.
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.
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 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 ALLSELECT 'DD0757B9-D1BE-4407-ABE0-24625CE65076','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','model','SIMPLE','sa' UNION ALLSELECT 'B9232B6F-3E09-480B-8658-4FE9ED2CC387','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','master','SIMPLE','sa' UNION ALLSELECT '9C5CC23D-72D8-40CC-B1B0-665CD5F27120','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_RedFlag','FULL','MM04519' UNION ALLSELECT '13055420-D791-4802-B75E-ADAB4C022BE7','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_ProductionData','BULK_LOGGED','M M04519'GO INSERT INTO BackupsSELECT '94539C6E-C459-42B1-928C-02E94BA2D230','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 2:04AM','Tran' UNION ALLSELECT '24CE98B1-C737-46EF-92D2-048309DF192B','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 8:04PM','Tran' UNION ALLSELECT '6521DA25-BB6C-4407-964A-09BBEAB8978D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 8:04AM','Tran' UNION ALLSELECT 'B0CB6502-F022-4F46-9994-177BB61E082F','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 12:04PM','Tran' UNION ALLSELECT '69C7078B-F5C3-4805-815C-1D3F6450628E','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 6:04AM','Tran' UNION ALLSELECT '8C6D591E-8633-409C-8585-231B3C0920FC','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 8:04AM','Tran' UNION ALLSELECT 'A7216995-BC83-48CF-B5E6-27EFF2E8A841','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 6:04PM','Tran' UNION ALLSELECT 'A2537957-E601-4BCE-A23B-317021219BA0','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 8:04PM','Tran' UNION ALLSELECT '4A23B5C6-65A4-483D-8F70-32B2D459769A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 4:04PM','Tran' UNION ALLSELECT '8812B10D-B2F0-48A1-ACEF-451FC1572D18','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr 4 2008 2:40AM','Full' UNION ALLSELECT '60C80D58-5FDC-41E4-A529-4CBA6A04B4EA','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 2:04PM','Tran' UNION ALLSELECT 'B7D4F3FF-DDC9-4BD3-8AE8-4CD9C4C31375','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 2:04AM','Tran' UNION ALLSELECT '1A2C3054-5563-465C-9DF8-53A154DB4DF1','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 12:04PM','Tran' UNION ALLSELECT '90C4F821-3251-482D-B103-566F7AA72CBE','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 6:04PM','Tran' UNION ALLSELECT '53F9773E-9F09-44E8-9BC8-6F253D66106C','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 2:04PM','Tran' UNION ALLSELECT 'F60C2566-44C4-495E-80BA-76831048451D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 10:04PM','Tran' UNION ALLSELECT 'FBF29499-D4ED-4AB0-BBE8-76FFA3CC6737','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr 3 2008 2:40AM','Full' UNION ALLSELECT '88C1F74E-CB0E-4CEC-9720-8D47558AAC5A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 12:04AM','Tran' UNION ALLSELECT '45DD22A0-16A0-4BEE-942E-8FA27DC52C31','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr 4 2008 2:10AM','Full' UNION ALLSELECT 'F9F2EEB2-EFAD-4229-994C-9F175269AF76','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 10:04AM','Tran' UNION ALLSELECT 'FE754924-E31A-43BC-8BD3-B3CA0EA02633','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 10:04AM','Tran' UNION ALLSELECT '8C692625-B071-4A12-A620-B7A4A56AC1B7','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 4:04AM','Tran' UNION ALLSELECT '62FBC73D-49C3-4572-A017-C025ACBF0948','13055420-D791-4802-B75E-ADAB4C022BE7','Apr 3 2008 1:21AM','Diff' UNION ALLSELECT '938DF985-C0E4-41E7-9510-C02FFE06F186','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 10:04PM','Tran' UNION ALLSELECT 'F19BD5F6-0FD4-4BD2-91DA-D03CF9719124','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 3:04AM','Full' UNION ALLSELECT 'D7B39FE1-B4BF-4AA0-9D3F-D0A363BFB757','13055420-D791-4802-B75E-ADAB4C022BE7','Apr 4 2008 1:20AM','Diff' UNION ALLSELECT '500BC0C0-0B7D-4D65-B635-D51F89DCA726','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 4:04AM','Tran' UNION ALLSELECT '943474EF-2AE1-49B5-9B8F-DA42630EB195','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr 3 2008 2:10AM','Full' UNION ALLSELECT 'CA58E9A5-155B-404A-B57D-DA902A2A1202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 6:04AM','Tran' UNION ALLSELECT 'F2F58383-1E7B-4191-BB89-E99E63B08202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 12:04AM','Tran' UNION ALLSELECT '985F479B-4C11-4C6D-AE7E-F44B3426DF7A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 3:04AM','Full' UNION ALLSELECT '966B6917-C284-46CA-8664-9DFBEF222BEF','13055420-D791-4802-B75E-ADAB4C022BE7','Mar 30 2008 1:32AM','Full' UNION ALLSELECT '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.BackupTypeFROM Backups BINNER JOIN Databases D ON B.DatabaseID = D.DatabaseIDINNER JOIN Servers S ON D.ServerID = S.ServerIDGROUP BY S.ServerName, D.DatabaseName, B.BackupTypeORDER BY S.ServerName, D.DatabaseNameGO /* 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.
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
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!
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:
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.
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?
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.
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.
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".
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
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
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
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.
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.
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?
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!
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.
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?
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?
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.
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
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.
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.
