How To Write A Complated SQL Query For Join Up To 6 Table ?
Jul 30, 2007
I want to do a Query in Table ZT_TransFmt9_Header
to join with other Table and then get the value of KeepMonth from ZT_DataBackup
but Table ZT_TransFmt9_Header is far from away ZT_DataBackup
it 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 here
http://picasaweb.google.com.tw/jovi.fat/TAbleRelation/photo#5092934117841944082
View 1 Replies
ADVERTISEMENT
Jul 30, 2007
How to write a left join using just one table? I have to select things using left join. Can you give just a example?
View 1 Replies
View Related
Jun 2, 2004
I have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
View 4 Replies
View Related
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
Apr 12, 2006
I've got a rather large database, approx 560 tables, that is about 10G. We're running into trouble when an application moves data from my database into GreatPlains accounting software.
The problem seems related to the value 'foo' not coming across correctly. I've looked at all the tables that I think would be involved, but couldn't find the value. So, I'd like to write a script to sequence through the tables and check each column for the value.
Anybody know how to write such a script.
Thanks,
alex8675
View 2 Replies
View Related
Jun 19, 2002
Hi,
Please advice me how to write query from table which is given as input(It is not hard coded)
Declare @varTableName varchar(30)
Select @varTableName = 'table_employee' -- table name
Select * from @varTableName
This gives me an error : Must declare the variable '@varTableName'.
View 2 Replies
View Related
Jan 16, 2005
Hi,
Anyone can help me for the Sql query?
I want to sort a table with a priority column, e.g. in the following...
Table A
======
value
======
9
3
1
7
4
======
After sorting:
Table A
========
no value
========
1 1
2 3
3 4
4 7
5 9
========
Anyone can help me?
Thanks.
Daniel.
View 1 Replies
View Related
Oct 26, 2006
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 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
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 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.
View 8 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
Oct 24, 2006
I want to filter some search results using an inner join.The criteria are passed as a parameter.If the parameter has value, I want to inner join with it. If there is a NULL value, I just want to ignore the inner join. I don't want to:1) Build a string and do an exec() - too slow and hard to maintain2) use a (@parm IS NULL) OR ('inner join') AGAIN, too slow Suggestions appreciated
View 4 Replies
View Related
Oct 24, 2001
How to use ANSI-standard JOIN to write follow query which contains two outer join ?
SELECT a.*,b.title as classification,c.title as
employees,d.username,d.password,d.role_id,d.status
FROM DBO.PROFILE a,DBO.classification b,DBO.employee c ,DBO.USER d
WHERE a.user_id = 1 and a.employee_id *=c.id and a.classification_id *=b.id
and d.id= 1
Many thanks
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
Nov 17, 2011
Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.
This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.
db1.usr_table.usr_name = jdoenew
If jdoenew is missing in the 2nd db I will need to write entries like:
db1.usr_table.usr_name = jdoenew
db1.usr_table.password = tmppassword
db1.usr_table.active = 1
View 1 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
Sep 13, 2006
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
View 1 Replies
View Related
Oct 28, 2006
I have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
View 4 Replies
View Related
Feb 10, 2008
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
View 8 Replies
View Related
Feb 11, 2008
hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
View 1 Replies
View Related
May 21, 2008
I have two table named tbl_Scale and tbl_NGTrDAMaster
tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key
tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key
and we get CityTypeID from xml databinder.......
In my form thr is two drop down list one for scale name and another for city type id
this is the data form tbl_NGTrDAMaster
17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412
but i am expecting only one combination of set.....
like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that....
please help me....
i am in big trouble......Thanx in advance
If my qes is not clear for everyone...
plz tell me....
i try my lebel best for understand my prob to u.....
View 2 Replies
View Related
Jun 2, 2004
i have a table
tab
col1 col2 num
A a 30
A b 20
B a 10
B b 40
C a 50
C b 40
now i want get col1 by distinct col1 ,and order by num, as the result:
col1
C
B
A
so can someone help me to write this "select..."
View 3 Replies
View Related
May 17, 2005
Hi
I have 2 tables and I want to Get information from that tables by SQL Query but How Can I writ this SQL Query ? .. My target as Follow
Class Table
-------------------------------------------------
ClassID ClassName
1 AA
2 BB
Student Table
-------------------------------------------------
StudentID StudentName ClassID
1 Student 1 1
2 Student 2 1
3 Student 3 2
4 Student 4 1
5 Student 5 2
6 Student 6 1
How Can I Writ SQL Query to get result like the following ..
--------------------------------------------------
ClassID ClassName StudentCount
1 AA 4
2 BB 2
My SQL Query must get all Class table column plus column content the count of student in each class
And thanks with my regarding
Fraas
View 3 Replies
View Related
May 12, 2006
Hello,
I have a table with fields; T1: Dept, Name, Desc, ModificationDate
How can I group by T1.Name, T1.Desc and bring T1.Dept which has the latest T1.ModificationDate
Can anyone write me this query?
View 3 Replies
View Related
Nov 26, 2003
CUSTOMER
Name City IndustryType
Abernathy Construction Willow B
Amalgamated HousingMernphisB
Manchester LumberManchesterF
Tri-City BuildersMemphis B
ORDERS
NumberCustNameSalespersonNameAmount
100Abernathy ConstructionZenith560
200Abernathy ConstructionJones1800
300Manchester LumberAbel480
400Abernathy ConstructionAbel2500
500Abernathy ConstructionMurphy6000
600Tri-City BuildersAbel700
700Manchester LumberJones150
800 Abernathy Construction Abel 75000
SALESPERSON
NamePercentOfQuotaSalary
Abel63132000
Baker3846200
Jones2649500
Kobad2739600
Murphy4255000
Zenith59129800
I have got the three tables above.
Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers.
Thank you very much!
View 1 Replies
View Related
Nov 26, 2003
CUSTOMER
Name City IndustryType
Abernathy Construction Willow B
Amalgamated HousingMernphisB
Manchester LumberManchesterF
Tri-City BuildersMemphis B
ORDERS
NumberCustNameSalespersonNameAmount
100Abernathy ConstructionZenith560
200Abernathy ConstructionJones1800
300Manchester LumberAbel480
400Abernathy ConstructionAbel2500
500Abernathy ConstructionMurphy6000
600Tri-City BuildersAbel700
700Manchester LumberJones150
800 Abernathy Construction Abel 75000
SALESPERSON
NamePercentOfQuotaSalary
Abel63132000
Baker3846200
Jones2649500
Kobad2739600
Murphy4255000
Zenith59129800
I have got the three tables above.
Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers.
Thank you very much!
View 1 Replies
View Related
Apr 5, 2006
User Page Name Permission
vijay customer.aspx 1
vijay customer.aspx 2
vijay customer.aspx 3
vijay user.aspx 2
Rajashekar customer.aspx 1
Rajashekar customer.aspx 2
Where Permission 1 = SAVE
2 = UPDATE
3 = DELLETE
Where I query on User and PageName I want the output as
User Page Name Permission
vijay customer.aspx 1,2,3
vijay user.aspx 2
Rajashekar customer.aspx 1,2
View 2 Replies
View Related
Feb 23, 2006
i am assuming there is a better way to write this query (since im not too proficient in SQL)
sql Code:
Original
- sql Code
select client_id from clients where client_id not in
(select schedule_det.client_id from schedule_det,
schedule_mstr where schedule_det.schedule_id=schedule_mstr.schedule_id
and schedule_mstr.status_code!='COMPLETE')
SELECT client_id FROM clients WHERE client_id NOT IN (SELECT schedule_det.client_id FROM schedule_det, schedule_mstr WHERE schedule_det.schedule_id=schedule_mstr.schedule_id AND schedule_mstr.status_code!='COMPLETE')
View 2 Replies
View Related
