CODEWITHSUNDEEP
sqljoin
homam
homam

Reputation: 1975

Joining a table on itself

Is there a better way to write this SQL query?

SELECT *,  (SELECT TOP 1 columnB FROM mytable WHERE mytable.columnC = T1.columnC ORDER BY columnD) as firstRecordOfColumnB
FROM
    (SELECT * FROM mytable WHERE columnA = 'apple') as T1

Notice that columnC is not the primary key.

Upvotes: 2

Views: 212

Answers (2)

Peter Örneholm
Peter Örneholm

Reputation: 2858

This might be better in case of performance:

SELECT
  *,
  (TOP 1 myLookupTable.columnB FROM mytable AS myLookupTable WHERE myLookupTable.keyColumn = mytable.keyColumn) as firstRecordOfColumnB
FROM
  mytable
WHERE
  columnA = 'apple'

But for the TOP 1 part I don't know any better solution.

Edit: If the keyColumn is unique, the data in firstRecordOfColumnB would be the same as in mytable.columnB. If it's not unique at least you need to sort that data to get a relevant TOP 1, example:

SELECT
  *,
  (TOP 1 myLookupTable.columnB FROM mytable AS myLookupTable WHERE myLookupTable.keyColumn = mytable.keyColumn
   ORDER BY myLookupTable.sortColumn) as firstRecordOfColumnB
FROM
  mytable
WHERE
  columnA = 'apple'

Upvotes: 0

Janick Bernet
Janick Bernet

Reputation: 21184

If the keyColumns is really a key column (i.e. unique), than the query can definitly be written more elegantly and efficiently...

SELECT
  *, columnB
FROM
  mytable
WHERE
  columnA = 'apple'

Upvotes: 2

Related Questions