Left join

[RFH Reseller: creativeworks]

Here's a fun one...

Lets sat you have two tables, one is relational.

The relational table might have more than one row for it's parent table.

You need to create a left join that will only return one of the relational rows for each record in the parent table...

[Staff: Warren Ashcroft]

Use a RIGHT JOIN?

[RFH Reseller: askjim]

I do not think a right join will suffice.

An example always helps

[Company] (ID INT, Name VARCHAR(50)
[Staff] (INT INT, CompanyID INT, VARCHAR(50)

Data
[Company]
1,RED
2,Blue
3,Green

[Staff]
1,1,Red1
2,1,Red2
3,2,Blue1
4,2,Blue2

Then run the following queries

SELECT*FROM
Company c
LEFTJOIN Staff s ON c.ID = s.CompanyID

SELECT*FROM
Company c
RIGHTJOIN Staff s ON c.ID = s.CompanyID

You will notice the first one returns all companies even if there are no staff whereas the second one does not return the company where there are no staff.

If you want only one row per company then you will need to write the query so it contains a sub query to select only one row from the staff table.
SELECT c.ID,c.Name, (SELECT TOP 1 s.Name FROM Staff s WHERE s.CompanyID = c.ID)
FROM Company c

Does this help?

[RFH Reseller: creativeworks]

Thanks for giving this the time Jim, I gave it a whiz through and it works a treat.

[RFH Reseller: nick]

Making use of Jim's example, would something like this not work?:

SELECT
	Company.Name,
	Staff.Name
FROM Company RIGHT JOIN Staff ON Company.ID = Staff.CompanyID
GROUP BY Company.Name

To return a single record for each company that has at least one member of staff, along with the first member of staff's name.

or

SELECT
	Company.Name,
	Staff.Name
FROM Company LEFT JOIN Staff ON Company.ID = Staff.CompanyID
GROUP BY Company.Name

If you want company records returned when there are no staff.

Sort of thing.

[RFH Reseller: askjim]

Sorry neither of the above queries with group by clauses will work as you can not include columns that do not appear within the group by clause

[RFH Reseller: nick]

Can't quite work out what you mean--though it could be, I'm not on the ball today; I've just this minute made a coffee using cold water.

Anyway, those queries do work for me, using MySql (so assuming MSSQL would behave the same).

Ta

[RFH Reseller: askjim]

I have never worked with mysql until a few mins ago just sql server and oracle.

Basically a quick search on google regarding select and group statements:

All non-aggregate columns selected must be listed in the GROUP BY clause.

The query you posted had a non-aggregate column (e.g. not sum avg max) and was not listed in the group by statement and therefore should not work. The reason all comes down to boring set logic.

The following article clearly explains why it is not possible

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

I tend to write ANSI standard SQL so was intrigued that you mentioned it worked in mysql. So I quickly knocked up a database using redfox and it works in mysql.

The following article details how it is not standard SQL and proprietary to mysql

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

I try to avoid using proprietary functionality specific to a database engine as it makes my solution less engine independent.

Hope this explains what I was trying to convey :-)

[RFH Reseller: nick]

Thanks for clearing that up, and I totally agree, it's much better to avoid non-standard methods.