JOIN QUERY

Question

JOIN QUERY

jayoub

SSCertifiable

Points: 6990
More actions
April 13, 2009 at 12:28 pm

#130022

I need help with a join query. I have two tables leas and suit and i need from the leas table the leas.bldgid, leas.occpname, leas.suitid, leas.phoneno1 and i need to join the suit table and get only the suit.address.
The relationship is the suitid in both tables. I have tried running the following:
SELECT LEAS.BLDGID, LEAS.SUITID, LEAS.OCCPNAME, LEAS.PHONENO1, SUIT.ADDRESS FROM LEAS, SUIT WHERE LEAS.SUITID = SUIT.SUITID AND LEAS.BLDGID = ' 630' ORDER BY SUITID
the leas table only has 321 records, but when i run this query my results are 2251. What am in doing wrong. I have tried adding the
group by LEAS.BLDGID, LEAS.SUITID, LEAS.OCCPNAME, LEAS.PHONENO1, SUIT.ADDRESS
but still no luck. Please help.
Jeff

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734666 More actions · Answer 1

First, please separate the clauses so it's easier to read. It helps us, and I bet it will help you with coding.

Second, I'd suggest that you use ANSI joins, which make it easy to determine what you are putting together. This code, should give you the matching rows in the tables (it's your code, reformatted).

SELECT LEAS.BLDGID

, LEAS.SUITID

, LEAS.OCCPNAME

, LEAS.PHONENO1

, SUIT.ADDRESS

FROM LEAS

INNER JOIN SUIT

ON LEAS.SUITID = SUIT.SUITID

WHERE LEAS.BLDGID = ' 630'

ORDER BY SUITID

If you are getting many more rows, then you have multiple rows in the other table.

If you run this:

SELECT LEAS.BLDGID

, LEAS.SUITID

, LEAS.OCCPNAME

, LEAS.PHONENO1

FROM LEAS

WHERE LEAS.BLDGID = ' 630'

how many rows do you get?

If you run this, how many rows?

SELECT SUIT.ADDRESS

FROM LEAS

INNER JOIN SUIT

ON LEAS.SUITID = SUIT.SUITID

WHERE LEAS.BLDGID = ' 630'

ORDER BY SUITID

jayoub SSCertifiable Points: 6990 More actions · Answer 2

I am getting a result of 2951 records.

The leas table has 321

THE SUIT TABLE HAS 314

Not sure why i am not getting the result i need.

Thanks for your help

Jeff

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Are your results from running Steve's code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jayoub SSCertifiable Points: 6990 More actions · Answer 4

Yes i am running steve's code and still i get 2251 records.

I examined the result and the code is picking up all possible matching suitid so I am getting results for about 9 buildings.

For example

Bldgid = 630 has suitid 0301

bldgid = 631 has suitid 0301

It picks up both like where bldgid = ' 630' means nothing.

Also i tried the link and the page did not come up.

Thanks for the help

Jeff

Jeffrey Williams SSC Guru Points: 90262 More actions · Answer 5

Yes i am running steve's code and still i get 2251 records.
I examined the result and the code is picking up all possible matching suitid so I am getting results for about 9 buildings.
For example
Bldgid = 630 has suitid 0301
bldgid = 631 has suitid 0301
It picks up both like where bldgid = ' 630' means nothing.

This explains why you are getting the extra rows. Your join criteria is not correct, causing the extra rows to be returned.

To give you an example, if we had the following:

Table LEAS:

BldgID SuitID

630 0301

631 0301

Table SUIT:

BldgID SuitID

630 0301

631 0301

Looking at the above, you would want to return 2 rows - one for each building. But, your join is only on the SuitID so you are going to return 4 rows, 2 for BldgID 630 and 2 for BldgID 631 which would look like:

Leas.BldgId, Leas.SuitId, Suit.BldgId, Suit.SuitId

630, 0301, 630, 0301

630, 0301, 631, 0301

631, 0301, 630, 0301

631, 0301, 631, 0301

To fix your problem, add the building id to the join:

SELECT LEAS.BLDGID

, LEAS.SUITID

, LEAS.OCCPNAME

, LEAS.PHONENO1

, SUIT.ADDRESS

FROM LEAS

INNER JOIN SUIT

ON LEAS.BLDGID = SUIT.BLDGID

AND LEAS.SUITID = SUIT.SUITID

WHERE LEAS.BLDGID = ' 630'

ORDER BY SUITID

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jayoub SSCertifiable Points: 6990 More actions · Answer 6

Thank You

It worked perfectly and since i have to run this for several buildings it saves me lots of time.

Thanks again

Jeff

Jeffrey Williams SSC Guru Points: 90262 More actions · Answer 7

You are welcome and thanks for the feedback.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs