Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I create a join

Posted on 2007-11-24
11
Medium Priority
?
245 Views
Last Modified: 2010-04-21
I want to JOIN two tables in the same database using the BETWEEN to restrict records
0
Comment
Question by:riciit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20344618
   SELECT tbl1.*, tbl2.* FROM table_1 As tbl1
    LEFT OUTER JOIN table_2 As tbl2
    ON tbl1.id = tbl2.id
    WHERE tbl1.datetime BETWEEN blah And blah
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20344635
hmmm - you can use BETWEEN to form a join between two tables but it will give you a CROSS type join, so will actuall increase the number of records returned, not reduce it.  I'm thinking that this is a pretty sophisticated join.  I might look something like this:
select *
FROM Table1 a
JOIN Table2 b
ON a.field1 between b.field1 +2 and b.field1 +10

For every one row in table a this would return 8 rows (assuming that field1was a sequential integer 1,2,3,4....etc).

However, maybe what you mean is a simple WHERE statement??
select *
FROM Table1 a
JOIN Table2 b
ON a.field1 = b.field1
where a.field1 between 120 and 150

which would return rows only where field1 was between the values of 120 and 150 (and assuming a one -to-one match betweem field1 in both tables, would only return one row for each row in Table1.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20344647
<< However, maybe what you mean is a simple WHERE statement?? >>
My code is based on making this assumption.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:riciit
ID: 20344663
Yes I believe the simple WHERE statement is what I am looking for. The task is actually to create the join
Here is the query I entered and the results
SELECT employee.name, job.jobcode
FROM employee
JOIN job
ON employee.name = job.jobcode
WHERE job.jobcode BETWEEN 004 and 008

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Johnny Boy                               ' to data type int.
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20344668
Yes Angel, I'm sure you are right.  

FYI: Between is INCLUSIVE (equivalent to less than/greater than OR  EQUAL TO) so just bear that in mind.  Otherwise it is a pretty useful command.
0
 
LVL 6

Accepted Solution

by:
PaultheBroker earned 1000 total points
ID: 20344672
yeah - you can't joint employee.name to jobcode, as one of them is a varchar field and one of them is an integer !!!! doesn't make sense.

you probably want to join on employee.job = job.jobcode.

Also, you will find it less typing if you get into the habit of aliasing all your tables - see the example below




SELECT e.name as [Employee Name], j.jobcode as [Job Code]
FROM employee e
JOIN job j
ON e.jobcode = j.jobcode
WHERE j.jobcode BETWEEN 004 and 008 

Open in new window

0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20344679
Have you considered doing a rtrim() on the name field as well, as it looks like there are trailing spaces in the data???  just a thought....

SELECT rtrim(e.name) as [Employee Name], ... etc
0
 
LVL 1

Author Comment

by:riciit
ID: 20344881
I am sorry but my experience here is limited as I am just a rookie(at best), is all new to me. So let me see if I have this right now. The reason I am having trouble here is that I am trying to join two different types of data a varchar and an integer... and that won't happen. Makes sense. I have looked at the tables and do not see data types that are compatable. Unless maybe employeeID and JobCode, JobCode is an integer and EmployeeID is Char but is numeric. Or should I recreate the tables to have some sort of data types which will join.
0
 
LVL 1

Author Closing Comment

by:riciit
ID: 31410821
Thank you for the hint on the aliasing it certainly did lessen the typing. The syntax for the query you have provided seems to have the resultant data set I was looking for. This answer gave more than just the solution, thank you for the lesson on aliasing too.
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20345147
If there isn't a jobcode field on the employee table (or the jobs table has an employeeID field)   then you can't join employees with jobs - its that simple!!   If you want to post the schema of both tables, it would enable more help to be forthcoming....(unless you've solved it already of course!)
0
 
LVL 1

Author Comment

by:riciit
ID: 20357390
CREATE TABLE job (
      jobcode                  INTEGER NOT NULL,
      title                  CHARACTER (41),
      exempt_status       BIT  NOT NULL,
      min_salary          DECIMAL (9,2),
      max_salary             DECIMAL (9,2),
      CONSTRAINT job_pk PRIMARY KEY  (jobcode)
      )


CREATE TABLE employee (
      ssn      CHARACTER (11) NOT NULL,
      name      CHARACTER (41),
      address       CHARACTER(80) NOT NULL,
      phone          CHARACTER(10),
      email      CHARACTER(40),
      jobcode       INTEGER NOT NULL,
      hire_date      DATETIME,
      salary      DECIMAL (9,2),
      CONSTRAINT employee_pk PRIMARY KEY (ssn),
      CONSTRAINT employee_to_job_fk FOREIGN KEY (jobcode) REFERENCES job (jobcode)
)

I know the answer has been accepted thought I might add this just to see what it brings for help in understanding the whole picture.
0

Featured Post

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question