A simplified version of my table layout is as follows:

table item

``+----+-------+-----+| ID | sdesc | ... |+----+-------+-----+| 1 | item1 | ... |+----+-------+-----+``

table itemaffectTable (call these qProps for properties with quantities)

``+--------+---------+----------+| itemID | affectID| quantity |+--------+---------+----------+| 1 | 2 | 10 | // item 1 has affect 2 for a value of 10| 1 | 3 | 2 | // item 1 has affect 3 for a value of 2| 2 | 1 | 5 | // item 2 gets aff 1 for 5| 2 | 1 | 6 | // item 2 gets aff 1 for 6 which means 11 total| 3 | 5 | 5 |+--------+---------+----------+``

table itemaffectbyTable (call these bProps they're relevant when present)

``+--------+---------+| itemID | affbyID |+--------+---------+| 1 | 6 || 3 | 2 || 3 | 3 |+--------+---------+``

Sample output:

``itemID sdesc qpropID value bpropID1221 a copper lantern 4 2 51221 a copper lantern 18 2 51221 a copper lantern 17 -5 5477 a shade 19 3 4477 a shade 19 3 6``

This is incorrect in two ways. For the first item the affectbyID 5 is repeated 3 times ... this is tolerable. In the second case we have the affectID 19 and affect value 3 being repeated twice, this is not permissible.

Ideally I'd like to see

``itemID sdesc qpropID value bpropID1221 a copper lantern 4 2 51221 a copper lantern 18 2 NULL1221 a copper lantern 17 -5 NULL477 a shade 19 3 4477 a shade NULL NULL 6``

The main problem is the repetition of the qpropIDs and values since they are additive. If the solution repeats bpropIDs it's no big deal.

** UPDATE **

I attempted to use the FULL JOIN idea to get my results but couldn't seem to zero in.

The closest I've come to the results I want came from using sqlFiddle to get

``select i.id, i.sdesc, iaft.affectID, iaft.amount, NULL FROM item iLEFT JOIN itemaffectTable iaft ON i.id=iaft.itemIDUNIONselect i.id, i.sdesc, NULL, NULL, iafbt.affectbyID FROM item iLEFT JOIN itemaffectedbyTable iafbt ON i.id=iafbt.itemIDORDER BY id``

So bottom line the idea is I want to retrieve a list of items which meet the criteria of the filters and then match those items up with their associated affectID and affectbyIDs.

The original is below.

``SELECT DISTINCT i.id, i.sdesc, iaft.affectID, iaft.amount, iafbt.affectbyID FROM item iINNER JOIN itemwearTable iwt ON i.id=iwt.itemIDLEFT JOIN itemaffectTable iaft ON i.id=iaft.itemIDLEFT JOIN itemaffectedbyTable iafbt ON i.id=iafbt.itemIDLEFT JOIN itemalignTable iat ON i.id = iat.itemIDLEFT JOIN itemgenderTable igt ON i.id = igt.itemIDLEFT JOIN itemgenreTable igrt ON i.id = igrt.itemIDLEFT JOIN itemclassTable ict ON i.id = ict.itemIDLEFT JOIN itemraceTable irt ON i.id = irt.itemIDWHERE (iat.itemID IS NULL OR iat.alignID = 1)AND (igt.itemID IS NULL OR igt.genderID = 1)AND (igrt.itemID IS NULL OR igrt.genreID = 1)AND (ict.itemID IS NULL OR ict.classID = 1)AND (irt.itemID IS NULL OR irt.raceID = 1)AND i.minlvl <= 50AND iwt.wearlocID=1ORDER BY sdesc``

This really is a strange result you're after - it's a sort of pivoted union or something.

Nevertheless, but here it is, with all the bells and whistles you asked for!

``````select id, sdesc, affectID, amount, affectbyID
from (select
id, sdesc,
if(sameid and @affectID = affectID and @amount = amount, null, affectID) as affectID,
if(sameid and @affectID = affectID and @amount = amount, null, amount) as amount,
@affectID := affectID,
@amount := amount,
if(sameid and @affectbyID = affectbyID, null, affectbyID) as affectbyID,
@affectbyID := affectbyID
from (select
if(@id is null, false, @id = id) as sameId,
@id := id as id,
sdesc, affectID, amount, affectbyID
from (select distinct
i.id,
i.sdesc,
iaft.affectID,
iaft.amount,
iafbt.affectbyID
FROM item i
LEFT JOIN itemaffectTable iaft ON i.id=iaft.itemID
LEFT JOIN itemaffectedbyTable iafbt ON i.id=iafbt.itemID
ORDER BY 1,3,4,5
) x) y) z
``````

This makes use of User Defined Variables to remember the previous values for columns.

The logic is reset for every new `id` value, and other columns are made null if the column (or column pair) has the same value as the previous row.

See a live demo on SQLFiddle

Take a look here. I believe this is what you are looking for. In SSMS I would solve this with a Full Join, but apparently you cant do that in mysql so you need a union to combine the right and left outer joins.

http://www.tutorialspoint.com/sql/sql-full-joins.htm

I don't quite get your expected result... it seems quite irrational to me.

Do you want to do something like, for each item, assume you get item-affect and item-affectedBy separately, and you want to combine the result in a row-based manner? i.e. row 1 of item A in first result will be put together with row 1 of item A in second result: e.g.

``````Item     Affect               Item     AffectedBy
1         a                    1         r
1         b
2         c                    2         s
2         t
3         d
4         u
5         e                    5         v
5         f                    5         w
``````

You want to see something like:

``````Item    Affect    AffectedBy
1         a          r
1         b          NULL
2         c          s
2         NULL       t
3         d          NULL
4         NULL       u
5         e          v
5         f          w
``````

Right?

If so, the most straight-forward way you can think of is, first make result of item-affect looks like:

``````Item     Affect   seq
1         a        1
1         b        2
2         c        1
3         d        1
5         e        1
5         f        2
``````

Seq column is the "rank" within group of item. Do similar thing in item-affectedBy

Then do a full outer join on item and seq column.

I am not sure how to achieve these in MySQL, in Oracle, the way to generate the seq is something like:

``````select item_id, affect_id, rank() over (partition by item_id order by rownum) rank
from item_affect
order by item_id, rank;
``````

Try to think if you can do similar thing in MySQL.

For full outer join, if it is not supported, you can always perform a union with left and right join.

Top