joint select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

joint select

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-11 : 11:01:35

How can i list the data if tbl2 does not have tel_no

I want to list all the a.fname like 'Abc%' or a.lname like 'xyz%' if there is no b.tel_no

SELECT A.id,A.fname,a.lname,B.tel_no
FROM tbl1 A
JOIN tbl2 A ON A.id = B.id WHERE A.fname like 'Abc%' OR A.lname like 'Xyz%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 11:04:34

[code]SELECT a.id,
a.fname,
a.lname,
b.tel_no
FROM tbl1 AS a
LEFT JOIN tbl2 AS b ON b.id = a.id
WHERE a.fname LIKE 'Abc%'
OR a.lname LIKE 'Xyz%'[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 11:11:12

[code]SELECT A.id,A.fname,a.lname
FROM tbl1 A
WHERE NOT EXISTS (SELECT 1 FROM tbl2 WHERE id = A.id)
AND (A.fname like 'Abc%' OR A.lname like 'Xyz%')
[/code]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-11 : 11:27:01

Thanks, Peso and visakh16

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-13 : 15:31:08

Hi guys,
I have 10 mileans data. I have to list break wise. I used following script but i can see the data on result a.id >50000

How do i list break wise?

SELECT a.id,
a.fname,
a.lname,
b.tel_no
FROM tbl1 AS a
LEFT JOIN tbl2 AS b ON b.id = a.id
WHERE a.id <=50000 AND a.fname LIKE 'Abc%'
OR a.lname LIKE 'Xyz%'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 15:40:57

What is "break wise"?

Show us sample data to illustrate your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-13 : 15:48:30

Like first time i want to list only a.ID <=50000, i jused the left join so i can disply over than 50000 (a.id)

I want to change script between a.id >50000 and a.id<100000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 15:51:17

So did you try adding that to your query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 16:05:25

He didn't pay attention to operator precedense.

WHERE a.id <=50000
AND (a.fname LIKE 'Abc%' OR a.lname LIKE 'Xyz%')

E 12°55'05.63"
N 56°04'39.26"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-13 : 16:06:37

yes i did.
My problem is: a.fname Like 'Abc%' OR a.fname LIKE 'Xyz%'
Look at the following script. I do not want to list a.id >50000 here

SELECT a.id,
a.fname,
a.lname,
b.tel_no
FROM tbl1 AS a
LEFT JOIN tbl2 AS b ON b.id = a.id
WHERE a.id <=50000 AND a.fname LIKE 'Abc%'
OR a.lname LIKE 'Xyz%'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 16:28:10

Your issue is not clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 16:29:35

quote:

Originally posted by visakh16

SELECT A.id,A.fname,a.lname
FROM tbl1 A 
WHERE NOT EXISTS (SELECT 1 FROM tbl2 WHERE id = A.id) 
AND (A.fname like 'Abc%' OR A.lname like 'Xyz%')

For performance reasons, use SELECT * instead of SELECT 1 or any other variation. It's a very tiny performance difference, but it's there nonetheless. This is true for EXISTS and NOT EXISTS. It's the only instances that I am aware where SELECT * should be used.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-13 : 16:46:15

I need tel_no also which is tbl2 and display a.id <= 50000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 17:06:06

Your issue is still not clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-13 : 17:30:05

Okay i am going to use this script.

SELECT A.id,A.fname,a.lname
FROM tbl1 A
WHERE NOT EXISTS (SELECT tel_no FROM tbl2 WHERE id = A.id)
AND A.ID Between 1 AND 50000 AND (A.fname like 'Abc%' OR A.lname like 'Xyz%')

My requirement is: i need to list all thoes records where id between 1 and 50000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 17:57:58

And what happens when you run that script? Does it error? Does it not produce the correct results? I wasn't born with the ability to read your mind, so I'm unable to help you until you explain yourself very clearly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 01:04:18

quote:
Originally posted by rudba

Okay i am going to use this script.

SELECT A.id,A.fname,a.lname
FROM tbl1 A
WHERE NOT EXISTS (SELECT tel_no FROM tbl2 WHERE id = A.id)
AND A.ID Between 1 AND 50000 AND (A.fname like 'Abc%' OR A.lname like 'Xyz%')

My requirement is: i need to list all thoes records where id between 1 and 50000

may be this


SELECT A.id,A.fname,a.lname
FROM tbl1 A 
WHERE NOT EXISTS (SELECT tel_no FROM tbl2 WHERE id = A.id) 
AND (A.fname like 'Abc%' OR A.lname like 'Xyz%')
OR A.ID Between 1 AND 50000

if this still doesnt work,provide more info in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources