I need to write a query using a join

Question

Post reply

I need to write a query using a join

ripper_ol_boy

SSC Enthusiast

Points: 144
More actions
February 6, 2014 at 10:54 pm

#100546

I need to write a query using a join- but can't figure out how to do it where I don't exclude "Frank Wright" - because he has two records - so the Lname is being counted as a duplicate and excluding both (and I want to include both records) while at the same time I need to correctly exclude both records 4 and 6 (Ed Jones and Mary Jones - both records of "Guilty" - since they share the same Lname)
SO the full query (using a JOIN) - should output 2 records - both Frank Wright (records 2 and 3) - since he is the same person - so shouldn't be excluded.
The sample table and the assignment (Problem:) is below. Any help is appreciated - Thanks
========================================================
Table: TEST
DriverIDFnameLnameDOB Disposition
1 JohnDoe 19651025 Not Guilty
2 FrankWright19770115 Guilty
2 FrankWright19770115 Guilty
3 Ed Jones 19810604 Guilty
4 MaryJones 19800730 Not Guilty
4 MaryJones 19800730 Guilty
5 LarryAble 19771201 Not Guilty
Problem:
Based on the sample table data above write a query using a JOIN that will list the names of people that ONLY have a Guilty disposition and do NOT share a Last Name with anyone else.
(note: the sample data is just for reference. you cannot use a persons name explicitly in the query, i.e. and Fname = ‘name’)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLCJ SSCommitted Points: 1781 More actions · Answer 1

I did not use join but was able to fetch the required output with following query...

CREATE TABLE #TEMP

(

DriverID INT, Fname VARCHAR(100),Lname VARCHAR(100),DOB VARCHAR(100), Disposition VARCHAR(100)

)

INSERT INTO #TEMP

SELECT 1, 'John','Doe', '19651025', 'Not Guilty' UNION ALL

SELECT 2, 'Frank','Wright','19770115', 'Guilty' UNION ALL

SELECT 3, 'Ed' ,'Jones' ,'19810604', 'Guilty' UNION ALL

SELECT 4, 'Mary','Jones' ,'19800730' ,'Not Guilty' UNION ALL

SELECT 4, 'Mary','Jones' ,'19800730', 'Guilty' UNION ALL

SELECT 5, 'Larry','Able' ,'19771201', 'Not Guilty'

SELECT T1.* FROM #TEMP T1 WHERE Disposition = 'Guilty'

AND EXISTS ( SELECT 1 FROM #TEMP T2 WHERE T2.FNAME = T1.FNAME AND T2.LNAME = T1.LNAME

AND T2.Disposition = 'Guilty'

GROUP BY T2.FNAME,T2.LNAME HAVING COUNT(1) > 1)

DROP TABLE #TEMP

ripper_ol_boy SSC Enthusiast Points: 144 More actions · Answer 2

Thanks for the quick response. I have something "similar" but I couldn't figure out how to take the multiple query format and do it as a JOIN instead- and that's where I was stumped, but maybe I can take what you have and modify it and combine with a JOIN somehow.

Thanks!

SQLCJ SSCommitted Points: 1781 More actions · Answer 3

I wasn't satisfied with the code written below .. but it works :crazy:

SELECT T1.* FROM #TEMP T1 INNER JOIN (

SELECT DriverID,Fname,Lname FROM #TEMP WHERE Disposition = 'Guilty'

GROUP BY DriverID,Fname,Lname HAVING COUNT(1) > 1 ) TST

ON T1.DriverID = TST.DriverID

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT t1.* --, t2.*

FROM #TEMP t1

LEFT JOIN #TEMP t2 ON t2.LNAME = t1.LNAME AND t2.DriverID <> t1.DriverID

WHERE t1.Disposition = 'Guilty'

AND t2.DriverID IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ripper_ol_boy SSC Enthusiast Points: 144 More actions · Answer 5

Hey Chris,

Could you explain the Select t1.*-- (I'm assuming it's some form of wildcard), but I've never used that before, and the only source I have for testing is VFP 6.0 - and it's not liking the two dashes (--).

Also if you know - is there a site online that I can test sql code - where I can input table data values? I've only found those Winstar pre-loaded tables, and I'm not able to test on those.

Thanks!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

How are you testing on VFP 6.0? SQL syntax is different over there.

* is to return all columns

-- is a line-comment (everything to te right becomes a comment).

To test, you could download and install SQL Server Express Edition with Advanced Services. Unless you don't have permissions on the computer

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

ripper_ol_boy (2/7/2014)
Hey Chris,
Could you explain the Select t1.*-- (I'm assuming it's some form of wildcard), but I've never used that before, and the only source I have for testing is VFP 6.0 - and it's not liking the two dashes (--).
Also if you know - is there a site online that I can test sql code - where I can input table data values? I've only found those Winstar pre-loaded tables, and I'm not able to test on those.
Thanks!

The * is a wildcard and means "all columns". The two dashes indicate that what follows, to the end of the line, is a comment.

Grab yourself a dev version of SQL Server. In the UK, contact Grey Matter by phone or email. You get 4 licences for I think 40 quid.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

I couldn't remember the name of the site, but I found it (by searching "test sql online").

http://sqlfiddle.com/ will allow you to test SQL Server, Oracle, MySQL, PostgreSQL and SQLite.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ripper_ol_boy SSC Enthusiast Points: 144 More actions · Answer 9

Thanks All!! I appreciate the help, and quick reply - they both worked, so I am set. I knew about the * wildcard - but forgot that the "--" was to comment out what follows (was thinking it was some new wildcard option).

Also - thanks for that link - it's awesome, I'm able to test now. Yeah, for VPF 6.0 I have to tweak the code a bit - but for the most part it works for testing most of my code - but some of the tasks I need to complete can't be done using it - so the link to that other site is extremely helpful.