Special Join for MySQL (RESOLVED)

[mccareth]

Hi everyone,

Ok, I'm going to try and explain this as best as possible and show lots of examples to try and be concise. I have two tables, that looks like this:

tblSection

fldSection     fldName
--------------------------
1                 General
2                 Romance
3                 Sci-Fi
4                 Fantasy

tblCredit

fldClient     fldSection     fldCredit
-----------------------------------------
1                2                  20
1                4                  24

What I would like to do is connect these two tables, show all sections, and base it off of the Client number. The output I'm looking for would be this:

fldSection     fldName     fldCredit
-----------------------------------------
1                  General        20
2                  Romance      24
3                  Sci-Fi
4                  Fantasy

This information would be based off of the client id which is 1 in the tblCredit table.

I've tried several different variations of joins (left, right, outer, etc), but to no avail. If anyone could help me, I'd appreciate it greatly. Thank you!!!

[dj4uk]

SELECT c.fldSection, s.fldName, c.fldCredit FROM tblCredit AS c LEFT OUTER JOIN tblSection AS s ON c.fldSection = s.fldSection WHERE c.fldClient = 1 ORDER BY c.fldSection

[mccareth]

Unfortunately, that only does the following...

fldSection     fldName     fldCredit
-----------------------------------------------------
1                  General            20
2                  Romance          24

It doesn't show the other sections of Sci-Fi and Fantasy.

[dj4uk]

Try FULL OUTER JOIN

[mccareth]

MySQL doesn't seem to like FULL for some reason.

[dj4uk]

Try RIGHT OUTER JOIN but I'm sure LEFT OUTER JOIN is the one that does that for SQL Server - might be worth investigating the MySQL manual to check it isn't implimented differently.

[mccareth]

Right Outer Join does the same thing, and unfortunately MySQL is not very helpful with it's instructions in it's manual. Here is what they have on joinshttp://www.mysql.com/doc/en/JOIN.html

[dj4uk]

The OUTER JOIN should return all records. If one of the records from one of the linked tables doesn't exist then null values should be put into the returned set. The LEFT, RIGHT and FULL just states which tables are allowed to return null values. FULL should cover every case. Dunno if its worth just trying a OUTER JOIN?

[mccareth]

I FINALLY figured it out... It's a little wonky, but it does work..

SELECT tblSection.fldSection, tblSection.fldName, tblCredit.fldCredit FROM tblSection LEFT JOIN tblCredit ON (tblSection.fldSection=tblCredit.fldSection AND tblCredit.fldClient=1)

This works. and quite nicely. I found an odd little blurb about a similar problem for someone else and tried it out.

[mccareth]

By the way, dj4uk, thank you for all your help. It was very much appreciated.