Combinations

Would it be possible to generate such a query I demonstrate below?

Let's supposing I had this Table (below) and I wanted to pare-up all combinations and output as 1 whole Table.

A1, B1
A2, B2
A3, B3
A4, B4

So for example, I pare-up the first item A1 with B1 then the first item A1 (again) with B2 ..and so on to end up with (below).

A1, B1
A1, B2
A1, B3
A1, B4

A2, B1
A2, B2
A2, B3
A2, B4

A3, B1
A3, B2
A3, B3
A3, B4

A4, B1
A4, B2
A4, B3
A4, B4

Of course I my also want to set conditions as to how these pare-up, as in I my want to avoid certain parings i.e. not have A1 go with B3 for example.

If you have a table called "self" and you want to join every row to every row, you would write something like

select * from self as papa join self as child

The worlds "papa" and "child" are arbitary - they're aliases which you then use to choose which ofthe combined records you need - for example:

select * from self as papa join self as child on papa.parent = child.sid

Okay, I think I get the idea - thanks

Hello again I thought I'd solved it but haven't I'm pretty new to SQL so please be bare with me - do you have an further example I can look at? Thanks

There's a complete worked example here.

Basically, if you tell MySQL to join a table to itself it gets confused when you give it where or on clasues; those clauses are ambiguous. So you give each of the incoming table copies its own name - an alias - and you can then identify which colum on the left hand copy is to be linked to which column on the right hand copy by referring to those copies via its alias.

I know this sound a bit "circular" but then that's what you get if you're joining a table to itself. The first time I came across anyone doing this I thought "how daft" to myself, but then there ARE times that you want to do it - a table of people where you're holding information about who is the father of who is a good example.

Thanks again for your time, that's certainly given me more to think about