Member Avatar for conwayce

Hi

I'm migrating from Oracle Sql to MS Sql. I'm trying to write an mssql query that will pull all rows from a tableA but only if row in tableB = apple. In other words the table might look like this:

tableA tableB
Joe apple
Joe orange
Joe peach
Fred potatoe
Fred carrot
Fred apple
Mike carrot
Mike orange

I'm looking for the following result:

tableA tableB
Joe apple
Joe orange
Joe peach
Fred potatoe
Fred carrot
Fred apple

I had no problem in Oracle but for some reason I can't get this right in mssql. Any assistance would be most appreciated.

  • 2 Contributors
  • 4 Replies
  • 131 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by tesuji
Member Avatar for tesuji

Hi conwayce

Cannot figure out the logic how result is created from the given tables.
How are these two tables related to each other? Are there any (hidden) relationships?
Best you post complete schema of both tables.

krs,
tesu

Member Avatar for conwayce

Sorry bout that. I was in a hurrry to post thread.

tableA tableB
Joe apple
Joe orange
Joe peach
Fred potatoe
Fred carrot
Fred apple
Mike carrot
Mike orange

Member Avatar for conwayce

Sorry about that. I was in a hurrry to post thread. Hope the follwoing makes more sense. Thanks for the quick response.

tableA tableB
Column1 Column1 Column2
Joe Joe apple
Joe Joe orange
Joe Joe peach
Fred Fred potatoe
Fred Fred carrot
Fred Fred apple
Mike Mike carrot
Mike Mike orange

Member Avatar for tesuji

Hi conwayce,

Sorry about that. I was in a hurrry to post thread. Hope the follwoing makes more sense. Thanks for the quick response.

tableA tableB
Column1 Column1 Column2
Joe Joe apple
Joe Joe orange
Joe Joe peach
Fred Fred potatoe
Fred Fred carrot
Fred Fred apple
Mike Mike carrot
Mike Mike orange

These SQL selects

select a.column1 from tableA a, tableB b 
  where a.column1 = b.column1 AND b.column2 = 'apple'

-- or with inner join:

select a.column1 from tableA a join tableB b  on a.column1 = b.column1
  where b.column2 = 'apple'

-- or with key join (if a.column1 and b.column1 are part of primary keys)

select a.column1 from tableA a join tableB b where b.column2 = 'apple'

should give

a.column1
-------------
Joe
Fred


krs,
tesu

p.s. There is no difference between Oracle and MS SQL Server

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.