SQL Help

[Surf314]

My wife is taking a database management course from a teacher that's kinda awful. If possible can some of you check some query statements and see how they are wrong?  Here is the first:

SELECT fName,lName, Person.pSSN, acctNum, empID
FROM Person, Purchase, Employment
WHERE Person.pSSN=Purchase.pSSN AND Person.pSSN=Employment.pSSN

Trying to join 3 tables: Person, Purchase and Employment

Thanks

[Fail Medic]

Oh shit yes SQL is my primary work job duty.  Lemme see.

Okay, hmm.  That style of joining is not what we do at work.  I know Oracle's flavor of SQL goes for just listing the tables in the FROM clause and then handling the "joining" in WHERE clause, but we're on Microsoft's T-SQL, and we use JOIN keywords.  So, after having said that, the query looks fine to me, I guess.  I would code it like this using JOIN keywords:

SELECT fName, lName, person.pSSN, acctNum, empID
FROM Person
LEFT JOIN Purchase purch ON purch.pSSN = per.pSSN
LEFT JOIN Employment emp ON emp.pSSN = per.pSSN

If you're getting errors about ambiguous field names, then you'd have to quantify some of those other fields.  Your example doesn't explicitly say where the fields are coming from.  I'd assume fName and lName are Person's responsibility, but what provides acctNum?  Purchase?  Yeah, if the point is to look for something "wrong", then I'd immediately call this one out on not quantifying all fields with table aliases.

[Surf314]

Oh shit yes SQL is my primary work job duty.  Lemme see.

Okay, hmm.  That style of joining is not what we do at work.  I know Oracle's flavor of SQL goes for just listing the tables in the FROM clause and then handling the "joining" in WHERE clause, but we're on Microsoft's T-SQL, and we use JOIN keywords.  So, after having said that, the query looks fine to me, I guess.  I would code it like this using JOIN keywords:

SELECT fName, lName, person.pSSN, acctNum, empID
FROM Person
LEFT JOIN Purchase purch ON purch.pSSN = per.pSSN
LEFT JOIN Employment emp ON emp.pSSN = per.pSSN

If you're getting errors about ambiguous field names, then you'd have to quantify some of those other fields.  Your example doesn't explicitly say where the fields are coming from.  I'd assume fName and lName are Person's responsibility, but what provides acctNum?  Purchase?  Yeah, if the point is to look for something "wrong", then I'd immediately call this one out on not quantifying all fields with table aliases.

You sir will probably getting some messages in the future.  And thanks.

[Master Shake]

Know any good sites for learning SQL, because I feel like it would be really hopeful to know when making websites.

[Surf314]

Know any good sites for learning SQL, because I feel like it would be really hopeful to know when making websites.

http://www.w3schools.com/sql/default.asp

That's what I was using to teach myself enough to help.

[Surf314]

SELECT DISTINCT lName+', '+fName AS Name, phone AS [Home Phone], p.ffNum as [BLTN-FF Number], flightDate AS [Date]
FROM Person AS p, Flight AS f, FrequentFlyer AS q
WHERE from='Hong Kong International' Or to='Hong Kong International' And q.ffNum=f.ffNum And p.ffNum=q.ffNum And p.pSSN=f.pSSN And p.ffNum=f.ffNum And q.pSSN=f.pSSN And p.pSSN=q.pSSN And p.ffNum Is Not Null And f.ffNum Is Not Null And q.ffNum Is Not Null
ORDER BY p.ffNum DESC;

-lName, fName, phone are in Person table
-ffNum is in Flight, FrequentFlyer, and Person tables
-flightDate is only in Flight table
-PK of Person is pSSN
-PK of Flight is FlightID
-PK of FrequentFlyer is ffNum
-Frequent Flyer is joined to Person and Flight table through ffNum
-Person and Flight are joined through pSSN

when it runs the query I get everyone that has a ffNum and their flight date to and from hong kong, but after that there is a list of the other people in the table who do not have ffnum's and they are listed with flight dates which is not possible
is there something i need to fix in the relationships? or is it just the code?

[Surf314]

NM she fixed that one.

[Fail Medic]

It didn't have anything to do with the fact that from='Hong Kong International' Or to='Hong Kong International' wasn't wrapped in parentheses, did it?  'Cause an OR with no parentheses is asking for trouble.

I have to admit, I'm having trouble understanding why so many criss-crossed joins are needed to tie all these tables together.  Why would p.ffNum need to worry about q.ffNum (And p.FFNum = q.FFNum) when f.ffNum is already hitching onto q.ffNum (And q.ffNum = f.ffNum)?  Never mind me, though, I'm just gettin' cross-eyed.  Glad she got it straightened out.

[Surf314]

Yea basically her teacher spent the entire semester teaching them how to make databases, then with no background dumped a bunch of crazy SQL queries on them for finales.  But she got it finished.  Thanks for the help.