INNER JOIN: Joining Two Different Colums With One Table?
Nov 15, 2004
Hello everyone,
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_SubCategories.SubCategoryName,
_SubCategories.SubCategoryName,
LinkName
FROM
_Links
INNER JOIN
_SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId
INNER JOIN
_SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_Links_Categories.CategoryName,
_Links_StatusCategories.StatusName,
LinkName
FROM
_Links
INNER JOIN
_Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId
INNER JOIN
_Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
Table_Categories: CategoryId (Primary Key), CategoryName
Table_SubCategories: CategoryId, SubCategoryId (Primary Key), SubCategoryName
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
Alec
View 3 Replies
ADVERTISEMENT
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
View 6 Replies
View Related
Oct 1, 2001
How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies
View Related
Dec 14, 2007
Hello, and thanks for taking the time to read this.
NOOB question:
In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:
CREATE TABLE [dbo].[T_Catalog](
[StlyeID] [int]
) ON [PRIMARY]
with data:
1
2
the SIZES table (filled in by the customer with all the size ranges they carry) is:
CREATE TABLE [dbo].[T_Sizes](
[SizeID] [int],
[SizeName] [nchar](10)
) ON [PRIMARY]
with data:
1,Small
2,Medium
3,Large
4,Xtra-Large
and the AVAILSIZES table would be:
CREATE TABLE [dbo].[T_AvailSizes](
[StyleID] [int],
[SizeID] [int]
) ON [PRIMARY]
1,1
1,2
1,3
2,1
2,3
2,4
Basically, then, we know that:
style 1 comes in Small, Medium, and Large
style 2 xomes in Small, Large, and Xtra large
WE know that, but getting SQL to tell us that is a major PIA!!
Now,
SELECT
t_Catalog.StyleId, t_AvailSizes.SizeID
FROM
t_Catalog
INNER JOIN t_AvailSizes ON
t_Catalog.StyleId = t_AvailSizes.StyleId
will give me a nice list of each item number with a separate row for each size number.
My questions are:
How do I get the size NAMES?
How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?
Is this the right way of doing this?
In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.
Thanks for any help and information you can provide.
View 2 Replies
View Related
Jul 20, 2005
What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
View 1 Replies
View Related
Jun 21, 2006
The problem:I'm updating a report to be "multi-language" capable. Previously,any items that had text associated with them were unconditionallypulling in the English text. The database has always been capable ofstoring multiple languages for an item, however.Desired output:Given the test data below, I'd like to get the following resultsselect * from mytestfunc(1)Item_Id, Condition, QuestionText1876, NOfKids <= 10, This many children is unlikely.select * from mytestfunc(2)CheckID, Condition, QuestionText1876, NOfKids <= 10, NULLThe current SQL for my UDF:CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,tblQuestionText.QuestionTextFROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =tblQuestionText.Item_ID)WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR(tblQuestionText.LanguageReference IS NULL)))Test data:CREATE TABLE [dbo].[tblCheck] ([Item_ID] [int] NOT NULL ,[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblCheck] ADDCONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED([Item_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblLanguage] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Priority] [int] NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Spoken] [bit] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADDCONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] ADDCONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR[CreationDate],UNIQUE NONCLUSTERED([Priority]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblQuestionText] ([Item_ID] [int] NOT NULL ,[LanguageReference] [int] NOT NULL ,[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SameAs] [int] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblQuestionText] ADDCONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED([Item_ID],[LanguageReference]) WITH FILLFACTOR = 90 ON [PRIMARY]GOINSERT INTO tblCheck (Item_ID, CheckDescr)VALUES(1876, 'NOfKids <= 10')INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(1,1,'English', 1)INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(2,2,'Espanol', 1)INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)VALUES (1876, 1, 'This many children is unlikely.')Any tips or pointers will be appreciated. Thanks.
View 2 Replies
View Related
Jan 20, 2006
Setup:
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following:
OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?
Thank you
View 3 Replies
View Related
Apr 20, 2015
I run into a problem when asking to show a query of employee vacation days.
table 1:
column1 is dates
e.g.
2015-01-01
2015-01-02
2015-01-03
.
.
.
2015-12-31
table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012
My query output need to be:
2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
View 4 Replies
View Related
Jun 4, 2015
I am looking for an alternate logic for below-mentioned code where I am inserting into a table and having left join with the same table
insert TABLE1([ID],[Key],[Return])
select distinct a.[ID],cat1,cat2 from
(select ID,[Key] Cat1 ,[Return] cat2 from @temp as temp) a left join TABLE1 oon a.ID= o.ID
and a.Cat1 = o.[Key]
and a.cat2 = o.[return]
where [key] is null order by ID
View 2 Replies
View Related
Jan 26, 2015
I have tables Companies, CompaniesDetails (the company branches), Addresses and Companies_Addresses.
The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,i.e., they are linked to Companies and CompaniesDetails via CompanyID and CompanyDetailID and to Addresses via addressID.
Companies_Addresses
id (PK)
companyID (FK)
companyDetailID (FK)
addressID (FK)
I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement.
Code:
SELECT DISTINCTAddresses.city as branchCity, Addresses.street as branchStreet
FROMCompanies
LEFT JOINCompaniesDetails AS cd ON companies.companyID = cd.companyID
LEFT JOINCompanies_Addresses AS c ON c.companyDetailID = cd.companyDetailID
LEFT JOINAddresses ON c.addressID = Addresses.addressID
WHERE Companies.name LIKE 'abc'
ANDCompanies.status_indicator like 'Current'
View 8 Replies
View Related
Dec 6, 2005
If a table gets joind with itself and then joined again with itself, is it possible to perform one kind of outer join from the third table to the second tabe and having the range of records in the second table limited to the joins between the first and the second table?
Join on RegionID Join on RegionID and City
Show records not qualifying for the joint
but limited to the scope of of records
established by the first joint
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City New York | | City New York|
|________________| |_______________|
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City New York | | City New York |
|________________| |_______________|
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City Buffalo | | City New York |
|________________| |_______________|
Plotin
View 3 Replies
View Related
Jul 19, 2007
hi friends can any body help fo rthe below issue:
stock
========
Item cost
pen150
pencil 150
stockdt
=========
Item qty
pen10
pen15
pencil 10
pencil 15
for the above two table of stock,stockdt the o/p should come like this
Item cost qty std.cost
pen 150 25 3750
pencil 150 25 3750
View 1 Replies
View Related
Nov 13, 2007
Hi guys,
This is regarding the joins...for instance I have three Tables A, B, C. And they are related to each other. If I am joining A->B,B->C then is it necessary to join A->C too? what happens if I omit join A->C? and what happens if I include join A->C too?
Thanks,
View 7 Replies
View Related
Sep 7, 2005
Hello allLet's say I have 1 table "contract" containing the following data:id year sales45 2005 10045 2004 9589 2005 25089 2004 27512 2005 42I want to make a table with one unique row for each id and then a column for2004 sales and 2005 sales, like this:select a.id, a.sales, b.salesfrom contract a, contract bwhere a.contract=b.contract(+)and a.year=2005and b.year=2004The rows for id 45 and 89 are shown perfectly. But id 12 is not shown at allbecause it doesn't have a record for 2004!! I don't know why 'cause Iouterjoined the tables.It works perfectly when I have two distinct tables for each year (forinstance contract_2005 and contract_2004). So the problem seems to be in thefact I like to join one table with itself.Someone has a solution for this?thanks!Maarten
View 2 Replies
View Related
Sep 20, 2013
What sort of script would convert a pre-existing table into the second below?
I only want to merge the columns with Primary_IDs 1111 & 3333 to have the same Secondary_ID values, without duplicating any similar Secondary_ID values between the 2 which I've marked in red below.
Code:
TABLE A
==========
Primary_ID | Secondary_ID
1111 | 1
1111 | 2
1111 | 3
1111 | 4
3333 | 1
3333 | 2
3333 | 10
3333 | 20
3333 | 100
3333 | 200
5555 | 12
5555 | 34
7777 | 56
7777 | 78
Code:
NEW TABLE A
==========
Primary_ID | Secondary_ID
1111 | 1
1111 | 2
1111 | 3
1111 | 4
1111 | 10
1111 | 20
1111 | 100
1111 | 200
3333 | 1
3333 | 2
3333 | 3
3333 | 4
3333 | 10
3333 | 20
3333 | 100
3333 | 200
5555 | 12
5555 | 34
7777 | 56
7777 | 78
View 11 Replies
View Related
Oct 1, 2013
I am trying to convert the following sql to linq
SELECT R.UserID,R.Cntrl_nbr FROM User R
WHERE (
R.REG_ID=
(SELECT MAX(B.REG_ID)
FROM User B
WHERE R.UserID = B.UserID )
The linq I am using is
var query = (from n2 in q1
where
n2.RegServiceTs == (q1.Where(c1 => c1.UserID == n2.UserID).Max(c2 => c2.REG_ID))
select n2).SingleOrDefault();
This is slow and is there a better way to do this?
View 1 Replies
View Related
Oct 8, 2013
I need the last year sales(lastyearsales), first name, last name and city from the following tables. The relation is as following:
SalesPerson<->Employee <- EmployeeAddress<-Address
SalesPerson<->Employee <-Contact
SalesPerson Table
1.SalesPersonID
lastyearsales
Employee Table
1.EmployeeID
2.ContactID
Contact Table
2.ContactID
FirstName
LastName
EmployeeAddress Table
1.EmployeeID
3.AddressID
Address
3.AddressID
City
And my answer:
Select: C1.firstname, C1.lastname, SP.lastyearsales, A.city
From: SalesPerson as SP join Employee as E1 on Sp.salespersonID = E1.EmployeeID
Right Join Employee as E2 on E1.EmployeeID=E2.EmployeeID join Address as A on E2.AddressID = A.AddressID
Right Join Employee Contact as C1 on E1.ContactID = C1.ContactID
View 2 Replies
View Related
Jul 16, 2014
In the following example, I have a "Person" table, and a many-to-many "Relationship" table which stores the r/ship between any two people:
[Person] [Relshp]
---------------- ---------------------
| PersonId PK|<-.-. | RelshpId PK |
| PersonName | '-| PersonId |
---------------- '--| RelatedToPersonId |
| RelationshipType |
---------------------
There are 2 FK constraints, linking
1. [Relshp].[PersonId] to primary key [Person].[PersonId], and
2. [Relshp].[RelatedToPersonId] to primary key [Person].[PersonId].
What kind of index structure would best support those FK constraints?
Would it be:
a) One combined index:
CREATE INDEX IX_Relshp ON Relshp (PersonId, RelatedToPersonId)
or
b) Two indexes:
CREATE INDEX IX_RelshpP ON Relshp (PersonId)
CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId)
or
c) Two "mirrored" combined indexes:
CREATE INDEX IX_RelshpP ON Relshp (PersonId, RelatedToPersonId)
CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId, PersonId)
View 3 Replies
View Related
Oct 15, 2006
Hi,
Sorry if this has been asked before, I looked through the FAQ but could not find an answer to the following.
I have a Project table which contains amongst other fields, a CreatedByID field and a LastModifiedByID field. Both these fields point to a User table. What I would like to do is get the two usernames from a query on the project table.
I know how to get one usename using the following Sql command but how do I get access to the second username ?
SELECT Project.Name,User.Username FROM Project,User WHERE Project.ID=@id AND User.ID = Project.CreatedByID;
I hope my ramblings make sense
Andy
Andy
View 2 Replies
View Related
Dec 19, 2007
use default pubs database in sqlserver2000.
use authors table and publishers table.
Write a query to list first name, last of all authors
and name of the publisher (if any) present in the same city
as the author. If no publisher is present in the city
where the author is located then the column should contain a
NULL value. If there is more than one publisher in the city
where the author is located, then the details of
the author are to be repeated for each publisher.
but there is no field match between authors table and publishers table.
View 9 Replies
View Related
Jul 23, 2005
My main table has the following structure:t1 (id_primary, id_secundary, name) i.e. [(1,1,"name1"), (2,1,"name2")]I want to join this table with the following second table:t2 (id_primary, id_secundary, value) i.e. [(1, NULL, "value1"),(NULL,1,"value2")]The join should first try to find a match on id_primary and only if thatfails it should find a match on id_secundary. Every row in t1 is matchedagainst a single row in t2.The following query works:selecta.name, isnull(b.value, c.value)fromt1 a left outer join t2 b on a.id_primary = b.id_primaryleft outer join t2 c on a.id_secundary = c.id_secundaryI'm wondering though if it would be possible to write a query that only usest2 once, since it actualy is quite a complex query that is calculated twicenow. Any ideas (besides using a temp table)?
View 3 Replies
View Related
Apr 29, 2008
I have query that takes 12 minutes to execute. The query uses around 9 tables but I have narrowed down the problem to one table that has over 65 million rows. The problem table has only 3 fields
FieldOne (PrimaryKey)
FieldTwo Varchar(3000)
FieldThree Varchar(3000)
The query uses the primary key of this table to perform the join. FieldTwo and FieldThree are only used as output parameters.
I noticed if I remove FieldTwo and FieldThree from the output (but still leave the table in the query), the query executes in 1 second. However if I include FieldTwo and FieldThree in the output, the query takes over 12 minutes to execute.
I cannot index FieldTwo and FieldThree because of the field size and I cannot reduce the size of the fields because of the data that needs to be stored in it? How can I index or do something similar to speed up the table look up.
View 1 Replies
View Related
Apr 9, 2008
Dear friend, the following is my query.
i am retreiving from the same table with different condition
1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'
2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'
this two query gives resulsets
i need to join this two resultsets. please give me sample query to join this two result sets
View 7 Replies
View Related
May 30, 2008
Dear all,
I have four different tables with three columns. The first two columns are equal for all tables. The third column is different:
Table I
COUNTRY | PRODUCT | SALES VALUE
AAA AAA 10
AAA AAB 10
Table II
COUNTRY | PRODUCT | SALES COST
AAA AAC 10
Table III
COUNTRY | PRODUCT | SALES MARGIN
AAB AAA 10
Table IV
COUNTRY | PRODUCT | SALES XXX
AAA AAA 5
What I would like to accomplish is to obtain a table where the records from these tables would be joined in a single table:
COUNTRY | PRODUCT | SALES VALUE | SALES COST | SALES MARGIN | SALES XXX
AAA AAA 10 0 0 5
AAA AAB 10 0 0 0
AAA AAC 0 10 0 0
AAB AAA 0 0 10 0
Can you advise me on what the best way to achieve this is? Thanks!
Kind regards,
Pedro Martins
View 4 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
Jun 8, 2007
Hello all,
I am Using MSSQL and ASP Classic for this project. Let me explain my problem, i have two tables:
Users
ID
Username
ChangeLog
ID
Description
UserID
DateChanged
The Users table will contain one record for each user in the system.
The ChangeLog table can contain any number (including 0) of rows relating to each user, when a change to the users account is made, and entry is put into the ChangeLog table.
The object of my report is to display a list of all users in the system and display the date and description of the last change made to the user.
So far i have written this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM Users
LEFT OUTER JOIN ChangeLog ON (ChangeLog.UserID = Users.ID)
ORDER BY ChangeLog.DateChanged DESC
However, if a user has got more than one change against it then a row will be returned for each change instead of just showing the latest change. So i then tried this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM ChangeLog
LEFT OUTER JOIN Users ON (Users.ID = ChangeLog.UserID)
ORDER BY ChangeLog.DateChanged DESC
The problem with this one? It only shows users that have actually had a change made to them, and the report *needs* to display ALL users regardless of wether they have been changed or not.
I'd appretiate any help you can give me at all, thanks in advance!
View 5 Replies
View Related
Jan 2, 2007
Dear All,
What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.
I'd like to select every record in table A but only joining the last relevant record from table B. So:
Table A:
A1 Prj1
A2 Prj2
Table B:
B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007
So I'd like to list using the most efficient way this:
A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007
I'm assuming this is NOT the most efficient way:
select A, (select top 1 date from B orderBy ...)
Any suggestions?
View 6 Replies
View Related
Dec 4, 2007
I wonder if you can help...
I have a simple setup: 2 tables and a joining table, and want to retrieve a data set showing every possible combination of table A and table B together with whether that combination actually exists in the joining table or not.
My tables:
channels
======
channel_id
channel_name
items
====
item_id
item_name
channels_items (joining table)
===========
channel_id
item_id
created
An example of the dataset I want (assuming 2 items and 2 channels, with itemA not being in channelB):
item_id item_name channel_id channel_name exists
======= ========= ========== ============ ======
1 ItemA 1 ChannelA True
2 ItemB 1 ChannelA True
1 ItemA 2 ChannelB False
2 ItemB 2 ChannelB True
I'm completely stuck on how to achieve this. Any guidance would be very much appreciated.
View 2 Replies
View Related
Oct 19, 2015
I have a lookup table with 4 levels of codes like follows:
create table #RiskElementCategory(
[RiskElementCategoryCode] [nchar](5),
[RiskElementCategoryCodeDsc [nvarchar](50),
[RiskElementCategoryCode_2] [nchar](5),
[RiskElementCategoryLevel2Dsc] [nvarchar](50),
[Code] ...
Along with some other rows with the same format. I need to join to this table using a RiskElementCode that I get from the Source system. The trick is that it can be at any level, but I don't know which level it is at. So what I have to do is somehow get the correct row from the lookup table based on the code from the source to get the correct level.
So for Example, If i receive the RiskElementCode of 'SSR', that is in column RiskElementCategoryCode_3 so I need the row that has 'NA' for anything after RiskElementCategoryCode_3 where RiskElementCategoryCode_3 = 'SSR'. If i get 'DFR' I need to get the row where RiskElementCategoryCode_4 = 'DFR' since there are no levels deeper than 4 i don't need to check anything else. If I get 'PRR', then I need the row where RiskElementCategoryCode = 'PRR' and code_2, code_3 and code_4 = 'NA'.
So besides getting the correct row based on the code, i need to get the correct row based on the level where the next levels are 'NA'. I should only get 1 row each time.
View 2 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
Aug 17, 2015
I have two tables: "Users" and "Messages".
The Messages table has a "UserFromID" and an "UserToID", indicating a message was sent from a user to a user.
The Users table has a UserID, FirstName, LastName and UserGroup.
Im trying to get all messages sent by or to a user in a specific group, with the First and Last names of the sending and receiving users.
Here is my tables and data:
CREATE TABLE tmp_users (userID INT, GroupID INT, UserName VARCHAR(10), FullName VARCHAR(100))
CREATE TABLE tmp_Messages (aKey INT IDENTITY, FromUserID INT, ToUserID INT, msg VARCHAR(100))
INSERT INTO tmp_users VALUES (1,1,'John','John Smith')
INSERT INTO tmp_users VALUES (2,1,'Jane','Jane Doh')
INSERT INTO tmp_users VALUES (3,1,'Bob','Bob Newheart')
[code]....
The query Im running so far is wrong, but here it is...
SELECT t.FromUserID, t.ToUserID, t.msg, u.UserName AS UserFrom,
u.GroupID AS FromGroup, u2.UserName AS UserTo, u2.GroupID AS ToGroup
FROM tmp_Messages t
LEFT JOIN (SELECT UserID, GroupID, UserName FROM tmp_users WHERE GroupID = 3) u
[Code] .....
im missing the details of one of the users.I know what the problem is, I just cant figure out how to get this working without using temp tables, which I cant do in the production version.
View 3 Replies
View Related
Dec 1, 2005
Hello,I am currently working on a monthly load process with a datamart. Ioriginally designed the tables in a normalized fashion with the ideathat I would denormalize as needed once I got an idea of what theperformance was like. There were some performance problems, so thedecision was made to denormalize. Now the users are happy with thequery performance, but loading the tables is much more difficult.Specifically...There were two main tables, a header table and a line item table. Thesehave been combined into one table. For my loads I still receive them asseparate files though. The problem is that I might receive a line itemfor a header that began two months ago. When this happens I don't get aheader record in the current month's file - I just get the record inthe line items file. So now I have to join the header and line itemtables in my staging database to get the denormalized rows, but I alsomay have to get header information from my main denormalized table(~150 million rows). For simplicity I will only include the primarykeys and one other column to represent the rest of the row below. Thetables are actually very wide.Staging database:CREATE TABLE dbo.Claims (CLM_ID BIGINT NOT NULL,CLM_DATA VARCHAR(100) NULL )CREATE TABLE dbo.Claim_Lines (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_LN_DATA VARCHAR(100) NULL )Target database:CREATE TABLE dbo.Target (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_DATA VARCHAR(100) NULL,CLM_LN_DATA VARCHAR(100) NULL )I can either pull back all of the necessary header rows from the targettable to the claims table and then do one insert using a join betweenclaims and claim lines into the target table OR I can do one insertwith a join between claims and claim lines and then a second insertwith a join between claim lines and target for those lines that weren'talready added.Some things that I've tried:INSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLGROUP BY T.CLM_ID, T.CLM_DATAINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Target.dbo.Target TINNER JOIN (SELECT CL.CLM_IDFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID =CL.CLM_IDWHERE C.CLM_ID IS NULL) SQ ON SQ.CLM_ID = T.CLM_IDI've also used EXISTS and IN in various queries. No matter which methodI use, the query plans tend to want to do a clustered index scan on thetarget table (actually a partitioned view partitioned by year). Thenumber of headers that were in the target but not the header file thismonth was about 42K out of 1M.So.... any other ideas on how I can set up a query to get the distinctheaders from the denormalized table? Right now I'm considering usingworktables if I can't figure anything else out, but I don't know ifthat will really help me much either.I'm not looking for a definitive answer here, just some ideas that Ican try.Thanks,-Tom.
View 2 Replies
View Related
Jun 2, 2015
I have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc. See U1 - U6
What is a better way to return this information rather than multiple joins?
Select
[AccountNumber]
,a.[AccountId]
,[Name]
,[new_LocationID]
,[ws_name]
,[new_BillingManageruserName]
,[new_AreaServiceManName]
[Code] ....
View 3 Replies
View Related
