Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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 ...

  3. #3
    Join Date
    Jan 2006
    Posts
    2

    Hmmmmm

    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  5. #5
    Join Date
    Dec 2005
    Posts
    3

    Duplicate Values using SQL 2000 Server

    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#.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  7. #7
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by noble24
    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''

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •