Right Join with WHERE clause query

1

Dear all,

I am working with 2 tables at the moment, the first is called GAZUPRNCOORDS and the second CCADDRESS

I am attempting to right join a column from a second table following selection from the first table.

The following query runs fine and returns results on the first table GAZUPRNCOORDS:

SELECT DISTINCT UPRN, X_COORDINATE, Y_COORDINATE FROM GAZUPRNCOORDS where X_COORDINATE > 400 and X_COORDINATE < 400 and Y_COORDINATE > 200 and Y_COORDINATE < 200

What I want to do is join a column from CCADDRESS called addr3 based on matching UPRN numbers. The column containing UPRN numbers in CCADDRESS table however is named REALUPRN.

What I have so far is as below but Iā€™m not having much luck amending it to work with a right join, can anyone advise where I am going wrong here?:

SELECT DISTINCT UPRN, X_COORDINATE, Y_COORDINATE FROM GAZUPRNCOORDS where X_COORDINATE > 400 and X_COORDINATE < 400 and Y_COORDINATE > 200 and Y_COORDINATE < 200 FROM GAZUPRNCOORDS RIGHT JOIN CCAddress ON GAZUPRNCOORDS.UPRN = CCAddress.addr3

2

The syntax is incorrect. Perhaps something like shown below is what you want?

SELECT DISTINCT
        g.UPRN ,
        g.X_COORDINATE ,
        g.Y_COORDINATE,
        c.SomeColumn,
        c.AnotherColumn
FROM    GAZUPRNCOORDS AS g
		RIGHT JOIN CCAddress AS c ON
			g.UPRN = c.addr3
			AND g.X_COORDINATE > 400
			AND g.X_COORDINATE < 400
			AND g.Y_COORDINATE > 200
			AND g.Y_COORDINATE < 200
1 Like
3

Hi James, yes I think this is much more on track. Thank you, I'll see what I can do from this :smile:

4

You are welcome.

Looking at the query though, it looks like your coordinate conditions may not be correct. It has sections like

...
AND X_COORDINATE > 400
AND X_COORDINATE < 400
...

X_COORDINATE cannot be both less than 400 and greater than 400, so you would not get any rows in your result set.

5

Hi James,

you're correct, I just used the 400 value as an example :slight_smile: