John Davidson

Message:

I would join my table notifications with 2 different tables, let me present a similar example.

Notifications:

ID | player_id | item_id | .... 

Basket player's items

player_id | item_id | note | points | rebounds

Soccer player's items

player_id | item_id | note | match | goal 

IMPORTANT: all the player ids are unique, the soccer players are in the format 1******** and the basket players are in the format 5********. so also all the couples player_id - item_id are unique.

The result should be a table like this:

id | player_id | item_id | note | attr1 | attr2

where in "note" there is a note of both kind of players and "attr1" is filled by points or match depends on the player type (same type. INT)

Moreover, the items can be joined with tables SOCCER_ITEMS and BASKET_ITEMS where there are information about the item itself. This JOIN can create duplicated rows because the item_id is not unique between the 2 tables and so a basket item can be joined with the info of a soccer item, creating issues.

is it possible to collect data in the way presented in SQL? I'm writing the code in PHP (don't know if it is useful or not)

---

My Answer:
attr1 and attr2 are the specific attributes for each type of player (points and rebounds for basket players, match and goal for soccer players).

Here is an example query to achieve this:

sql
SELECT n.ID, n.player_id, n.item_id, n.note,
CASE
WHEN n.player_id LIKE '1%' THEN s.match
WHEN n.player_id LIKE '5%' THEN b.points
END AS attr1,
CASE
WHEN n.player_id LIKE '1%' THEN s.goal
WHEN n.player_id LIKE '5%' THEN b.rebounds
END AS attr2
FROM notifications n
LEFT JOIN soccer_players_items s ON n.player_id = s.player_id AND n.item_id = s.item_id
LEFT JOIN basket_players_items b ON n.player_id = b.player_id AND n.item_id = b.item_id


This query uses a CASE statement to determine which attributes to select based on the player_id format. It joins the notifications table with the soccer_players_items and basket_players_items tables on both player_id and item_id to get the specific attributes for each player.