Joins On Same Table

Jul 20, 2005

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
Thanks

View 9 Replies

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?

Jun 25, 2007

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

View 11 Replies View Related

Table Joins!

Dec 5, 2007

Just curious if anyone has any in depth knowledge of how table join filtering works:

SELECT col1
FROM tbl1 a
INNER JOIN tbl2 b
ON a.col2 = b.col2
AND a.col3 = 1

(versus)

SELECT col1
FROM tbl1 a
INNER JOIN tbl2 b
ON a.col2 = b.col2
WHERE a.col3 = 1

Running some simple tests, the executions plans look identical. Does anyone know if/when either of these options would be preferential over the other? This is a rather difficult topic to Google and find any decent information.

Thanks!

View 5 Replies View Related

Why Two Joins On The Same Table ?

Apr 10, 2008

I was looking at some sample queries on how to do product promotion. Basically im after the top few products that have been ordered along with a given product ID. I think this should do it, but wasnt sure about the 2nd join...and why ?

SELECT
ProductID,
ProductName,
SUBSTRING(Description, 1, 150) + '...'
AS Description
FROM Products
WHERE ProductID IN
(
SELECT TOP 5 details2.ProductID
FROM OrderDetails details1
INNER JOIN OrderDetails details2
ON details1.OrderID = details2.OrderID
WHERE details1.ProductID = @ProductID
AND details2.ProductID != @ProductID
GROUP BY details2.ProductID
)

View 2 Replies View Related

3 Table Joins

May 16, 2008

I have 3 tables, students (studentId, studentName), classes (classId, className) and studentclass (studentId, classId, sessionId)

Want to display all studentnames, classnames and sessionIds, regardless of whether the student is enrolled in a specific class or not.
So the result set should show all students and all classes:
studentname classname sessionId
----------- --------- ---------
john english 2
john math <null>
jane english <null>
jane math 3

Please help in how to write such SQL?
Thanks in advance

View 6 Replies View Related

How Two JOINS From One Table

Jul 20, 2005

I got this problem where I need to look up values in two columns fromanother table.I can get OUTER LEFT JOIN working when looking up one column from a tablebut when looking up at two columns from a table I can't get the SQL syntaxcorrect.The scenario isA table has definitions for abbreviation of initials. From my search querythere are two columns of initials, I am having problems in writing an SQLstatement to look up and replace both columns of initials with theircorrect definitions. I was using an OUTER LEFT JOIN statementI am sorry if this isnt the correct Newsgroup to post this.

View 3 Replies View Related

Multiple Joins To The Same Table

Oct 29, 2006

I'm trying, with little success, to achieve something that should be quite easy (I think!) and any advice would be appreciated.

I have a leagues table structured so:

LeagueID | Name | Player1 | Player 2 ... Player6

and the data in the player columns is a userid from the users table and I'm trying to display the Leagues but with the player names rather than player IDs.

I'm working along the lines of

Code:

select
u1.displayname as Player1,
u2.displayname as Player2
from DCMLeagues as L
inner join Users as u1 on L.player1 = u1.userid
inner join Users as u2 on L.player2 = u2.userid

but with little success so far. Any thoughts would be appreciated! Thanks very much in advance.

-- Chris

View 5 Replies View Related

Table Self-joins With Updates

Sep 2, 2005

table = PEOPLE

Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 0 T
Steve 0 T
Paul 0 T

I'm trying to add-up all of the Money values for each Name and store them into their names, but under Type 'T'.

after the update command it should look like this

Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 700 T
Steve 500 T
Paul 600 T

View 1 Replies View Related

Find All Joins To A Table

Apr 25, 2008

Hello team.

I have an issue I'm hoping you can help me with.

I have very large sql server with 15 databases and thousands of tables. We have an "employee" table where we have historically been joining to the "EmployeeName" field in procs, views, etc.(bad practice, I know). I would like to now go back and make things right. I would like to identify ALL procs and views that have a join on the "EmployeeName" field, and modify it to use the "employeeid" field. I would like a script or a suggestion that would help me identify all the places where I would need to make this change.

I thought of querying the syscomments table, but the joins are not always laid out the same way so I know I wouldn't be able to catch all of them. Maybe using profiler to capture all statements executed and have them trigger an email to a DB developer every time? I don't know... Suggestions?

Many Thanks

View 2 Replies View Related

Problem With 2 Joins From Same Table

Aug 6, 2007

Hi I'm new to SQL and I'm having some problems with the following join. I have a table, Ticket, with two int fields, Submitter and Acceptor, among many others. These fields reference the ID of users stored in the Users table and are usually set to different IDs (ie. they are independant fields).

Here is the relevent SQL to get the ticket records (generated by the Query Editor in SQL Management Studio Express):

SELECT Users.Name, Tickets.ID
FROM Tickets INNER JOIN
Users ON Tickets.Submitter = Users.ID AND Tickets.Acceptor = Users.ID

Problem is, this only returns records where the submitter and acceptor IDs are the same which is rarely the case. What am I doing wrong here? I've created a lot of joins for other records without problem - this is the only case where two fields are coming from the same table.

Thanks for any help.

View 2 Replies View Related

Table Joins On More Than One Field

May 4, 2006

Hello all,Can someone help me with this SQL?1) EMPLOYEE table has columns 'employee_id' and 'emp_sid' and othercolumns2) EMPLOYEE_BENEFITS table has a column called employee_entity, thiscolumn can be joined to either 'employee_id' OR 'emp_sid' but not bothin the EMPLOYEE table.3) EMPLOYEE_TRACK table has column called employee_track_entity, thiscolumn can be joined to the employee_benefits_id (PK) of theEMPLOYEE_BENEFITS table.I am listing the sql for the tables (the tables shows only the columnsin question)CREATE TABLE [dbo].[EMPLOYEE] ([employee_id] [int] IDENTITY (1, 1) NOT NULL ,[empsid_id] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_BENEFITS] ([employee_benefits_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_entity] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_TRACK ] ([employee_track_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_track_entity] [int] NOT NULL) ON [PRIMARY]GOSELECT * FROM EMPLOYEE eINNER JOINEMPLOYEE_BENEFITS eb ON (e.employee_id = eb.employee_entity ORe.empsid_id = eb.employee_entity)INNER JOINEMPLOYEE_TRACK et ON eb.employee_benefits_id = et.employee_track_entityThe above SQL I wrote is this: the second inner join uses a OR to joineither of the columns in the first table EMPLOYEE. There is performancedegradation with this SQL. With huge data It takes about 30 seconds toexecute. I know this is not the perfect way to do it, can anyone of theSQL Gurus please enlighten me to a faster approach?If I dont use the OR I can try left join on the same tableEMPLOYEE_BENEFITS twice by changing the join types, but If I did thiswhat table alias can I use to join to the 3rd table?SELECT * FROM EMPLOYEE eLEFT JOINEMPLOYEE_BENEFITS eb1 ON e.employee_id = eb.employee_entityLEFT JOINEMPLOYEE_BENEFITS eb2 ON e.empsid_id = eb.employee_entityINNER JOINEMPLOYEE_TRACK et ON [???].employee_benefits_id =et.employee_track_entitythanksadi[Sorry I am posting this twice, on SQL Programming forum too]

View 3 Replies View Related

Multiple Joins On The Same Table

Jan 5, 2006

I'm new to SQL 2005 & C# - I'm a MySQL/PHP crossover.

I'm using s Stored Procedure and I'm trying to do multiple joins onto
one table. I have 6 fields in one table that are foreign keys of
another table:

Table1

---------

id

PrimaryCode

SecondaryCode1

SecondaryCode2

SecondaryCode3

SecondaryCode4

SecondaryCode5

Table 2

---------

id

Title

CommCode

The fields in table 1 (except is obviously) hold the id of a row in
Table 2. When displaying data I want to display "Title" -
"CommCode" for each item in Table 1. I got myself started by
searchig on the net and I have a stored procedure. The obvious
problem is that as it goes through the Query only the last value
remains in place - since each value before it is cleared in the
UNION. How can I do this?? Here's my Stored Procedure:

=====================================

ALTER PROCEDURE GetRegistersSpecific

@SearchTxt int

AS

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
PrimaryCode AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON PrimaryCode = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode1 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode1 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode2 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode2 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode3 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode3 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode4 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode4 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode5 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode5 = CommodityCodes.id

WHERE registrations.ID = @SearchTxt

=====================================

Thanks

View 4 Replies View Related

Table Joins (Mistake Corrected)

Aug 29, 2002

I have a correction to my previous thread all values are '83' not '80' My mistake

Message as it should be:

am new to SQL.

MY issue: Table (A) has the customerID field as a primary key. Table(b) has a customerID field but isn't the primary key. I am selecting all records from Table(A) and Table(B) Where Table(A).customerID=83 AND Table(B).customerID=83.

Table(A) has the customerID '83' Table(B) doesn't. The int 83 doesn't exist in table(B)

The various joins I constructed always returns (0 row(s) affected). When I enter a value that is inboth tables I get the desired result.

MY Goal:
I would like one record returned from table(A) if the customerID is not present in table(B) and one record that has a single customerID in both table(A)and Table(B)

Please advise.

Thanks

View 1 Replies View Related

Which Is Best? Select From Joins Or Single Table

Jun 3, 2008

Hi,

I am using a SP which one has lot of Joins(More than 10 tables).

For every request from user SP executed, and the DB performance got slow.

For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table.

I thought if the user selects from single table means the performance will increase.

I don't know whether this one is a right solution.

I am very new to SQL server. Help me for this Problem.

Thanks in advance.

View 1 Replies View Related

Which Is Best? Select From Joins Or Single Table

Jun 3, 2008

View 6 Replies View Related

Multiple INEER JOINS Between Two Table.

Sep 16, 2007

Hello Everyone,

I have a quite unusual problem, and I have hard time finding the answer.

I have a table with Locations - lets say that it has just ID, and Name,
and a Transport table containing the ID, ArrivalLocationID and DepartureLocationID.

Now - when I select the Transport table I want to get names of the Arrival and Departure locations from th other table.

If it was a single link I woul do an INNER JOIN like:

SELECT
Transport.*,
Locations.Name AS ArrivalLocation
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
Transport.ArrivalLocation = Locations.ID

But I want to do a double INNER JOIN between two same table. And here I get a problem - how to do it? Something like:

SELECT
TransportProductOperationPeriods.*,
Locations.Name AS LArrivalLocation,
Locations.Name AS LDepartureLocation,
Locations.ID AS LArrivalLocationID,
Locations.ID AS LDepartureLocationID
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
TransportProductOperationPeriods.ArrivalLocation = LArrivalLocationID
INNER JOIN Locations ON
TransportProductOperationPeriods.DepartureLocation = LDepartureLocationID

but obviously that does not work ;)

Thanks in advance,
Joseph

View 2 Replies View Related

Table Joins Causing Duplicates?

May 15, 2015

I have a table with call data (ContactID, Queues Entered, Call Status, Date & Time Stamps etc). Each entry relating to a contact ID goes onto a new row. The first row for a contact is the date and time it is created. It then captures the queue (or queues) it enters before it is answered. Finally, it captures when the call is released (Completed).

I'm trying to link all this data into one single row per contact ID to make it easier to report on.

I started off by using DISTINCT to pull back all of the Contact ID's. I then used a Left Join to pull back the date and time of creation. I created a further Left Join to pull back the first queue that it entered and so on.

When I did this, I started getting duplicates. This is because some calls enter more than one queue.

How can I do this so that it only has one ContactID per row. Also, for the Queue, is there anything I can do to ensure it pulls back the first Queue it enters? (These are time stamped). Subsequently, I would then need to add the second and third queue it enters in other columns. (A call can enter a maximum of 3 queues).

View 8 Replies View Related

Joins On Views That Are Formed With Outer Joins

Nov 3, 2000

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.

Any ideas on the principles involved here.

View 1 Replies View Related

Update Table With Joins And Aggregate Function

Apr 3, 2007

Is this possible? What I am looking for is something like:UPDATE T_SitesSET T_Sites.LastDate = T_Inspections.DateFROM T_SitesINNER JOIN T_Assets ON T_Sites.SiteID = T_Assets.AssetIDLEFT OUTER JOIN T_Insecptions ON T_Assets.AssetID = T_Inspections.AssetID-- But I need only the last inspection done on the site (including if it is null)

View 1 Replies View Related

In Outter Joins, What Makes A Table Either Right Or Left?

Dec 16, 2002

Until today, I was always under the impression that left vs. right was determined by which side of the comparison operator the table was located.

In other words:
LEFT JOIN LeftTable.ID = RightTable.ID

would pull all the records from LeftTable and those that matched from from RightTable and that:

RIGHT JOIN RightTable.ID = LeftTable.ID

would pull exactly the same result set but I was wrong. So, if it is not the table position in relation to the comparison operator, is it simply that the tables listed first in the FROM clause aren the ones "Left" of those subsequently entered?

View 2 Replies View Related

Multi Table Query Using Two LEFT JOINs

Jul 24, 2012

I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.

SELECT
quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
FROM quote
LEFT JOIN machining_operations
ON machining_operations.quote_num = quote.quote_id
LEFT JOIN parts
ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
GROUP BY quote.quote_id

View 10 Replies View Related

Report Model: How Do I Handle Table Joins?

Jan 23, 2008

Hi,

I'm only new to Reporting services and have just started working on a report model for ad hoc reporting.

I have around 50 tables, many of them related through FK's. How can I handle this when designing the model?

E.G

I have a Table called Posts, which has a Hospital ID which is an FK to the Hospitals table. A relationship exists between the two in my Data Source View, however I cannot access the Hospital Name (stored in the Hospital table) using the report builder.

I'd greatly appreciate any help you can give.

Thanks,
John

View 3 Replies View Related

Query To Find Manager Name From Employee Table Without Joins

Oct 25, 2012

Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins

Note:- also need records who doesn't have manager (null)

table structure
eid------ename------mgrid
1------Nancy------2
2------Andrew------null
3------Janet ------2
4------Margaret------2
5------Steven------4
6------Michael ------5

o/p
Employee------Manager
Nancy------Andrew
Andrew------Null
Janet ------Andrew
.
.

View 9 Replies View Related

Multiple Table Joins Makes Query Go To Sleep

Jul 23, 2005

Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed

View 9 Replies View Related

Nested Joins - Joining One Table To Another Multiple Times

Jan 9, 2006

Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks

View 1 Replies View Related

SQL Server 2012 :: How To Get Most Recent And Oldest From Joins To A Child Table

Jul 23, 2014

I have the following tables in my DB

Employee table - This table has EmployeeID, Name, DOB.

EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.

EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.

I want my query to output me the following fields...

EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary

Here is a piece of code to generate sample data sets

DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')

DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')

[Code] ....

Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.

selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,

[Code] ....

View 5 Replies View Related

Conditional Joins? (table Structure And Sample Data Provided)

Nov 13, 2007

Hello, can anyone tell me if it is possible to conditionally join tables? If so, how could it be done?

What I would like to do is create a query similar to the one below but include the rows in the Asset table where the corresponding Alert field is NULL.

SELECT A.AssetId, A.IndustryId, A.RegionId, A.RevenueId, AL.AlertId

FROM Alerts AS AL INNER JOIN

Assets AS A ON AL.IndustryId = A.IndustryId AND AL.RegionId = A.RegionId AND AL.RevenueId = A.RevenueId
WHERE AlertId = 1

The output I am after would be the first row in the Assets table. But since the RevenueId column is NULL, I get nothing. The only time the above query will work is if all three Id columns are populated. That is not always going to be the case. Please don't suggest changing table structures or adding data. That is not an option.

Below are the table structures and sample data.

CREATE TABLE [dbo].[Alerts](
[AlertId] [int] NOT NULL,
[IndustryId] [int] NULL,
[RegionId] [int] NULL,
[RevenueId] [int] NULL,
CONSTRAINT [PK_Alert] PRIMARY KEY CLUSTERED
(
[AlertId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Assets](
[AssetId] [int] NOT NULL,
[IndustryId] [int] NOT NULL,
[RegionId] [int] NOT NULL,
[RevenueId] [int] NOT NULL,
CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED
(
[AssetId] ASC,
[IndustryId] ASC,
[RegionId] ASC,
[RevenueId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (1, 2, 5, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (2, 2, 5, 1)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (3, 2, NULL, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (4, NULL, 5, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (5, 3, NULL, 4)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (6, NULL, 4, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (7, 3, 4, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (1, 2, 4, 3)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (1, 2, 5, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (2, 2, 5, 5)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (2, 3, 4, 5)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (3, 2, 4, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (4, 2, 5, 4)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (5, 3, 5, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (5, 2, 4, 5)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (5, 3, 1, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (6, 3, 2, 4)

Thanks.

View 7 Replies View Related

Transact SQL :: Capture Data Change From Multiple Table Joins

Jun 9, 2015

I have 5 tables that are joined respectively,

Each one of the tables listed below has a “CreateDateTime” and “UpdateDateTime” fields, I need to get yesterday changes, I can get any record where either CreateDateTime or UpdateDateTime is greater than midnight yesterday butI need to watch dates on all of the tables so I need to do atleast 10 date checks.

If any table shows an updated or created record, I need to gather ALL of the information for that customer. So, if my name didn’t change (SCUS table), but my email does (SEML table), I have to pull out both the SCUS and SEML tables (and the others, of course). So It may not be simple WHERE clause, How can I achieve this:

SELECT
SCUS.CUSFULLNAME
,
SCUS.CUSMIDDLENM
,
SCUS.CUSLASTNM ,

[Code] ....

View 3 Replies View Related

JOINS To Sub-Queries -vs- JOINS To Tables

Aug 11, 2005

SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig

View 3 Replies View Related

Ansi Joins Vs. SQL Joins

Oct 12, 1999

Hi,

Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?

The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)

However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.

Angel

View 1 Replies View Related

UNION ALL, Joins And No Joins

Feb 29, 2008

I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,

Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.

Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC

Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.

View 14 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

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

Multiple Left Joins (2 Left Joins 1 Big Headache)

Sep 1, 2005

Hi All,

Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.

My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx

I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.

View 2 Replies View Related