Not Grasping Outer Joins For Ms-sqlserver :: 3 Tables To Join
Mar 15, 2006
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working
on a three table query.
Assumptions:
-- I have events in the Event table.
-- Each event CAN have one Transaction, but it's not guaranteed
-- Each transaction, ir present, will have one or more Amount records
This would be the pseudo-query without any special joins:
-----------------------------------------
SELECT
a.Name,
SUM( c.amount ) as Total
FROM
Event a,
Transaction b,
Amounts c
WHERE
a.EventID = b.EventID
ANDb.TransID = c.TransID
-----------------------------------------
This is fine if there is a Transaction for the Event. But, if there's
no transaction for an event, no record is pulled of course.
What I need is for a record to come back for each event regardless of
the presence of a Transaction. If there's no transaction, then the
"Total" column should be 0.
How would I get an OUTER JOIN to work on this so that each Event gets a
record?
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.
Is it possible to utilize more than two tables in a single outer join? I have one table that I want every row and 18 others where I only want an entry if one is present meeting the conditions of "1.customerid = 2.Customerid" etc. I haven't run across this before and would appreciate any help.
Is it possible to do a full outer join on more than 2 tables, i've been looking for the syntax online but can't seem to find it, so far i have
FROM dbo.TRNEWL_RTENTN_DETL RR FULL OUTER JOIN VBVT_FIN_DIV_TRANSF ON RR.X_POLICY_NO = VBVT_FIN_DIV_TRANSF.X_POLICY_NO and RR.X_POLICY_EFCTV_DT= VBVT_FIN_DIV_TRANSF.X_POLICY_EFCTV_DT and RR.X_ASCO_CD = VBVT_FIN_DIV_TRANSF.X_ASCO_CD and RR.PRODUCT_RENWL_ABBR = VBVT_FIN_DIV_TRANSF.PRODUCT_RENWL_ABBR
Now i need to do another Full outer join to RR with table VBVT_FIN_RGN_TRANSF, is that possible?
I need a small help in creating a join query in sql.
My two tables are A and B. A has got columns id, dname, counter and date. B has got two columns link and date. The primary key of A is the column Id whereas in table B, both are primary keys(composite key).
I need to get the records (id,dname,counter) from A which has no corresponding link in table B.
To be precise, If the table A has fields (id,dname,counter) (1,abc,2000) and if table B has no record for abc, this row should be returned from A.I hope using left outer join will help me in getting my desired result.I hope someone will be able to help me out.
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT Article.articleId , Article.articleName , Article.articleStatus , Articlegroup_2.ArticlegroupId , Articlegroup_2.g2_key , Articlegroup_2.g2_name , articleGroup.articleGroupId FROM HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2 WHERE articleGroup.articleGroupId = Article.articleGroupId AND Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id
I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.
I'm a SQL code novice so any help would be greatly appreciated!
Here is my existing SQL query (without the new outer join table):
Hello,I'm trying to link two tables... one for Employees and the other forTimecardsI need to get a list of employees that do not have timecards on anSPECIFIC DATEI tried the follonwingSELECT Employess.EmployeeIDFROM Employees LEFT OUTER JOIN Timecards on Employees.EmployeeID =Timecards.lmpEmployeeIDWHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'But it doesn't work. However, when I comment the date condition out(lmpTimecardDate = '10/24/2007') it works all right but It's not whatI needAnother interesting point... if I use the following query... it worksall rightSELECT Employess.EmployeeIDFROM EmployeesWHERE Employees.EmployeeID not in (select Timecards.EmployeeID fromTimecardswhere TimecardDate = '10/24/2007')I'd like to be able to use the Left Outer Join option.... Am I doingsomething wrong?... or is it that if It doesn't like the condition I'musgin in the WHERE clause (TimecardDate = '10/24/2007')Thanks for your helpPablo
I am trying to select specific columns from multiple tables based on acommon identifier found in each table.For example, the three tables:PUBACC_ACPUBACC_AMPUBACC_ANeach have a common column:PUBACC_AC.unique_system_identifierPUBACC_AM.unique_system_identifierPUBACC_AN.unique_system_identifierWhat I am trying to select, for example:PUBACC_AC.namePUBACC_AM.phone_numberPUBACC_AN.zipwhere the TABLE.unique_system_identifier is common.For example:----------------------------------------------PUBACC_AC=========unique_system_identifier name1234 JONES----------------------------------------------PUBACC_AM=========unique_system_identifier phone_number1234 555-1212----------------------------------------------PUBACC_AN=========unique_system_identifier zip1234 90210When I run my query, I would like to see the following returned as oneblob, rather than the separate tables:-------------------------------------------------------------------unique_system_identifier name phone_number zip1234 JONES 555-1212 90210-------------------------------------------------------------------I think this is an OUTER JOIN? I see examples on the net using a plussign, with mention of Oracle. I'm not running Oracle...I am usingMicrosoft SQL Server 2000.Help, please?P. S. Will this work with several tables? I actually have about 15tables in this mess, but I tried to keep it simple (!??!) for the aboveexample.Thanks in advance for your help!NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAILADDRESS.Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!(Rhetorical question only.)
We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.
Thanks in advance for any help. -David Edelman
Test script below, followed by results =========================================== create table parent (p_id int NOT NULL) go create table child (p_id int NOT NULL, c_type varchar(6) NULL) go insert parent values (1) insert parent values (2) insert parent values (3) insert parent values (4) insert parent values (5) insert parent values (6) insert parent values (7) insert parent values (8) insert parent values (9) insert parent values (10) go
I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting. I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I know to be incorrect. Furthermore, it appears that the counts are being multiplied together (in one instance, enrolled should be 14, waiting should be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts are accurate. The problem only occurs when I try to pull counts from both the tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Cod_Lingua - Des_Lingua ------------------------------ ITA Italian GER German ENG English FRA French
and another table with product/description
ProductID - Cod_Lingua - Description ------------------------------------------- 1 ITA Mia Descrizione 1 ENG My Description
I've this SELECT:
SELECT Tab_Lingue.Cod_Lingua, Descrizioni_Lingua.Description FROM Descrizioni_Lingua RIGHT OUTER JOIN Tab_Lingue ON Tab_Lingue.Cod_Lingua=Descrizioni_Lingua.Cod_Lingua WHERE Descrizioni_Lingua.ProductID=1
I get these results: ITA - Mia Descrizione ENG - My Description
I don't want this. I'd like to have this: ITA - Mia Descrizione ENG - My Description GER - (null) FRA - (null)
I have been using an outer join to display information in a form. I would like to edit the rows that are returned. The join shows all of the rows in the 1st table, and the corresponding rows in the 2nd other. If there are no corresponding rows in the 2nd table, a NULL is displayed. The problem is when I try to edit one of the fields that is "NULL", I get an error. I know why the error is happening, because the NULL field is a row that does not exist, yet. The jet database engine will automatically insert rows into the 2nd table, but SQL server will not. I have tried to set primary key and foreign key properties for the tables in question, but it still does not work. Does anyone know how to do this?
I have been reading about outer joins and how they are much faster to process on larger data sets than NOT EXISTS are..Did anyone find any significant performance gain..Logically, using an outer join does not make sense in this context..Can someone shed some light on this subject?
I am trying to create an outer join between two tables in a query thatincludes several other tables.When I double-click on the Join line, it presents three join options:1) ONLY records from table1 and table2 where join fields are equal2) ALL values from table1 and ONLY records from table2 where joinfields are equal3) ALL values from table2 and ONLY records from table1 where joinfields are equalIn my case, I want option 2 - all values from table1, and if there isno match to table2, I want a blank to appear in the output.When I select this option, I get the following error:"Can't have outer joins if there are more than two tables in thequery."How can I get around this, since there are other tables in my query?Thanks.Dennis HancyEaton CorporationCleveland, OH
I am confused with inner and outer joins. Can someone explain to me exactly what the differences are? The reason I am asking is I am trying to populate a datagrid from multiple tables. The SQL query currently pulls out all customers that have revenue only, When in fact I need it to pull out all customers regardless of revenue so that I can see which customers have "0" revenue.
Here is my sql query which sorts each day into a new datagrid column.
SQL = "SELECT revenue_forecast.oct_03 AS 'forecast', pb_customers.customer_name AS 'customer', sum(case when day(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost else 0 end) as day1, sum(case when day(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost else 0 end) as day2, FROM pb_report_shippers Left outer JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id left outer JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id left outer JOIN revenue_forecast ON pb_customers.customer_id = revenue_forecast.customer_id WHERE pb_report_shippers.shipper_date_time between cast('11/01/03' as datetime) and cast('11/2/03' as datetime) AND pb_report_shippers.job_completed IN('1','0') AND pb_customers.customer_deleted <> '1' GROUP by pb_customers.customer_name, revenue_forecast.oct_03 Order BY pb_customers.customer_name"
I wrote a SQL query that firstly do an outer-join for two table and after I add another table with outer-join as well. I'm getting the following message: Server: Msg 301, Level 16, State 1, Line 1 Query contains an outer-join request that is not permitted. May be someone knows what is it? If I remove the second join- it work....
Hi, I need to change some SQL statements in Oracle to to SQL Server syntax. For example, the following is a query in Oracle SQL syntax. ================================================== =========================== SELECT COUNT(*) FROM TABLE1 T1, TABLE2 T2, TABLE3 T3, TABLE4 T4, TABLE5 T5 WHERE ( T1.NO(+)=T3.NO AND T3.NO=T2.NO(+) AND T4.NO(+)=T3.NO AND T3.NO=T5.NO(+) ) AND ( T1.CODE(+)='VALUE1' AND T2.CODE(+)='VALUE2' AND T4.CODE(+)='VALUE3' AND T5.CODE(+)='VALUE4' ) ================================================== ========================= Can you anyone please tell me the SQL Server equivalent? I am having doubts in the outerjoin part. Thanks in advance Regards R
select * from furn_piece_master fpm, furn_piece_detail fpd, furn_detail fd where fpd.fpd_master_id = fpm.fpm_id and fpm.ymr = '200411U' and fpd.ymr = fpm.ymr and fpd.chain = fpm.chain and fpd.store = fpm.store and fpd.week = fpm.week and fd.ymr = fpd.ymr and fd.chain = fpd.chain and fd.store = fpd.store and fd.report_type = fpd.report_type and fd.week = fpd.week and fd.item_key = fpd.item_key
Now I want to do an outer join on the furn_detail (fd) store only, but leave the rest of the furn_detail inner joins. How would I do this?
I'm upgrading from SQL 2000 -> 2005 at the moment. Having an issue with some old Outer Join SP's which use the =* and *= operators.
The issue i am having is this. Using this Procedure on the Pubs DB: SELECT title, qty FROM titles, sales WHERE titles.title_id *= sales.title_id ORDER BY qty
Works fine. I get 23 rows with 2 of them having NULL in qty.
This also works, giving the same result: SELECT title, qty FROM titles LEFT OUTER JOIN sales ON titles.title_id = sales.title_id ORDER BY qty
All looking good. Now, just say i want to limit the rows from the Right table. Lets just select Store #6380. Using the old *= operator: SELECT title, qty FROM titles, sales WHERE titles.title_id *= sales.title_id AND Stor_ID = 6380 ORDER BY qty
This works fine, giving me all 18 titles, with sales amounts next to two of them, the rest are null, which is the response I want. However, if I insert the same WHERE clause into the OUTER JOIN code: SELECT title, qty FROM titles RIGHT OUTER JOIN sales ON titles.title_id = sales.title_id WHERE Stor_Id = 6380 ORDER BY title
It seems to act like an Inner Join, not an Outer Join, giving me only rows that match on both sides. I can't seem to work out why this would do this. Am I overlooking something simple? Can someone give me some idea why it's not working?
First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?
I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:
SELECT A.* , F.Descricao AS FamiliasDescricao , M.Descricao AS MarcasDescricao , I.Descricao AS IVADescricao FROM Artigos AS A LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;
The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error [7]", Native error 25607, an the stack trace ends with:
em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s() em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan() em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d() em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()
If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.
Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?
I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...
Take the following example:SELECT * FROM [User] LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).
Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.
The solution I have used in the past is:
SELECT * FROM [User] LEFT OUTER JOIN ( SELECT * FROM [Profile] INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId] ) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]
The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.
I could do this:SELECT * FROM [User] LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.
I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.
Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?
I'm having problems with queries that use a view in an outer join. It's just sits there and grinds. Below is some queries to illustrate.
SELECT EvalID,RecIndex FROM dbo.vCurrentEvalInfo
Executed in 1 second in SQL Query Analyzer Returns 29,820 Rows ----
SELECT RecIndex FROM dbo.tblCNAKeyInformation
Executed in 1 second in SQL Query Analyzer Returns 47,978 Rows ----
SELECT C.RecIndex, E.EvalID FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E ON C.RecIndex=E.RecIndex
Executed in 1 second in SQL Query Analyzer Returns 47,978 Rows ----
SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E ON C.RecIndex=E.RecIndex
Executed in 1 second in SQL Query Analyzer Returns 47,978 Rows ----
Never Finishes Executing. If I include any column from the dbo.tblCNAKeyInformation and the query just grinds.
exampe: SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults, C.LastName FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E ON C.RecIndex=E.RecIndex
Also, the below query doesn't finish executing either. SELECT RecIndex FROM dbo.tblCNAKeyInformation WHERE (RecIndex NOT IN (SELECT RecIndex FROM dbo.vCurrentEvalInfo))
Any I missing something Here? I seems like it's recalcualting the view with each scan of the outer table on the join instead of using a cached copy for x number of micro seconds ago.
I could solve this my dumping the view to a temp table, however that seems like an ugly hack to me.
SELECT Market_code, Description, Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ), CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ), Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END ) FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_code Where Budget.BudgetYear=2007 or Budget.BudgetYear is null GROUP BY Market_code, Description ORDER BY Description
-- ------------------GIVES only 12 results -------------------------------
SELECT Market_code, Description, Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ), CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ), Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END ) FROM Budget, Market Where Budget.Customer_Market_code =* Market.Market_code AND Budget.BudgetYear=2007 GROUP BY Market_code, Description ORDER BY Description
Other than being much less readable, is there a downside to combining left and right outer joins in the same SELECT? I'm reviewing some generally poor code done by a contractor and it's peppered with queries with both left and right joins. I've always thought it was just a semantic difference, but I was just wondering if, other than readability, there were any performance issues.
I am trying to extract the non matching records and the matching ones in two tables from two servers, one a linked server in one go. For example if table A has records, Rec1, Rec2, Rec3 and Rec6 AND Table B has Rec1, Rec2, Rec3 and Rec7 I need to get in the result set Rec1, Rec2, Rec3, Rec6 and Rec7.
The real query I ran is as follows. I want to know the list of all tables in GlobalDB database in sg_qt1 and sg_q5 servers. NOTE : sg_q5 is a Linked server to sg_qt1.
Select Substring(a.name,1,30), Substring(user_name(a.uid),1,19) from sysobjects a full outer JOIN sg_q5.globaldb.dbo.sysobjects b ON a.name = b.name where a.xtype = 'u' and b.xtype = 'u' and a.name not in ('dtproperties','rowcounts','globalDBrowcounts')
If I run it from sg_qt1, the result I get contain all tables from sg_qt1 but not the non-matching ones from sg_q5.
I am obviously doing something wrong, but what is it?
Thanks. If possible please reply to, r.wimalaratne@iaea.org
My problem exists on the last line - TOAD doesn't seem to like the two sets of parenthesis together. Am I supposed to put something in between? It's OK in the line above because the substring is on the other side.
SELECT distinct fp.active_fl, fp.empl_id, fp.proj_mgr_name, pm.project, fp.subctr_id, rd.CONT_CRNCY FROM planner.rbws_proj_mgr pm, finance.fin_proj fp, finance.rbws_detl rd WHERE SUBSTR(fp.proj_id, 1, 9) = pm.project (+) AND pm.PROJECT = substr(rd.PROJ_ID,1,9) (+)
I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys. I bolded the three IDs that could be NULL or have a foreign key for a value. An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet. Right now I only get profiles that have foreign key values and it misses the rest in the search. So NULL MakeID or ModelID no result on that item my SQL statement below. Using SQL Server 2005. Pretty new this to SQL and databases but so far this has been the only trying part.
Thanks
String dbsql = "SELECT a.EquipmentID " + " , a.SerialNo " + " , b.Category " + " , c.Subcategory " + " , d.Make " + " , e.Model " + " , f.Status " + " FROM tblEquipInfo a " + " , tblEquipCat b " + " , tblEquipSubcat c " + " , tblEquipMake d " + " , tblEquipModel e " + " , tblStatus f " + " WHERE b.Category = '" + val + "' " + " AND a.CategoryID = b.CategoryID " + " AND a.SubcategoryID = c.SubcategoryID " + " AND a.MakeID = d.MakeID " + " AND a.ModelID = e.ModelID " + " AND a.StatusID = f.StatusID";
