Another Noob question

I am using SQL server 2000. I am trying to create the simplist of queries in my mind and I keep getting duplications and its driving me nuts. I have two tables. (MASTER, SLAVE we will call them) All I am trying to do is "show me data within this date range" and it gives me results but duplicates. Help. PLEASE do not refer me to http://www.dbforums.com/t1201578.html because that is unclear to me! Here is my code:

SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER, SLAVE
WHERE TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<='20061230')
ORDER BY SERVICE DATE

I am a noob, but I can't see this being complex.SELECT DISTINCT ...

might solve your problem.

Oh, yes ... this query has two tables. Where did you join them? If you do not do that, you'll get cartesian product of all values. Therefore, joining tables properly might be another possible solution. Such as

...
WHERE slave.some_column = master.some_column
AND ...|||Well, I tried the distinct and it made my outcome worse. I do not have a join to the tables because I am not clear how to do it yet.|||Well, you'd better figure that out :)

I have no idea how your tables look like (it is unclear which table contains which column you provided in the sample query), but seeing "DOB" (Date Of Birth?), you might have something like PERSON_ID in both tables which *might* be the column you are looking for.|||Agree with Littlefoot. You must join the tables by creating a relationship between the two. In order to do this, each of the tables must contain a common KEY upon which the tables are to be linked.

To create a relationship between the tables, click on the relationship icon on the toolbar, follow the steps that ultimately will permit you to make a connection between the common KEY, a field in the database, usually holding unique data for each record, eg. SS#.|||The join syntax is pretty straightforward
lets assume that TECH is the common column

SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER
join SLAVE on Slave.TECH=master.tech
WHERE MASTER.TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<=#20061230#)
ORDER BY SERVICE DATE

incidentally I think you will have a problem with spaces in column names
I'd suggest developing or using a naming convention.
Personally I've always used something that distinguishs tables form columnsm used captialisation to make things easier to read, but there as many naming conventions as politicians want to grab all the cash in your pocket. Find one htat works for you, or your organistion and stick with it.

Select PLName, PFName, PMName, DoB, Acct, SrvDate
FROM DTMaster
join DTSlave on Slave.Tech=DTMaster.Tech
WHERE DTMaster.Tech='TECHNAME' AND(SERVICE DATE>='20060101' AND SrvDate<='20061230')
ORDER BY SrvDate

HTH|||I am using SQL server 2000. I am trying to create the simplist of queries in my mind and I keep getting duplications and its driving me nuts. I have two tables. (MASTER, SLAVE we will call them) All I am trying to do is "show me data within this date range" and it gives me results but duplicates. Help. PLEASE do not refer me to http://www.dbforums.com/t1201578.html because that is unclear to me! Here is my code:

SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER, SLAVE
WHERE TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<='20061230')
ORDER BY SERVICE DATE

I am a noob, but I can't see this being complex.

What the others said, basically!

What columns correspond to what tables? Where is Tech? Is it on Master or Slave? The others are right; this syntax looks like it'll get you a cartesian product which isn't what you want. You want an inner join!

Also, if you're looking for a date range, maybe try using BETWEEN instead of 'SERVICE DATE>='20060101' AND SERVICE DATE<='20061230''

Annoying Inner Join Problem

Greetings SSIS friends,

I have the following problem in SSIS and it's driving me nuts!!!!!

My situation is as follows :

Data Source 1 & Data Source 2are joined using a merge join. This bit works fine.

The output of the above join is then joined to a third data source but this time, I only get 63 rows coming through instead of 77097 even though the join key in the second merge join component is the same as the first one!!!

I thought I was going mad so I decided to see if the same problem occurs if I was to do this with T-SQL. I created 3 temporary tables for each of my data sources.

I did an inner join between tmpTable_Source1 and tmpTable_Source2, I then stored the result in tempTable4

The final inner join was performed between tempTable4 and tempTable3 and the result produced 77097 and not 63 rows.

What the hell is SSIS playing at?! The merge keys I used in T-SQL is the same one I used in my SSIS package!!!!

Are all the inputs of your merge joins actually sorted? The inputs require to be sorted; otherwise you will get unexpected results.|||

Hi Rafael,

Yes my inputs are sorted but anyway, I decided to do away with one of my data sources and combined it as part of the SQL command in my second data source.

Now I have another problem!!!

In my UNION ALL component I get an error for one of my Input fields. It says that the metadata for my input column does not match with the metadata for the associated output column. The trouble is, this was working just fine before and I haven't touched it!!!!

What would cause this kind of problem?!!

Your help would be much appreciated.

|||

Sorry Rafael,

I've solved this little problem. Looks like the data type for one of my input columns was a unicode string.

I will look in to your idea with the other saved package that I've got. Still unsure aboutt the ordering business but will try it and let you know.

Thanks for your help.

|||

Union all transforms are not very friendly when refreshing metadata. I have found faster to delete the faulty column mapping inside of the lookup and added it back. To delete it select the whole row representing the column with the problem and select delete from the right click menu. Then add it back using the drop down lists. Alternative, you could delete the whole Union all and place it back.