February 15, 2017 at 2:29 pm
I have 2 tables:
TABLE A
column one column two
A 1
B 2
C 3
TABLE C
column one column two
A 1
Z
C 3
So I need to join them by column one, but the where should be any item is not listed on the following match ( A.item-B.Item) I should be able show the value of the column two, example: Z=Null
How can I do that with a join? or with an union?
Thank you!!
February 15, 2017 at 2:42 pm
montserrat.deza - Wednesday, February 15, 2017 2:29 PMI have 2 tables:
TABLE A
column one column two
A 1
B 2
C 3
TABLE C
column one column two
A 1
Z
C 3
So I need to join them by column one, but the where should be any item is not listed on the following match ( A.item-B.Item) I should be able show the value of the column two, example: Z=NullHow can I do that with a join? or with an union?
Thank you!!
How would you imagine constructing such a query with a UNION ? Do you know what UNION does? It brings multiple result sets together, not necessarily multiple tables. You don't necessarily need a join, but that doesn't mean you couldn't use one. However, are these tables really as simple as two columns each, or is this a generalized version of what you want to do? It also sounds like homework... So...
If you want help, you're going to have to put some level of effort into working this out for yourself. First, can I assume that I understand you correctly in that you only want to see records from Table A that do not have a matching value in Column One from Table C ? What might that question suggest in the way of a JOIN ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 3:32 pm
Well yes this is a generalized version of what you want to do. What I was doing with the join was something like this:
Select column one, column two from table A
JOIN tableB on tableA.columnone = tableB.columnone
Where
not (tableA.columnone = tableB.columnone)
and is not working because the where and the join are a contradiction, so how can I do this? Thank you for your help!
February 15, 2017 at 3:35 pm
Is this something like what you're looking for?
Select column one, column two from tableA
Where
tableA.columnone NOT IN (SELECT column_one FROM tableB)
February 16, 2017 at 1:49 pm
Thank you so much!! yes it works!!!
February 16, 2017 at 2:07 pm
ZZartin - Wednesday, February 15, 2017 3:35 PMIs this something like what you're looking for?
Select column one, column two from tableAWhere
tableA.columnone NOT IN (SELECT column_one FROM tableB)
This will only give you records from TableA that don't have a match in TableB, but not the ones in TableB that don't have a match in TableA. Another option is below.
SELECT *
FROM TableA a
FULL OUTER JOIN TableB b
ON a.column_one = b.column_one
WHERE a.column_one IS NULL
OR b.column_one IS NULL
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 17, 2017 at 8:58 am
Thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply