How Can i select a field and check it with itself?

Question

i want to say

select col1,col2,col3 
from table1 
   inner join table2 on table1.col1=table2.col1 
                        and ..... ( ?  )

? : i want just 1 record or first record from table1 joined with first record from table2. but the command cause all record joined that can be join. for example if 2 records are in the table1 that col1=1432 and just 1 record in table2 exists that col1=1432 command joined all. but i want to join just first from table1 with first from table2

i want to display all record that are more than 1 record to be join.

I think you need to `group by` on `col1` then think what exactly you want to show in `col2` and `col3`. If you need to show first record for each group you might want to look on this question: http://stackoverflow.com/questions/3800551/sql-select-first-row-in-each-group-by-group — default locale, Apr 10 '13 at 11:45
its no different when i use group by. because its joined with inner join . — Ehsan Pakravan, Apr 10 '13 at 12:02
i have two table:: table1:col1,col2,col3:key,col4,col5 ; table2: col1,col2,col3,col4:key,col5,col6,col7 ; col1 from table1 and cole1 from table2 are for join. in col1 of table1 data may be Duplicate and in col1 of table2 too. in table1 col1,col2 must be same table2 col1,col2; but may be two records of each of table same data! so i want to in select each record from table1 just with ONE record of table2. AND Other record can be Specified from each table — Ehsan Pakravan, Apr 10 '13 at 19:51

score 0 · Answer 1 · answered Apr 10 '13 at 11:43

0

i want just 1 record or first record from table1 joined with first record from table2

Here you go:

select top 1 col1,col2,col3 
from table1 
   inner join table2 on table1.col1=table2.col1 
                        and .....

answered Apr 10 '13 at 11:43

Mayukh Roy

1,815
3
19
31

i want to select all record of my table. this is just for 1 record! – Ehsan Pakravan Apr 10 '13 at 11:49
can you give the structure of your table? may be add a SQL Fiddle, and rephrase your question please? I dont think i got it totally right! – Mayukh Roy Apr 10 '13 at 12:00

score 0 · Answer 2 · answered Apr 10 '13 at 12:00

0

Try this query -

SELECT *
FROM dbo.table1 t1 
JOIN (
SELECT TOP 1 *
FROM dbo.table2 
) t2 ON t1.col1 = t2.col1

answered Apr 10 '13 at 12:00

Devart

119,203
23
166
186

Tim Lehner · Answer 3 · 2013-04-10T12:15:46.893

0

If you're on SQL Server 2005 or above, you can try a ranking function to grab only the first record of each group:

select *
from (
        select a.col1
            , a.col2
            , a.col3
            -- Use the order by to determine which rows will be ranked first for each group
            , row_number() over (partition by a.col1 order by a.col2) as rownum
        from table1 as a
            join table2 as b on a.col1 = b.col1
    ) as q
where rownum = 1 -- Only get the first row of each group

i want to display all record that are more than 1 record to be join.

You can add this to your where clause:

    and exists (
        select col1
        from table1
        where col1 = q.col1
        group by col1
        having count(*) > 1         
    )

To offer another solution that doesn't use a ranking function, I think we would need to know more about your tables, especially unique keys and how you define the first record of each group.

edited Apr 10 '13 at 12:15

answered Apr 10 '13 at 12:05

Tim Lehner

14,813
4
59
76

Okay, please provide more info on your tables such as keys and how to determine the "first" row of each group. – Tim Lehner Apr 10 '13 at 13:01
i have two table:: table1:col1,col2,col3:key,col4,col5 ; table2: col1,col2,col3,col4:key,col5,col6,col7 ; col1 from table1 and cole1 from table2 are for join. in col1 of table1 data may be Duplicate and in col1 of table2 too. in table1 col1,col2 must be same table2 col1,col2; but may be two records of each of table same data! so i want to in select each record from table1 just with ONE record of table2. AND Other record can be Specified from each table – Ehsan Pakravan Apr 10 '13 at 19:15

How Can i select a field and check it with itself?

3 Answers3