SQLTeam.com | Weblogs | Forums

Full Join Tables and Where parameters


#1

im trying to join tables (using full) just to simply select queries , they do have one column in commom but cant figure how to pull data from the second table it just says NULL. so Table1 has columns ID , Name LastName Birthday, Table2 Has ID, Address, Zip code. How can i add a column from table2 to the query below so it show the address using where instead of ORDER BY

select Name, LastName, Birthday from Table1
Full Join Table2
on Table1.ID = Table2.ID
Where Name= john


#2

In case is not a typo, you should use single quotation marks ' for the value you search.

Where Name= 'john'


#3

im just trying to incorporate a column from table2 to show lets say his address too in table2


#4
select Name, LastName, Birthday, Table2.columnX
from Table1
Full Join Table2
on Table1.ID = Table2.ID
Where Name= 'john'

#5

Tried that figuring was a simple way and for some reason the Table2 data shows as NULL but when i run a normal select query on table2 it shows fine


#6
  1. It is Full Join the right join you want ? and join base on ID ?

You could use :

select Name, LastName, Birthday, Table2.columnX
from Table1
CROSS JOIN Table2
--Where Name= 'john'

#7

now with this the columnx shows but the rest repeats itself over and over over 9000 times same Name LastNam, Birthday and columnsx shows tons of results too. Just by cross joining the normal query pul the same result thousands of times


#8

using distinct with cross join pull only unique data but when i add another column from table2 then it goes all crazy


#9

Are you sure the matching column name in table2 is just "id"? Could it be something like "nameId" or "name_id"? Often an identity column, like "id", is added to every table (not a good idea, but it's still very common) but when the id from a different table is used, it is prefixed with some type of qualifier.

For example:
customers_table ( id int, ... )
orders_table ( customer_id /contains id from the customers_table/, ...)


#10

well was using id just for example, with this so far its working since both tables have a UserID

select DISTINCT Name, LastName, birthday, Table2.Address Table2.UserID, from table1, table2
where table1.UserID= Table2.UserID and addresss = '111 nw 11 st'

now is showing only the record for that address and im guessing where the userID mtaches