MS SQL Server :: Join Problem With Non Matching Data

Merge Join Not Matching Correctly

Mar 31, 2007

And by correctly, I mean the way it *should* match, of course.I've got 2 data sources, using a left outer join, matching 2 columns. Whatever is the right side of my table is never matching and returning data. Here's my basic setup:OLE DB Source 1 (table1)
Sel Name Order JoinKeyno UniqueID 1 yesyes Column1 0 no
...

OLE DB Source 2 (table2)
Sel Name Order JoinKeyyes ID 1 yesyes Columns 0 no
...

There is a link (arrow) between UniqueID and ID, and the join type is "Left Outer Join". When I execute the statement "SELECT * FROM Table1 LEFT JOIN TABLE2 ON TABLE1.UniqueID = Table2.ID", the data returns correctly to me. What am I missing with the properties I've set above with the merge join?

View 6 Replies View Related

Returning Only Non Matching Left Join Records

Mar 3, 2015

How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.

I am checking for all parishes with no associated drivers. Currently I only have 2 of such.

The regular left join

select parish.name, driver.fname from parish left join driver on driver.parish=parish.name

Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.

View 1 Replies View Related

Transact SQL :: How To Join Results Of Two Queries By Matching Columns

Aug 10, 2015

I have two queries as below;

SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;

and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;

How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?

View 4 Replies View Related

Simple Group By And Count With Join Not Matching Between Sql Server And Sql CE

Apr 2, 2007

In Sql Server

Code Snippet

CREATE TABLE t_contact

(

Id uniqueidentifier,

FirstName nvarchar(50),

LastName nvarchar(50),

TaskId uniqueidentifier

)

GO

CREATE TABLE t_task

(

Id uniqueidentifier,

Start datetime

)

GO

INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');

INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')

INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')

GO

SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start

GO

Result (Expected)

2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1

Result In Sql CE (UnExpected)

2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3

Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.

Thanks Adam

View 3 Replies View Related

T-SQL (SS2K8) :: Outer Join Add Non Matching Rows To Each Order Group?

Mar 30, 2015

In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?

if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table

[Code]....

View 2 Replies View Related

Data Verification And Matching

Apr 17, 2008

i have one task in which i have to match some attributes(required for creating a new databse) with the exiting database, are these attributes present in exisisting database, if yes how many , and how many are not,pls do reply

View 3 Replies View Related

Update If Matching Data (Maybe No PK)

Feb 4, 2015

Basically without going into too much detail, our company gets databases arriving and put onto our systems which have been made my other organizations with no guarantee of what the primary key is or if this is one at all.

I should probably give my main problem in an example for clarity:

Currently I have a .csv file full of data that needs to be put into say TableA. However I do not know if TableA has a primary key or not, or if the file that needs to be inserted into TableA contains duplicate data. I have the importer sorted that does this if you ignore the problem of duplicate data, however what I would like is an MS SQL query that does the following (but I cannot figure it out):

Assuming we are reading through the file line-by-line and a check is performed each time:

1.If there is a line with a primary key in the file that matches a primary key in TableA in the database update that row in the database with the line in the file.

2. If there is no primary key on the table and there is an exact data match between the line in the file and a row in the database then update it.

3. If neither 1 or 2 are successful then just insert the data.

Obviously the potential lack of a PK here makes things a lot more convoluted.

View 1 Replies View Related

Matching Two Sets Of Data

Mar 12, 2008

Hi All,

I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.

Does anyone have any suggestions how i would go about doing this?

Thanks in advance
Humate

quote:Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.

View 2 Replies View Related

Returning Matching/Non Matching Records

Feb 4, 2007

Hi All

I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.

I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data

In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible

As always any help or direction would be appreciated.

View 5 Replies View Related

T-SQL (SS2K8) :: Matching Two Data Sets

Apr 28, 2015

I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.

How can i write a query to do this? Examples are as below.

Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000

Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900

create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)

[Code] ....

View 5 Replies View Related

Sub-report With No Matching Data Question

Aug 23, 2007

I have a report in which I have a sub-report linked to the main report by a parameter. When there is no data for the sub-report, no data displays on the main report as expected.

Now the users are asking if there is no data in the sub-report if I can instead have "None" printed on the main report. I tried adding a label to the sub-report, but since none of the data from the main report links to the sub-report, nothing is still displayed. I then put a label on the Main report and placed it behind (set to back) on the main report. This works great when there is no data, but if there is data the label still prints and the report looks awful.

I cannot think of a way to accomplish what they have asked for.

Any ideas?

Thank you,

T.J.

View 4 Replies View Related

Finding Matching Data In One Column That Does Not Match In Another

Nov 18, 2005

I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.

I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.

I have tried this, but it does not seem to want to work.

Code:

Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)

I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.

These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P

Any ideas?

View 2 Replies View Related

T-SQL (SS2K8) :: Matching Existence Of Data Between Like Columns?

Jul 16, 2015

I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.

If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact

If name, phone and email all provided, and source the same then update to new values.

If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert

If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert

If name and phone on SourceContact and name and/or Phone is blank in Contact then update

If name and email on SourceContact and name and/or email is blank in Contact then update

If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource

If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email

If Contact_fk is 0 then it is a new contact and just insert it.

IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact];
CREATE TABLE [dbo].[SourceContact]
(
[SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED,
[ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''),
[ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),

[code]....

View 2 Replies View Related

Matching Data Mining Tutorial Results

Nov 25, 2007

Hi All

Microsoft released an introductory tutorial for data mining in September 2007, and I was attempting to match the results on:
http://msdn2.microsoft.com/en-us/library/ms169911.aspx

I was not able to match the results, so then I went "under the hood" for the tables, and discovered that my copy of AdventureWorksDW has truncated values in the Education field of the ProspectiveBuyer table. I had wanted to connect "EnglishEducation" from the Mining Model to "Education" in the "Input Table" to see if the additional linkage would affect the prediction results (the wizard does not automatically link these fields because they technically do not have the same name).

1) Is everybody's "Education" field in the "Prospective Buyers" table populated with truncated values? (which may be intentional since it is a sample dataset) -- compare to "EnglishEducation" field in the vTargetMail table

2) Am I the only one getting 0.50580 for all rows when I use "Decision Tree" (per the tutorial instructions)? By contrast, I was able to see a variance in expression values for clustering and naive Bayes.

My guess: everybody has a truncated "Education" field, and there is something different in how the data mining model was run under the Decision Tree option to yield the tutorial's numerical results for "expression".

View 8 Replies View Related

Matching Relational Records. Is It Possible Using Data Minig?

Jun 20, 2006

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

View 1 Replies View Related

SQL Server 2012 :: Pattern Matching And Data Conversion

Feb 20, 2014

I have a table ("MyData") with string columns that have nvarchar data that looks like this:

ColA
--------
42/90
78/109

I plan to do a mathematical grouping on the numerator (eg: 0-10,11-20,21-30, etc... ), So I need to grab the numerator and turn it into an int. For reasons I can't get into, I have to do this in pure T-SQL.

So my question: How would I write a select statement that regex pattern matches "^([0-9]+)/" on ColA and returns integers instead of text?

E.g. THis Query:

SELECT [magic t-sql syntax] as Converted_ColA from MyData

should return this set of int values:

42
78

View 8 Replies View Related

SQL2005 Lookup Not Matching Imported SQL7 Data

Feb 19, 2008

I'm importing floor machine data from SQL7 into SQL2005 using SSIS. I import the SQL7 data into a SQL2005 master table and then attempt to match the import data with the data in a current table for either update or insert of new machines. The SQL2005 master table was imported from a SQL2000 database. When I run the first Lookup import, it does not recognize the PK matches between the SQL7 import and SQL2005 master and imports all the SQL7 as new machines. The first Lookup branches to a second lookup that checks for changes in the SQL2005 master. When I run the package a second time, the second Lookup treats all the records as updates when it gets to the second Lookup, but should treat these as found. Any suggestions as to why this process is not working properly would be appreciated. Is there a way I can embed a picture of the process from SSIS in this post? Thanks

View 3 Replies View Related

Reporting Services :: Lookup Matching Fields In Rows Of Data

Jul 16, 2015

Our division has approximately 300 employees. We have an annual shift bid where seniority is calculated using Date of Hire. If 2 or more employees share the same Date of Hire then we fall back to Date of Application. Currently the SSRS report does a very simple query and shows all the employees in order of their Date of Hire. If they match then it sorts the matching Date of Hire entries alphabetically by the employee name. It then becomes the task of the scheduler to locatethe entries with the same Date of Hire and manually look up the employees' dates of applications then sort them accordingly and re-write the report.

Goal:
Convert the manual process into an automatic process by modifying the current SSRS report.
Data:
The dataset is "DivDir" which contains the following fields: "EmpName", "DofHire", & "AppDate".

EmpName    DofHire         AppDate
Adam ......   12/2/1996      11/15/1996
Bob ..... .... 1/16/1997       12/27/1996
Charlie ....... 1/16/1997      12/12/1996
Dan ......   ... 4/11/2001     3/22/2001

In the above example I want the SSRS report to list the employees in this order: Adam, Charlie, Bob, Dan.How do I do this programmatically using SSRS?

View 7 Replies View Related

T-SQL (SS2K8) :: Unable To Extract Data Based On Matching Info From A Local Database

Oct 2, 2014

I have a MySql Database i need to extract data from based on matching info in a local SQL Server database,

I will ultimately need to cycle through 20 of these MySql databases , but the query below is taking 1 minute and 20 plus seconds...Just for one..

Is there a simple tweak that would speed it up?

USE [CAS]
GO
DECLARE @PhoneDB varchar(max),
@SQL NVARCHAR(MAX);
set @PhoneDB = '[PHONEDB_PHI]';
set @SQL = 'SELECT

[Code]....

View 4 Replies View Related

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

CREATE TABLE #Temp
(
  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,

[code]....

Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses

Thank You

View 1 Replies View Related

Fill Data Grid With Data From JOIN Query

Jul 8, 2013

I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.

So I have 2 tables for products in the system

products
+-----------+------------+
|productid |productname |
|Int |varchar(50) |
+-----------+------------+

productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int |Int |Text |Money |Int |
| |FK_From_ | | | |
| |productid_| | | |
| |products | | | |
+---------+----------+------------+------+------+

One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.

CREATE VIEW [dbo].[v_stock]
AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
FROM tab_productdetails INNER JOIN
tab_products ON tab_productdetails.productid = tab_products.productid

The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".

I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.

Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.

View 1 Replies View Related

Join Three Data Sets From Different Data Flows Into One Txt File

Mar 9, 2008

Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.
Let's explain a little more:

I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.

Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.

What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?

Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?
Thanks for your help!

View 7 Replies View Related

Exporting Data From A Merge Join From One Data Flow To Another

Mar 1, 2006

Hi,

Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!

View 8 Replies View Related

Join Into New Data

Jan 19, 2012

I need joining 2 tables and putting the result into a new table.

Table export has the following columns:
Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k

Table cc_split has the following columns:
Company, Fun_Account, CC_Account, x, y, Split

The new table called export2 has exactly the same values as export i.e. :
Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k

So what I need to join from export and split into export2 are company, cc_account (that shall go into account column of export2 together with the account of export) and the amount, which shall be ( split column * amount column of export), the rest values shall be the same.

So what I have so far is

Code:
INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT,
EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY,
COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE)
SELECT COMPANY, CC_ACCOUNT, SPLIT
FROM EXP_DATA INNER JOIN CC_SPLIT ON EXP_DATA.COMPANY=CC_SPLIT.COMPANY AND
EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT
WHERE EXP_DATA.PERIOD IN ('1102', '1103', '1104')]

So at the end basically table exp_data 2, shall have the same values as exp_data but the account column shall include the same values of account column of exp_data plus the values of cc_account of cc_split table. And also the amount column shall be the multiplication of the column split from cc_split * amount of exp_data

I have realised that I need an alias, maybe a for exp_data and b for cc_split.

View 4 Replies View Related

Inner Join Duplicate Data

Jun 13, 2000

i have 2 tables not connected in any way
but both have orderid filed (same filed).
In one table this filed (and onther one) are keys,
the second table dose not hace a key at all.
The same order_id CAN repeat itself in each table.
When i try to join the tables (some rows just in one table and some in both):
Select tab1.name, tab1.orderid, tab2.sku
from tab1 inner join tab2 on tab1.orderid=tab2.orderid
The result i get is duplicate.
each row is multiple.
What I'm doing wrong?

View 3 Replies View Related

Join On Similar Data

Feb 23, 2015

Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like

employeeID
R_12345678
S_5555555
T_777777
U_7777

Our new data structure simply removed the letter & underscore.

employeeID
12345678
5555555
777777
7777

Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?

View 9 Replies View Related

How To Join Data - Can't Figure This One Out

May 22, 2008

Hi -

I have three tables that I am trying to join in order to produce some report output. I have tried numerous ways, but they all don't give the results I need to provide.

Below is the table creation, some inserts for data and the output I am looking for.

CREATE TABLE [SalesAcct]
(
[EmpName] [varchar] (100),
[Acct_Num] [varchar](15),
[Acct_Name] [varchar] (50)

)

CREATE TABLE [Perf]
(
[Acct_Num] [varchar](15),
[PerfDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[Clicks] [int],
[Impressions] [int]
)

CREATE TABLE [Revenue]
(
[Acct_Num] [varchar](15),
[RevenueDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[ServiceLevel] [varchar],
[TransactionType] [varchar],
[GrossRev] [decimal](18, 2)
)

INSERT INTO SalesAcct (EmpName, Acct_Num, Acct_Name)
VALUES ('BobSmith', '100XYZ', 'My Account')

INSERT INTO Perf (Acct_NUm, PerfDate, Channel, Medium, Clicks, Impressions)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net', 240, 500)

INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net',500.50)
INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Cable',23.75)

What I need to provide to mgmt is two reports...one is rolled up by EmpName and AcctNum and Date, and the other is details like:

Suumary_Acct_By_Day:
EMPName | Acct_Num | Acct_Name | Date Rev $ | Clicks | Impressions
BobSmith| X0000005 | My Account | 2008-05-11 |524.25| 240500

Detail_Acct_By_Day:
EMPName | Acct_Num | Channel | Medium | Date | Rev $ | Clicks | Impressions
BobSmith | X0000005 | US | Net | 2008-05-11 | 500.50 |240 | 500
BobSmith | X0000005 | US | Cable | 2008-05-11 | 23.75 | 0 | 0

My latest query, which doesn't work is:
select S.EmpName, S.acct_num, R.RevenueDate as Date, coalesce(R.Channel, P.channel)as channel,
coalesce(R.medium, P.medium), GrossRev, coalesce(P.clicks, ''), coalesce(P.impressions, '')
FROM SalesAcct AS S
left outer join Revenue as R on S.acct_num = R.acct_num
left outer join Perf as P on S.acct_num = P.acct_num

Yields two rows...but the Medium duplicates and the Clicks and Impressions also get duplicated when they should be zero.

Any help would be appreciated.

Thank you,
Will

View 4 Replies View Related

Join Data From 2 Tables

Nov 22, 2007

I have 2 tables , one is Broker and Other is Booking, Booking table has 4 brokers codes as foreign keys with other columns, like

Booking

id Broker1 Broker2 Broker3 Broker4
1 100 101 102 101
2 101 102

brokers can bu null in booking (means no broker)

Brokers

BrokersCode BrokerName
100 John
101 muller
102 ABC

Now my requirement is to show broker Name (John, Muller, ABC) against their codes in table Booking.
i tried with left outer join, but this is not working after join in result it shows more rows in booking table as original.
(No of records in booking table should be same after join as before, the thing is only to show brokers names) .

Any help?
Thanks

View 4 Replies View Related

Sql Data Join Related Problem

Apr 1, 2008

I am using join for 2 tables in one sql data file.
now i want to build relationship between 2 tables of different SQL file is it possible?

View 5 Replies View Related

How To Join Tables And Get Desired Data...

May 16, 2005

I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.

Thanks in advance!!

View 6 Replies View Related

SQL 2012 :: CTE Ignores The Data In The Second Join?

Aug 19, 2014

I have created a common table expression. The first query is meant to select a particular months worth of data. The second query is meant to select the following months data but only for the subscribers (MSISDN's) that appeared in the first month.

The query returns result for month 1 but ignores the second month. If I remove the last inner join to the CTE (INNER JOIN Test_Table tt ON tt.MSISDN = B.SUBSCRIBER_NUMBER) then I get a full list for both months but obviously this isn't limited to those MSISDN's in the first month.

The second issue that I'm having is that I can't select a sample of say only a 1000 MSISDN's from the first month...

WITH Test_Table (Report_Month, MSISDN, SUB_SEGMENT)
AS
(
Select
R.Report_Month,
R.MSISDN,
DV.SUB_SEGMENT

[code]....

View 9 Replies View Related