I have a table with data that is refreshed regularly but I still need to
store the old data. I have created a seperate table with a foreign key
to the table and the date on which it was replaced. I'm looking for an
efficient way to select only the active data.
Currently I use:
SELECT ...
FROM DataTable AS D
LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
WHERE D.Key IS NULL
However I am not convinced that this is the most efficient, or the most
intuitive method of acheiving this.
Can anyone suggest a more efficient way of getting this information
please.
Many thanks.
*** Sent via Developersdex http://www.developersdex.com ***
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of
SELECT * FROM a LEFT JOIN b ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Please help me with the efficient JOIN query to bring the below result :
create table pk1(col1 int)
create table pk2(col1 int)
create table pk3(col1 int)
create table fk(col1 int, col2 int NOT NULL, col3 int, col4 int)
insert into pk1 values(1) insert into pk1 values(2) insert into pk1 values(3)
insert into pk2 values(1) insert into pk2 values(2) insert into pk2 values(3)
insert into pk3 values(1) insert into pk3 values(2) insert into pk3 values(3)
insert into fk values(1, 1, null, 10) insert into fk values(null, 1, 1, 20) insert into fk values(1, 1,null, 30) insert into fk values(1, 1, null, 40) insert into fk values(1, 1, 1, 70) insert into fk values(2, 3, 1, 60) insert into fk values(1, 1, 1, 100) insert into fk values(2, 2, 3, 80) insert into fk values(null, 1, 2, 50) insert into fk values(null, 1, 4, 150) insert into fk values(5, 1, 2, 250) insert into fk values(6, 7, 8, 350) insert into fk values(10, 1, null, 450)
Below query will give the result :
select fk.* from fk inner join pk1 on pk1.col1 = fk.col1 inner join pk2 on pk2.col1 = fk.col2 inner join pk3 on pk3.col1 = fk.col3
But I require also the NULL values in col1 and col3
Hence doing the below :
select distinct fk.* from fk inner join pk1 on pk1.col1 = fk.col1 or fk.col1 is null inner join pk2 on pk2.col1 = fk.col2 inner join pk3 on pk3.col1 = fk.col3 or fk.col3 is null
The above is the reqd output, but the query will be very slow if there are more NULL valued rows in col1 and col3, since I need to also use distinct if I use 'IS NULL' check in JOIN.
Please let me know if there is an aliternative to this query which can return the same result set in an efficient manner.
Hi allI have a bit of a dilema that I am hoping some of you smart dudesmight be able to help me with.1. I have a table with about 50 million records in it and quite a fewcolumns. [Table A]2. I have another table with just over 300 records in it and a singlecolumn (besides the id). [Table B]3. I want to:Select all of those records from Table A where [table A].descriptiondoes NOT contain any of (select color from [table B])4. An exampleTable Aid ... [other columns] ... description1the green hornet2a red ball3a green dog4the yellow submarine5the pink pantherTable Bidcolor55blue56gold57green58purple59pink60whiteSo I want to select all those rows in Table A where none of the wordsfrom Table B.color appear in the description field in Table A.I.E: The query would return the following from Table A:2a red ball4the yellow submarineThe real life problem has more variables and is a little morecomplicated than this but this should suffice to give me the rightidea.Due to the number of rows involved I need this to be relevantlyefficient. Can someone suggest the most efficient way to proceed.PS. Please excuse my ignorance.CheersSean
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.
Which way of retrieving a record is more effecient?:Select tbl1.field1, tbl2.field1from table1 tbl1 inner join table2 tbl2on tbl1.id = tbl2.idwhere someid = somevalueand someid = somevalueorSelectfield1 = (Select field1 from tabl1 where someid = somevalue),field2 = (Select field2 from table2 where someid = somevalue)
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name] from Contact right join Account on Contact.[Account Name] = Account.[Account Name] where Contact.[Account Name] = Account.[Account Name]
I'm trying to join 2 tables. I thought I was getting the correct results but it turns out I'm not. My Query: SELECT IVINVA, IVORDN, IVCSLN, IVRESR, IVCITM, CONVERT(varchar(12),CAST(IVIAMT as money),1) AS ExtPrice, CONVERT(varchar(12),CAST(IVPIVC as money),1) AS DistPrice, IVCSUM, IVQYCS, IVDESC, OIRESR, OIDPCT, CONVERT(varchar(12),CAST(IVPIVC - (OIDPCT / 100 * IVPIVC) as money),1) AS NetPrice FROM INVDET1_TBL LEFT JOIN ORDDIS_TBL ON ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN AND ORDDIS_TBL.OIRESR = INVDET1_TBL.IVRESR WHERE IVORDN = '0859919' AND IVINVA = '00324024' Basically, my problem lies in the seonc condition of the LEFT JOIN. I needed to set the two tables equal my item number, because in some situations I need that logic to get the correct result. It most other cases, that item column in the ORDDIS_TBL is NULL, thus giving me the wrong results. In that case, I would want the JOIN to only be ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN, and not include the second part. Is there a way I can condition this with an If statement, If ORDDIS_TBL.OIRESR is Null then do this join, if not, then do this? I'm confused how to get the proper result here.
3) select p.komorka,isnull(sum(ustalenia),0) from #plantemp p left join analiza_1 a on p.komorka=a.komorka where a.koniec between '20040701'and '20040731' group by p.komorka
komorka ustalenia (sum)
08.0000 SI/1788138.9300 SI/262856.8900
I need all rows from table 1 bat right and left join gives me the same results, WHY
As you can see the ParentIndexID and ChildIndexID fields refer to tblIndices.IndexID I would like a stored procedure as follows: show all index names and show the wights for the indexID you passed. This is what I have so far and it is not correct yet. Not sure what the syntax should be.
alter PROCEDURE [dbo].[uspBasketIndices_Get]
@IndexIDint
AS
select i.IndexID, i.[Name], bc.Weight from tblIndices as i left join tblBasketConstituents as bc on i.IndexID = bc.ParentIndexID and i.IndexID = @IndexID order by i.[Name]
Any one know any facts and figures about maximum Left Joins allowed (or recommended) in one query?
I am running a MS SQL 2000 my database is full of relational data and most of my foreign keys (INT data type) are a Clustered Indexed, Usually I will only be pulling one record from collection of about a dozen tables, but the Database is expected to grow fast and become big.
Right now I have a Stored Proc that has eight(8) LEFT JOINs in it. My worry is that this query will kill me as the database approaches 50,000 records.
Hi all. My query works fine, it generates reports but not my expected result.
select d.fullname, p.nickname, p.birthdate, p.birthplace, p.gender, p.civilstatus, p.religion, p.nationality, p. weight, p.height, p.haircolor, p.eyecolor, p.complexion, p.bodybuilt, p.picture, p.dialectspoken, d.mobilephone, d.prprovince,[Age] = dbo.F_AGE_IN_YEARS( birthdate, getdate() ), c.name, c.address, c.telno, c.email, c.occupation, ed.year1, ed.year2, ed.degree, sch.schname from hremployees as e inner join psdatacenter as d on e.empdcno = d.dcno inner join pspersonaldata as p on e.empdcno = p.dcno left join hrappempcharrefs as c on e.empdcno = c.empdcno left join hrappempeducs as ed on e.empdcno = ed.empdcno left join hrsetschools as sch on ed.schoolcode = sch.schcode
the above query gives a 77 records
if i ran "select * from hremployees" generates 60 records
i think the error is in the left joining. hrappempcharrefs, hrappempeducs and hrsetschools must be left joined to hremployees.
Hi Guys, I started working with linq and vb9.0 but i have a small problem i could feagure how to solve in c# but not in vb I wanted to make left join or right join on vb 9.0 and linq is it possible or this is only c# feature ? Waiting to hear from u guys, Thanks Softy
Hello! I need to write a query using left outer join and I'm having trouble with it. I have 2 tables:customer and cust_info. I want to pull all records from customer table and cust_info table even if there is no related data in cust_info table. But I need one condition in this query:only records from customer table where cus_type in ("A","B","C"). I don't need all other types ("D","E").
So my query looks like this:
select customer.cus_name,customer.cus_address,customer.cu s_type,cus_info.status from customer left outer join cus_info ON customer.sxdat_pk = cus_info.sxdat_pk and cus_type in ("A","B","C") AND cus_info.cus_table = 'CUSTOMER'
The result should be like this:
cus_name cus_address cus_type status Amoco 457 bent A new Bingo 47 lone oak C NULL Sears 1020 Magic dr. B exist
But my query pulls records for customers with type "D" and "E" that I'm trying to exclude from result.
I have to left join 2 tables. The first consists of columns:id, description, descr_num. The second table: id, descr_num, value.
I need to extract description from table1 where descr_num is in the range , say, 1-10.
LEFT JOIN
extract value from table2 ,descr_num should be in the same range. in table2 some values from (1,10) and desc_num could be not present. BUT i want this left join to be limited as i said.
Is there any solution to this prob. without creating temp tables. Or actually can i do such a join? Thank you
To join the table but MUST follow the condition as bitActiv = TRUE: select emp.nvcEmpName, emp.nvcEmpAddress, ety. nvcEmployeeType from cst_EmpProfile emp left join cst_EmpType on emp.intEmployeeTypee = ety.intEmpType and emp.bitActiv = 1.
But, the sql statement doesnt output the my expected result. Because the data row return must be 1st and 2nd row as it bitActiv = true. So, how's I going achieve what i want. tq.
Hi, I'm getting the error Syntax error (missing operator) in query expression 'dbPWork.id = dbPWorkWord.work_id LEFT JOIN words ON dbPWorkWord.word_id = words.id'." on the query below. Everything looks in place to me. Do you see anything that shouldn't work?
Query:
Code:
Code:
SELECT DISTINCT dbPWork.id, description_e as description, w_id , dateStart, dateEnd FROM dbPWork LEFT JOIN dbPWorkW ON dbWork.id = dbPWorkW.work_id LEFT JOIN words ON dbPWorkW.word_id = words.id
Everything works fine until I add the second LEFT JOIN statement. Any ideas? I'm stumped.
Hi, Iwant to make a left join between two tables connected with a specific field but I also want to have some criteria on the right table. I want to display all the rows from the first(left) table and only the rows from the secod (right) table that match the join and the criteria. However If I have criteria on the second table the join behaves like an inner join displaying only the rows that exist in both tables. In MS Access instead of the right table I use a query in which I enter the criteria and then I connect it with a left join in a new query in order to get the right results. How can I do itin MSSQLServer without using Views? Must I use a subquery with Exists and what is the right syntax in order to achieve this?
a job can be done with 1 or more operators I want to be able to show user both assigned and unassigned operator on the front end on a certain job so I haev only parm being passed in @jobID.
How do I got about showing all the operators but somehow differentiate ones already assigned to job
select a.[Cuno], [doc_type], [Doc_name] from [assembly] a left join [assembly_Doc] doc ON a.id = doc.assembly_id left join [assembly_Recog] re ON a.id = re.assembly_id where [activate] =1 and doc.doc_type = 1
I only have two records for doc_type =1 in the [assembly_Doc] table. Without joining the third table [assembly_Recog], the results is corrected, 2 records returned. I need to join with [assembly_Recog] table, but when it joined, I got the duplicate records. It listed twice. pls see the results below:
What is the logic of the below DELETE statement?It deletes all records where the file_modified column value is smaller then the maximum value in the same column, but not crystal clear how...
delete a from Staging a left join (select max(file_modifieddate) as file_modifieddate from Staging) b on a.file_modifieddate = b.file_modifieddate where b.file_modifieddate is null
